Goran Bibic Ответов: 1

Ежемесячный отчет SQL из существующих таблиц работником

Нужно починить этот код для ежемесячного отчета
Радник - рабочий
Vrijemeprijave это дата и время приходите на работу,
Vrijemeodjave это конец работы

Что я уже пробовал:

SELECT  [radnik] as Radnik,SUM(cast((CASE WHEN DATEPART(WEEKDAY, vrijemeprijave) in (1, 7) then 0 else (CASE WHEN(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) > 8 THEN 8 ELSE(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) END) end)as float))   AS radni_sati," 
                     + "SUM(cast((CASE WHEN DATEPART(WEEKDAY, vrijemeodjave) in (1, 7) then(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) else(case when(DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) > 8  then((DATEDIFF(SECOND, vrijemeprijave, vrijemeodjave) / 3600) - 8) ELSE NULL END) end)as float))   AS 'Prekovremeni'"
                     + "from[prijava_radnika]"
                     + "group by[radnik]";

Jörgen Andersson

А что случилось?

CHill60

Дайте нам схемы таблиц, некоторые примеры данных и некоторые ожидаемые результаты или, по крайней мере, объясните, что должны показать результаты. Невероятно трудно распутать эти заявления по делу (которые, как я подозреваю, не требуются)

CHill60

Некоторые другие моменты:
1) Зачем использовать DATEDIFF(SECOND, Vrijemeprijave, Vrijemeodjave) / 3600 вместо DATEDIFF(MINUTE, Vrijemeprijave, Vrijemeodjave) ?
2) Зачем бросать сумму как поплавок - это кажется бессмысленным
3) Зачем убирать 8 минут из объема проделанной работы за Прековремени?
4) вы действительно имеете в виду NULL в этой части вместо 0? Быть последовательным

Goran Bibic

Опубликуйте мой код...создайте какой-нибудь исправленный код, например...мне нужна помощь...спасибо
1) это нормально для меня...некоторые recomantidiot?
2)сумма...что использовать?
3) не 8 минут, а 8 часов...работа 13 часов...8 - это нормально, 13-8= 5 - это сверхурочно
4)Да...нуль...мне не нужен 0...Или я могу использовать 0 becose из суммы часов

Мне нужна помощь для лучшего решения

CHill60

1 - это кажется излишним, тем более что расчет все равно приведет только к целым часам. Если вы хотите записать неполные часы вам нужно будет использовать / 3600.0
2 - Не бросайте вообще
3 - мои извинения, я неправильно понял код.
4 - Поскольку вы вводите значение в сумму, а не в счет, то вам было бы лучше использовать 0 вместо нуля.
Теперь когда я знаю в чем на самом деле проблема я пойду и посмотрю на это

Goran Bibic

Подобный этому...
ошибка-Msg 156, Уровень 15, состояние 1, строка 13
Неверный синтаксис рядом с ключевым словом "группа".

Выберите.Радник, функция isnull(конвертировать(тип varchar(20), на.vrijemeodjave, 113), ") как vrijemeodjave, конвертировать(тип varchar(20), б.vrijemeprijave, 113), а vrijemeprijave,
--Случай, когда функция datepart(будний день, б.vrijemeprijave) в (1,7) тогда 0 иначе (случай, когда (функция datediff(во-вторых, б.vrijemeprijave, а.vrijemeodjave) / 3600) &ГТ; 8 и 8 еще(функция datediff(во-вторых, б.vrijemeprijave, а.vrijemeodjave) / 3600) конец) конец как radni_sati,
--Случай, когда функция datepart(день недели, а.vrijemeodjave) в (1,7) затем (функция datediff(во-вторых, б.vrijemeprijave, а.vrijemeodjave) / 3600) другой(случай, когда(функция datediff(во-вторых, б.vrijemeprijave, а.vrijemeodjave) / 3600) &ГТ; 8 лет ((функция datediff(во-вторых, б.vrijemeprijave, а.vrijemeodjave) / 3600)-8) еще null конец) конец как prekovremeni
Сумма(бросание((случай, когда функция datepart(будний день, 01), в (1, 7) тогда 0 иначе (случай, когда(функция datediff(второе, 01, 3) / 3600) &ГТ; 8 и 8 еще(функция datediff(второе, 01, 3) / 3600) конец) конец)как поплавок)) Как укупно_радни_сати,
Сумма(бросание((случай, когда функция datepart(день, 3) в (1, 7) затем(функция datediff(второе, 01, 3) / 3600) другой(случай, когда(функция datediff(второе, 01, 3) / 3600) &ГТ; 8 лет((функция datediff(второй, 01, 3) / 3600) - 8) еще null конец) конец)как поплавок)) Как rekovremeni Ukupno'
Из(выбрать Радник vrijemeodjave, функции row_number() над(раздел по заказу IdPrijava по vrijemeprijave) как параметр rownum
От dbo.prijava_radnika) а внутреннее соединение(выбрать Радник vrijemeprijave, (функции row_number() над(раздел по заказу IdPrijava по vrijemeprijave) - 1) Как RowNumMinusOne от dbo.prijava_radnika)
группа А. радника

1 Ответов

Рейтинг:
2

CHill60

Ваша первая проблема вызвана наличием внутреннего соединения но не включением предложения ON например 

b ON a.radnik = b.radnik
Я уже использовал b в качестве псевдонима для последнего подзапроса.

Тогда вы получите ошибку 
Column 'a.vrijemeodjave' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Вы должны иметь оба vrijemeodjave и vrijemeprijave в агрегатной функции, чтобы заставить это работать. Я обычно использую MAX Обратите внимание, что в данный момент Вы также не должны преобразовывать даты в строки - оставьте это вызывающему коду или слою пользовательского интерфейса.

Я предполагаю, что вы пытаетесь рассчитать отработанные часы и сверхурочные в течение месячного периода, но ваш sql настолько запутан, что трудно сказать, что вы должны делать дальше.

Вот техника для облегчения чтения (и, следовательно, легче узнать, где что-то идет не так)

Для начала я настроил некоторые тестовые данные (в табличной переменной) следующим образом:
declare @prijava_radnika table (IdPrijava int identity(1,1), radnik int, vrijemeodjave datetime, vrijemeprijave datetime)
insert into @prijava_radnika (radnik, vrijemeodjave, vrijemeprijave) values
(1001, '2018 MAY 03 08:00:00', '2018 MAY 03 17:00:00'),
(1001, '2018 MAY 04 08:00:00', '2018 MAY 04 17:00:00'),
(1002, '2018 MAY 04 09:00:00', '2018 MAY 04 17:00:00'),
(1003, '2018 MAY 04 10:00:00', '2018 MAY 04 17:00:00'),
(1004, '2018 MAY 04 10:00:00', '2018 MAY 04 17:00:00'),
(1004, NULL, '2018 MAY 04 17:00:00'),
(1004, '2018 MAY 04 10:00:00', NULL)
Затем я посмотрел на все это DATEDIFF(SECOND, 01, 3) / 3600)Они делают код трудным для чтения, а также делают его менее эффективным. Они также возвращают только целые часы, мои сотрудники тоже хотят быть зачисленными за работу этих дополнительных получасов: -) поэтому я "абстрагировал" эти вычисления в подзапрос ... или в моем случае "общее табличное выражение (CTE)", подобное этому:
;with getHours as
 (
	SELECT IdPrijava, radnik, vrijemeodjave, vrijemeprijave, DATEDIFF(SECOND, vrijemeodjave, vrijemeprijave) / 3600.0 as hrsWorked,
	wkday = CASE WHEN DATEPART(WEEKDAY, vrijemeprijave) in (1,7) THEN 0 ELSE 1 END -- This is a trick
	FROM @prijava_radnika
	WHERE vrijemeodjave IS NOT NULL AND vrijemeprijave IS NOT NULL
 )
 select * from getHours
Здесь следует отметить несколько моментов:
1. Теперь я могу обратиться к hrsWorked везде вместо того, чтобы DATEDIFF(SECOND, 01, 3) / 3600)
2. Я собираюсь получить hrsWorked обратно десятичной потому что я использовал 3600.0
3. Я включил трюк - wkday что я буду использовать, чтобы установить hrsWorked до 0, если дата назначена на выходные ... смотрите следующий запрос.
4. эта дополнительная точка с запятой (;) в начале CTE важна, так как у меня есть весь код инициализации табличного значения над этим запросом в моем скрипте.

Вот мои результаты на данный момент:
1	1001	2018-05-03 08:00:00.000	2018-05-03 17:00:00.000	9.000000	1
2	1001	2018-05-04 08:00:00.000	2018-05-04 17:00:00.000	9.000000	1
3	1002	2018-05-04 09:00:00.000	2018-05-04 17:00:00.000	8.000000	1
4	1003	2018-05-04 10:00:00.000	2018-05-04 17:00:00.000	7.000000	1
5	1004	2018-05-04 10:00:00.000	2018-05-04 17:00:00.000	7.000000	1
Теперь я хочу иметь возможность рассчитывать нормальные рабочие часы и сверхурочные с учетом выходных, поэтому я добавил еще один CTE calcOverTime
;with getHours as
(
   SELECT IdPrijava, radnik, vrijemeodjave, vrijemeprijave, DATEDIFF(SECOND, vrijemeodjave, vrijemeprijave) / 3600.0 as hrsWorked,
   wkday = CASE WHEN DATEPART(WEEKDAY, vrijemeprijave) in (1,7) THEN 0 ELSE 1 END -- This is a trick
   FROM @prijava_radnika
   WHERE vrijemeodjave IS NOT NULL AND vrijemeprijave IS NOT NULL
)
, calcOverTime as
(
   SELECT IdPrijava, radnik, vrijemeodjave, vrijemeprijave, (wkday * hrsWorked) as actualhours,
   OverTime = CASE WHEN (wkday * hrsWorked) > 8 THEN hrsWorked - 8 ELSE 0 END,
   NormalHours = CASE WHEN (wkday * hrsWorked) > 8 THEN 8 ELSE (wkday * hrsWorked) END
   FROM getHours
)
select * from calcOverTime
Моменты, которые следует отметить:
1. Посмотрите, как я сейчас вычисляю hrsWorked ... если дата является выходным днем, то wkday будет равен 0, а любое число, умноженное на 0, станет 0.

Теперь мои результаты до сих пор выглядят так (я отредактировал некоторые десятичные знаки, чтобы сохранить вывод аккуратным)
1	1001	2018-05-03 08:00:00 2018-05-03 17:00:00 9.000 1.000	8.000
2	1001	2018-05-04 08:00:00 2018-05-04 17:00:00 9.000 1.000	8.000
3	1002	2018-05-04 09:00:00 2018-05-04 17:00:00 8.000 0.000	8.000
4	1003	2018-05-04 10:00:00 2018-05-04 17:00:00 7.000 0.000	7.000
5	1004	2018-05-04 10:00:00 2018-05-04 17:00:00 7.000 0.000	7.000
Наконец, я могу суммировать эти детали по работнику за каждый месяц со следующим запросом 
SELECT radnik, month(vrijemeodjave) as [Month], SUM(NormalHours) as NormalHours, SUM(OverTime) as OverTime
FROM calcOverTime
group by radnik, month(vrijemeodjave)
ORDER BY radnik, month(vrijemeodjave)
Я не знаю, почему вы пытались использовать rownum и rownum - 1, но это способ получения значений для "предыдущих" строк. Я не думаю, что вам это нужно здесь, потому что у вас есть время начала и окончания в одном ряду. Если вам действительно нужно посмотреть на предыдущие (или следующие) строки, то подумайте об использовании LAG (или LEAD), если у вас есть более поздняя версия SQL

Edit - таким образом, полный код является:
;with getHours as ( SELECT IdPrijava, radnik, vrijemeodjave, vrijemeprijave, DATEDIFF(SECOND, vrijemeodjave, vrijemeprijave) / 3600.0 as hrsWorked, wkday = CASE WHEN DATEPART(WEEKDAY, vrijemeprijave) in (1,7) THEN 0 ELSE 1 END -- This is a trick FROM @prijava_radnika WHERE vrijemeodjave IS NOT NULL AND vrijemeprijave IS NOT NULL ) , calcOverTime as ( SELECT IdPrijava, radnik, vrijemeodjave, vrijemeprijave, (wkday * hrsWorked) as actualhours, OverTime = CASE WHEN (wkday * hrsWorked) > 8 THEN hrsWorked - 8 ELSE 0 END, NormalHours = CASE WHEN (wkday * hrsWorked) > 8 THEN 8 ELSE (wkday * hrsWorked) END FROM getHours ) 
  SELECT radnik, month(vrijemeodjave) as [Month], SUM(NormalHours) as NormalHours, SUM(OverTime) as OverTime FROM calcOverTime group by radnik, month(vrijemeodjave) ORDER BY radnik, month(vrijemeodjave)

Goran Bibic

Вся работа кода и результат такие же как в примерах

Но последняя строчка

Выберите Радник месяц(vrijemeodjave) как [месяц], сумма(NormalHours) как NormalHours, сумма(сверхурочная работа) сверхурочная
Из calcOverTime
группа по Раднику, месяц(vrijemeodjave)
Заказ от радника, месяц(vrijemeodjave)

не работают

CHill60

Вы не можете запустить эту линию самостоятельно. Я обновлю свое решение