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

Откуда:
Сообщений: 44
Здравствуйте, вопрос к более опытным
есть таблица
Начало смены(SHIFT_DATE)оборудования(EQUIP_IDENT)    Время начала статуса(START_TIMESTAMP)      Статус(STATUS_CODE)      Время окончания статуса(END_TIMESTAMP)


/*Статус - к примеру оборудование стоит,двигается это все отдельные статусы*/

Задача следующая нужно разбить время статусов почасово. Проблема заключается в том, что статус может начинаться в один час а заканчиваться в другой. Как сделать так чтобы часть статуса попадала в один час а вторая часть во второй?



часы я выбрал так
     DISTINCT DATEADD(hh,1,DATEADD(ss, -DATEPART(ss,est.START_TIMESTAMP), DATEADD(mi, -DATEPART(mi,est.START_TIMESTAMP),dbo.season_offset(est.START_TIMESTAMP,1)))) as 'DATE_TIME_HOURS',



Я прикрутил одну конструкцию, но она обрезает час с одной стороны(если час конца статуса > часа начала статуса - тогда рубить по концу стартового часа), но тогда как добыть 2-ю часть от начала 2-го часа до конца статуса?

Я понимаю что ковыряться в чужом коде с чужой базой... но описывая словами происходящее:
1.
Выбираю часы по старту статуса
Выбираю часы по концу статуса
Оборудование
Статус
Время начала статуса
Время конца

2.
Условие во внешнем case - если час старта статуса = часу конца статуса и статус = рабочий
тогда выбираю время статуса
если если час = часу старта и час конца статуса > часа начала статуса
тогда время (начала статуса до часа начала статуса (например 18 ) + 00:59:59)

получится к примеру статус начался в 18:30:23 и закончился в 19:15:00 тогда его обрежет с 18:30:23 до 18:59:59

Как достать часть от 19:00:00 до 19:15:00??


select
DATE_TIME_HOURS,
EQUIP_IDENT,
START_TIMESTAMP,
END_TIMESTAMP,
CASE 
		WHEN DATE_TIME_HOURS_START = DATE_TIME_HOURS_END AND tab.STATUS_CODE = 'KIO_WORK' 
		THEN DATEDIFF(SS,START_TIMESTAMP,END_TIMESTAMP)
		WHEN DATE_TIME_HOURS_START = DATE_TIME_HOURS AND DATE_TIME_HOURS_END > DATE_TIME_HOURS_START  AND  tab.STATUS_CODE = 'KIO_WORK' 
		THEN DATEDIFF(SS,START_TIMESTAMP, DATEADD(HH,-1,DATE_TIME_HOURS_START) + '00:59:59') 
		ELSE 0 END AS WORK   

from(
select 
     DISTINCT DATEADD(hh,1,DATEADD(ss, -DATEPART(ss,est.START_TIMESTAMP), DATEADD(mi, -DATEPART(mi,est.START_TIMESTAMP),dbo.season_offset(est.START_TIMESTAMP,1)))) as 'DATE_TIME_HOURS', -- Это часы
     DATEADD(hh,1,DATEADD(ss, -DATEPART(ss,est.START_TIMESTAMP), DATEADD(mi, -DATEPART(mi,est.START_TIMESTAMP),dbo.season_offset(est.START_TIMESTAMP,1)))) as 'DATE_TIME_HOURS_START', --Это час старта статуса
     DATEADD(hh,1,DATEADD(ss, -DATEPART(ss,est.END_TIMESTAMP), DATEADD(mi, -DATEPART(mi,est.END_TIMESTAMP),dbo.season_offset(est.END_TIMESTAMP,1)))) as 'DATE_TIME_HOURS_END', -- Это час конца статуса
     est.EQUIP_IDENT,
	 --DATEDIFF(SS,START_TIMESTAMP,END_TIMESTAMP)as duration,
	 est.STATUS_CODE,
	 START_TIMESTAMP,
	 END_TIMESTAMP	


from EQUIPMENT_STATUS_TRANS est

where SHIFT_DATE between @start_date and @end_date
and SHIFT_IDENT = @shift_ident
--AND DATEPART(HH,START_TIMESTAMP)=DATEPART(HH,END_TIMESTAMP)
) tab
order by EQUIP_IDENT,DATE_TIME_HOURS,START_TIMESTAMP



Пример выполненного запроса:

DATE_TIME_HOURS     EQUIP_IDENT  START_TIMESTAMP             END_TIMESTAMP      DURATION
2016-03-23 21:00:00.000	11	2016-03-23 20:00:00.000	2016-03-23 20:05:17.000	0
2016-03-23 21:00:00.000	11	2016-03-23 20:05:17.000	2016-03-23 20:06:24.000	67
2016-03-23 21:00:00.000	11	2016-03-23 20:06:24.000	2016-03-23 20:09:45.000	0
2016-03-23 21:00:00.000	11	2016-03-23 20:09:45.000	2016-03-23 20:10:05.000	20
2016-03-23 21:00:00.000	11	2016-03-23 20:10:05.000	2016-03-23 20:11:16.000	71
2016-03-23 21:00:00.000	11	2016-03-23 20:11:16.000	2016-03-23 20:14:57.000	221
2016-03-23 21:00:00.000	11	2016-03-23 20:14:57.000	2016-03-23 20:16:18.000	0
2016-03-23 21:00:00.000	11	2016-03-23 20:16:18.000	2016-03-23 20:19:57.000	219
2016-03-23 21:00:00.000	11	2016-03-23 20:19:57.000	2016-03-23 20:21:16.000	79
2016-03-23 21:00:00.000	11	2016-03-23 20:21:16.000	2016-03-23 20:24:42.000	206
2016-03-23 21:00:00.000	11	2016-03-23 20:24:42.000	2016-03-23 20:26:25.000	0
2016-03-23 21:00:00.000	11	2016-03-23 20:26:25.000	2016-03-23 20:30:29.000	244
8 апр 16, 13:55    [19032701]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
Glory
Member

Откуда:
Сообщений: 104751
используйте вспомогательную таблицу/запрос, где у вас будет 12 записей - по одной на кадждый час
8 апр 16, 14:08    [19032792]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
FastTI
Member

Откуда:
Сообщений: 44
Glory,
Не много не понял...
8 апр 16, 14:12    [19032812]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
Glory
Member

Откуда:
Сообщений: 104751
FastTI
Не много не понял...

Что именно ?
8 апр 16, 14:14    [19032834]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
FastTI
Member

Откуда:
Сообщений: 44
Glory
будет 12 записей - по одной на кадждый час

не понял логики... как вы это видите? и что это даст? можно подробнее пожалуйста (возможно вы видите выход которого я не усматриваю)
8 апр 16, 14:17    [19032863]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
Glory
Member

Откуда:
Сообщений: 104751
FastTI
как вы это видите? и что это даст?

Это даст решение для " что статус может начинаться в один час а заканчиваться в другой. Как сделать так чтобы часть статуса попадала в один час а вторая часть во второй?"
Потому что несколько записей будет попадать в один временной промежуток
8 апр 16, 14:19    [19032881]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
FastTI
Member

Откуда:
Сообщений: 44
Glory, создать таблицу и (триггером?) записывать в нее по две записи одного и того же промежутка?
8 апр 16, 14:22    [19032912]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
Glory
Member

Откуда:
Сообщений: 104751
FastTI
создать таблицу и (триггером?) записывать в нее по две записи одного и того же промежутка?

Каким еще триггером ?
Вы умеете соединять несколько таблиц в запросе ?
8 апр 16, 14:24    [19032918]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
FastTI
Member

Откуда:
Сообщений: 44
Glory, да... просто приджойнить таблицу саму с собой? я не до конца просто представляю реализацию... потому могу подтупливать
8 апр 16, 14:25    [19032926]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
Glory
Member

Откуда:
Сообщений: 104751
FastTI
да... просто приджойнить таблицу саму с собой?

С какой с собой ?
"используйте вспомогательную таблицу/запрос, где у вас будет 12 записей - по одной на кадждый час"
8 апр 16, 14:26    [19032934]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
FastTI
Member

Откуда:
Сообщений: 44
Glory, а как эти записи будут попадать в таблицу вспомогательную??? данные пишутся в исходную таблицу постоянно.
8 апр 16, 14:29    [19032957]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
Glory
Member

Откуда:
Сообщений: 104751
FastTI
а как эти записи будут попадать в таблицу вспомогательную???данные пишутся в исходную таблицу постоянно.

Мда.
8 апр 16, 14:30    [19032973]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
iap
Member

Откуда: Москва
Сообщений: 47105
FastTI
Glory, а как эти записи будут попадать в таблицу вспомогательную??? данные пишутся в исходную таблицу постоянно.
Эти данные вы занесёте своими руками один раз.
8 апр 16, 14:31    [19032977]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
FastTI
Member

Откуда:
Сообщений: 44
iap
Эти данные вы занесёте своими руками один раз.

Хорошо. Что именно должно быть во вспомогательной таблице. Почему записей 12?
8 апр 16, 14:39    [19033049]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
Glory
Member

Откуда:
Сообщений: 104751
FastTI
Почему записей 12?

Тут вы правы - записей должно быть по количеству часов в сутках. Т.е. 24
8 апр 16, 14:48    [19033142]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
FastTI
Member

Откуда:
Сообщений: 44
Glory,
Glory
записей должно быть по количеству часов в сутках. Т.е. 24

вот я и не понял сначала...

Спасибо вам. Попробую прикрутить
8 апр 16, 15:01    [19033255]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
FastTI
Member

Откуда:
Сообщений: 44
Glory,
Спасибо вам еще раз, я воспользовался вашим советом.
Но возник вопрос такого плана если статус длиться больше 2-х часов...

например с 12:30 до 14: 30

тогда я получу 30 минут одного часа и 30 другого а час между 13:00 и 14:00 не попадает...
8 апр 16, 16:08    [19033945]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
FastTI,

в join с таблицей с часами проверяйте вхождение в период. Ну и вопрос же если больше суток статус
8 апр 16, 16:17    [19034031]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
FastTI
Member

Откуда:
Сообщений: 44
TaPaK,
TaPaK
в join с таблицей с часами проверяйте вхождение в период. Ну и вопрос же если больше суток статус



сейчас у меня таблица часами от 0 до 23

при join я указываю условия
 JOIN Plgok_hours ph on (ph.Hours = DATEPART(HH,START_TIMESTAMP) or ph.Hours = DATEPART(HH,END_TIMESTAMP))


об этом вхождении речь?
8 апр 16, 16:50    [19034245]     Ответить | Цитировать Сообщить модератору
 Re: Почасовое разбиение  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
FastTI,

ph.Hours BETEEN DATEPART(HH,START_TIMESTAMP) AND DATEPART(HH,END_TIMESTAMP)

ну и по прежнему переход с 23 на 0.. надо обрабатывать
8 апр 16, 16:54    [19034262]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить