Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Boyler
Member

Откуда: Казахстан, Астана
Сообщений: 111
Есть таблица вида (сильно упрощенно)
Материал 1 Материал 2 Материал 3 Время
388 81 558 19.01.2012 11:31:11
384 80 555 19.01.2012 12:09:25
382 81 556 19.01.2012 12:30:55
386 84 554 19.01.2012 12:46:03
385 80 558 19.01.2012 12:53:34
385 76 555 19.01.2012 13:10:36
384 77 562 19.01.2012 13:20:12

Пытаюсь посчитать количество записей попадающих за ночную смену с 20.00 по 8.00, с группировкой по дню месяца.
SELECT     TOP (100) PERCENT COUNT(*) AS Количество, SUM(Материал 1) AS Материал 1, SUM(Материал 2) AS Материал 2, SUM(Материал 3) AS Материал 3, DAY(time1) AS День
FROM         dbo.UA#Ricipe
WHERE     ({ fn HOUR(time1) } BETWEEN 20 AND 24) OR
                      ({ fn HOUR(time1) } BETWEEN 0 AND 7)
GROUP BY DAY(time1)
ORDER BY День

Количество Материал 1 Материал 2 Материал 3 День
19 6898 1344 8001 2
2 746 116 1049 5
43 15965 2601 21313 6
34 12448 2074 19290 7
17 6197 1196 8889 12
43 15644 2996 21441 13


Вроде бы как и правильно, но по времени с 20ч до 24ч это относиться допустим к 1.01.2012, а с 0ч до 8ч уже к 2.01.2012.
В итоге получается что я плюсую конец предыдущей ночной смены с началом следующей.

---------------- День ---------------
0 1 2 3 4 5 6 7 8 ...... .. . . . .. 20 21 22 23 24

Мне же надо
20 21 22 23 24 0 1 2 3 4 5 6 7 8 ...................
и отнести этот результат к первому числу месяца.

Как этого можно добиться и можно ли?
Спасибо.
28 фев 12, 07:03    [12161341]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6202
Просто определитесь с тем, как определяется номер суток смены. Например, считаем, что номер суток смены - это номер суток, в которые смена началась. Тогда как-то так:
set language russian
;with cte as (
  select stuff1, stuff2, stuff3, todate from (
    values
      (388, 81, 558, convert(datetime,'19.01.2012 21:31:11')),
      (384, 80, 555, convert(datetime,'19.01.2012 22:09:25')),
      (382, 81, 556, convert(datetime,'20.01.2012 02:30:55')),
      (386, 84, 554, convert(datetime,'20.01.2012 03:46:03')),
      (385, 80, 558, convert(datetime,'20.01.2012 22:53:34')),
      (385, 76, 555, convert(datetime,'20.01.2012 23:10:36')),
      (384, 77, 562, convert(datetime,'21.01.2012 03:20:12')),
      (384, 77, 562, convert(datetime,'21.01.2012 04:20:12'))
  ) t(stuff1, stuff2, stuff3, todate)
)
select SUM(stuff1), SUM(stuff2), SUM(stuff3), f.[day]
from cte
cross apply (
  select case when datepart(hh,todate)<=4 then day(dateadd(dd, -1, todate)) else day(todate) end
)f([day])
group by f.[day]
28 фев 12, 07:28    [12161350]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Boyler
Member

Откуда: Казахстан, Астана
Сообщений: 111
Гляжу в книгу вижу фигу ;-)
Простите за каламбур. Туповат я в этом.

Как тоже самое оформить запросом в преставлении относительно данного примера
SELECT     TOP (100) PERCENT COUNT(*) AS Количество, SUM(Материал 1) AS Материал 1, SUM(Материал 2) AS Материал 2, SUM(Материал 3) AS Материал 3, DAY(time1) AS День
FROM         dbo.UA#Ricipe
WHERE     ({ fn HOUR(time1) } BETWEEN 20 AND 24) OR
                      ({ fn HOUR(time1) } BETWEEN 0 AND 7)
GROUP BY DAY(time1)
ORDER BY День

Не ради получения готового результата а чтобы понять суть, я с SQL последнеий раз общался лет 10 назад. Собственно свой запрос уже два дня пишу.
28 фев 12, 07:48    [12161359]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Boyler
Member

Откуда: Казахстан, Астана
Сообщений: 111
Ну в целом направление поиска понятно...
Буду рыть в направлении select case
А то не понятно было как условие отбора поставить по мимо WHERE.
28 фев 12, 08:29    [12161402]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Boyler
Member

Откуда: Казахстан, Астана
Сообщений: 111
Уж простите моё ламерство
SELECT     COUNT(*) AS Количество, SUM(Материал 1) AS Материал 1, CASE WHEN (datepart(hh, time1) <= 7) THEN (DAY(time1) - 1) ELSE DAY(time1) 
                      END AS День
FROM         dbo.UA#Ricipe

Выдает ошибку вроде того что нельзя испеользоваться time1 если нет "GROUP BY time1"
GROUP BY День - не принимает видимо потому что это альяс а не реальный столбец.
28 фев 12, 09:33    [12161546]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6202
Boyler
Уж простите моё ламерство

Да нам-то что. Вы сами подумайте, что вам вернет
DAY(time1) - 1

если time1 придется на 3 часа ночи 1-го марта, к примеру.
28 фев 12, 09:59    [12161657]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
iljy
Member

Откуда:
Сообщений: 8711
Boyler,

целиком выражение для День укажите.
28 фев 12, 10:01    [12161667]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Boyler, не могу понять в чем сложность... В формировании условия для фильтра чтоль?

SELECT CONVERT(DATETIME, '20120228 20:00:00',112), DATEADD(HOUR,12,CONVERT(DATETIME, '20120228 20:00:00',112))
28 фев 12, 10:05    [12161689]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Boyler
Member

Откуда: Казахстан, Астана
Сообщений: 111
Пока пришел к этому.
SELECT     TOP (100) PERCENT COUNT(*) AS Количество, time1, CASE WHEN (datepart(hh, time1) <= 7 AND (DAY(time1) - 1) > 0) THEN (DAY(time1) - 1) 
                      ELSE DAY(time1) END AS День, time1 AS Дата
FROM         dbo.UA#Ricipe
WHERE     (MONTH(time1) = MONTH(GETDATE())) AND ({ fn HOUR(time1) } BETWEEN 20 AND 24 OR
                      { fn HOUR(time1) } BETWEEN 0 AND 7)
GROUP BY time1

Запрос возвращает.
time1 День Дата
06.02.2012 21:44:26606.02.2012 21:44:26
06.02.2012 22:08:09606.02.2012 22:08:09
06.02.2012 22:16:48606.02.2012 22:16:48
06.02.2012 22:25:23606.02.2012 22:25:23
07.02.2012 2:03:09607.02.2012 2:03:09
07.02.2012 2:11:47607.02.2012 2:11:47

т.е. получилось сместить окончание смены (с 0ч до 8ч) на предыдущий День.
Сон Веры Павловны
если time1 придется на 3 часа ночи 1-го марта, к примеру.

Уже столкнулся, ноль вернет.
28 фев 12, 10:14    [12161741]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Boyler
Member

Откуда: Казахстан, Астана
Сообщений: 111
В предыдущем посте указал реальные данные, пеутаница будет не много.

Я собственно пытаюсь сделать Представление таблицы с данными.

Мне надо просчитать сколько сделала ночная смена с 20.00 до 8.00 и дневная смена и сгруппировать это по дням месяца.
С дневной продем нет
WHERE     (MONTH(time1) = MONTH(GETDATE())) AND ({ fn HOUR(time1) } >= 08.00) AND ({ fn HOUR(time1) } <= 20.00)

А с ночной получается переход через ноль часов, вот и пытаюсь все что после нуля часов отнести к предыдущему дню - когда смена началась.
28 фев 12, 10:18    [12161781]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Boyler
Member

Откуда: Казахстан, Астана
Сообщений: 111
Упрощенно хочу
Количество ночная смена День месяца
511
556
317
4312
5313
5714
5615
5916

Количество дневная смена День месяца
511
556
317
4312
5313
5714
5615
5916
28 фев 12, 10:22    [12161804]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
iljy
Member

Откуда:
Сообщений: 8711
Boyler,

выражение, прявязывающее смену к дню, можно записать так:
select dateadd(d, (datediff(hh, 0, time1) -8) /24,0)

Это верно и для дневной, и для ночной.
28 фев 12, 10:24    [12161812]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Boyler,

и выбросьте, наконец, TOP(100) PERCENT ... ORDER BY
28 фев 12, 10:46    [12161976]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Boyler
Member

Откуда: Казахстан, Астана
Сообщений: 111
автор
и выбросьте, наконец, TOP(100) PERCENT ... ORDER BY

Это не я - конструктор это делает.

В общем у меня получился такой результат
SELECT     TOP (100) PERCENT COUNT(*) AS Количество, SUM(Ist_Cement_1) AS Цемент, SUM(Ist_Lime_1 + Ist_Add_Comp) AS Известь, SUM(Ist_Anhydrite) AS Гипс, DATEADD(d, (DATEDIFF(hh, 0, time1) - 8) / 24, 0) AS [Дата смены]
FROM         dbo.UA#Ricipe
WHERE     (MONTH(time1) = MONTH(GETDATE())) AND ({ fn HOUR(time1) } BETWEEN 20 AND 24 OR
                      { fn HOUR(time1) } BETWEEN 0 AND 7)
GROUP BY time1

КоличествоЦементИзвестьГипсДата смены time1
13634247101.02.2012 0:00:0002.02.2012 2:59:14
13624227001.02.2012 0:00:0002.02.2012 3:07:11
13624207101.02.2012 0:00:0002.02.2012 3:16:49
13634217101.02.2012 0:00:0002.02.2012 4:20:54
13725235605.02.2012 0:00:0005.02.2012 23:16:02
13745266005.02.2012 0:00:0005.02.2012 23:23:37
13725235805.02.2012 0:00:0006.02.2012 0:03:21
13745236105.02.2012 0:00:0006.02.2012 0:11:03

Чтобы достигнуть необходимого результата пришлось сделать второе представление используя первое.
SELECT     SUM(Количество) AS Количество, SUM(Цемент) AS Цемент, SUM(Известь) AS Известь, SUM(Гипс) AS Гипс, [Дата смены]
FROM         dbo.[Ночная смена]
GROUP BY [Дата смены]

КоличествоЦементИзвестьГипсДата смены
551999824601334617.02.2012 0:00:00
702636533882423518.02.2012 0:00:00
572065626873346219.02.2012 0:00:00
662392432005400820.02.2012 0:00:00
632282826494383221.02.2012 0:00:00
642376829400325522.02.2012 0:00:00
481739923096243223.02.2012 0:00:00
702531933500357224.02.2012 0:00:00
124334121260925.02.2012 0:00:00
602255029828304826.02.2012 0:00:00

Может это и коряво получилось но в принципе результат меня более устраивает.
А может и проще можно было...

iljy
выражение, прявязывающее смену к дню, можно записать так:
select dateadd(d, (datediff(hh, 0, time1) -8) /24,0)
Это верно и для дневной, и для ночной.

Возможно не правильно воспользовался, но пригодилось.

Единственный ньюанс остался - попадает 31-е число, но может это и хорошо, пока не определился.

Так что огромное всем спасибо за участие.
28 фев 12, 12:15    [12162635]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Boyler
Member

Откуда: Казахстан, Астана
Сообщений: 111
И прошу прощения за наглость, вопрос в догонку.

Как вывести каждое четвертое значение или через одно?
28 фев 12, 12:24    [12162711]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
Boyler,

NTILE
28 фев 12, 12:33    [12162805]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
[Номер строки] % 4 = 0
[Номер строки] % 2 = 0
28 фев 12, 12:38    [12162849]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Boyler
Member

Откуда: Казахстан, Астана
Сообщений: 111
Всем еще раз большое спасибо!
28 фев 12, 12:50    [12162970]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
Guf
NTILE

Прошу прощения
28 фев 12, 12:56    [12163022]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
Boyler
Member

Откуда: Казахстан, Астана
Сообщений: 111
Не страшно
автор
[Номер строки] % 4 = 0
[Номер строки] % 2 = 0

Такой вариант подошел
28 фев 12, 13:18    [12163217]     Ответить | Цитировать Сообщить модератору
 Re: Работа с датой. Подсчет производительности ночной смены с 20,00 до 8,00  [new]
smallserg
Member

Откуда:
Сообщений: 82
Boyler,

Boyler,

а почему просто не вычесть 8 часов ?

SELECT   COUNT(*) AS Количество, SUM(Материал 1) AS Материал 1, 
SUM(Материал 2) AS Материал 2, SUM(Материал 3) AS Материал 3, DAY(dateadd(hh,-8,time1)) AS День
FROM         dbo.UA#Ricipe
WHERE     ({ fn HOUR(dateadd(hh,-8,time1)) } BETWEEN 12 AND 24) 
GROUP BY DAY(dateadd(hh,-8,time1))
ORDER BY День
29 фев 12, 11:11    [12168244]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить