Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Korzhech Member Откуда: Сообщений: 6 |
Доброго дня, уважаемые форумчане. Есть задача, помогите, пожалуйста понять как реализовать, возможно ли это Существует таблица с данными по сбоям оборудования, т.е номер оборудования, начало и окончание сбоя(длительность может достигать нескольких дней, но и может быть несколько минут). Необходимо разложить каждый простой на часы, табличка исходная и что нужно получить ниже. Исходник:
Что требуется:
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1 авг 17, 11:06 [20690663] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8322 |
Korzhech, создайте почасовой календарь и считайте count(*) между временем от и до. |
1 авг 17, 11:24 [20690799] Ответить | Цитировать Сообщить модератору |
Korzhech Member Откуда: Сообщений: 6 |
Владислав Колосов, Спасибо за совет, но честно говоря не понял как продолжительность считать count(*) -ом |
1 авг 17, 14:14 [20691741] Ответить | Цитировать Сообщить модератору |
Browny Member Откуда: Сообщений: 53 |
Если я правильно понял пример, группировать (считать, сколько конкретный станок дал сбоев за один час) не требуется - надо просто вывести перечень сбоев, "привязав" их к часам? Вариант уже был предложен - часовое расписание... declare @sb table(device_id int,start_date datetime,end_date datetime,sboy_id int,finished int); set dateformat dmy; insert into @sb(device_id,start_date,end_date,sboy_id,finished) Select 11270, '09.01.2017 8:35', '09.01.2017 19:35', 101, 1 union Select 11270, '09.01.2017 19:35', '10.01.2017 8:35', 100, 1 union Select 11272, '10.01.2017 8:35', '10.01.2017 19:35', 1, 1 union Select 11272, '11.01.2017 8:35', '11.01.2017 19:35', 23, 1 union Select 11272, '11.01.2017 19:35', '12.01.2017 8:35', 58, 1 union Select 11270, '12.01.2017 8:35', '12.01.2017 10:05', 101, 1 union Select 11270, '12.01.2017 10:05', '12.01.2017 10:15', 34, 1 union Select 11270, '12.01.2017 10:15', '12.01.2017 19:35', 28, 1 --Select * from @sb ;with borderdates(maxdate,startdate) as ( Select max(end_date), dateadd(hour, datepart(hour, min(start_date)), Cast(Floor(Cast(min(start_date) as Float)) as Datetime)) from @sb ), schedule(datestart, datefinish) as ( Select borderdates.startdate, dateadd(hour, 1, borderdates.startdate) from borderdates union all Select schedule.datefinish, dateadd(hour, 1, schedule.datefinish) from schedule cross join borderdates where schedule.datefinish <= borderdates.maxdate ) Select Cast(Floor(Cast(s.datestart as float)) as datetime) as [date], Right('0' + convert(varchar,datepart(hour, s.datestart)) + ':00',5) as [time], b.* from schedule s inner join @sb b on s.datestart between b.start_date and b.end_date order by s.datestart asc |
1 авг 17, 14:27 [20691796] Ответить | Цитировать Сообщить модератору |
Korzhech Member Откуда: Сообщений: 6 |
Browny, Верно + подсчитать сколько минут он простоял в конкретный час в минутах(duration, mi) |
1 авг 17, 14:37 [20691831] Ответить | Цитировать Сообщить модератору |
Browny Member Откуда: Сообщений: 53 |
declare @sb table(device_id int,start_date datetime,end_date datetime,sboy_id int,finished int); set dateformat dmy; insert into @sb(device_id,start_date,end_date,sboy_id,finished) Select 11270, '09.01.2017 8:35', '09.01.2017 19:35', 101, 1 union Select 11270, '09.01.2017 19:35', '10.01.2017 8:35', 100, 1 union Select 11272, '10.01.2017 8:35', '10.01.2017 19:35', 1, 1 union Select 11272, '11.01.2017 8:35', '11.01.2017 19:35', 23, 1 union Select 11272, '11.01.2017 19:35', '12.01.2017 8:35', 58, 1 union Select 11270, '12.01.2017 8:35', '12.01.2017 10:05', 101, 1 union Select 11270, '12.01.2017 10:05', '12.01.2017 10:15', 34, 1 union Select 11270, '12.01.2017 10:15', '12.01.2017 19:35', 28, 1 ;with borderdates(maxdate,startdate) as ( Select max(end_date), dateadd(hour, datepart(hour, min(start_date)), Cast(Floor(Cast(min(start_date) as Float)) as Datetime)) from @sb ), schedule(datestart, datefinish) as ( Select borderdates.startdate, dateadd(hour, 1, borderdates.startdate) from borderdates union all Select dateadd(hour, 1, schedule.datestart), dateadd(hour, 2, schedule.datestart) from schedule cross join borderdates where schedule.datefinish <= borderdates.maxdate ) Select Cast(Floor(Cast(s.datestart as float)) as datetime) as [date], Right('0' + convert(varchar,datepart(hour, s.datestart)) + ':00',5) as [time], s.datestart, s.datefinish, datediff(minute, case when b.start_date between s.datestart and s.datefinish then b.start_date else s.datestart end, case when b.end_date between s.datestart and s.datefinish then b.end_date else s.datefinish end ) as [duration,min] b.* from schedule s inner join @sb b on not (s.datestart > b.end_date or s.datefinish < b.start_date) order by sboy_id, s.datestart Нуждается в выверке. |
1 авг 17, 15:27 [20691996] Ответить | Цитировать Сообщить модератору |
Korzhech Member Откуда: Сообщений: 6 |
Browny, Расчет верный) Спасибо только встречается вот такая ошибка (строк обработано: 34) Сообщение 530, уровень 16, состояние 1, строка 30 The statement terminated. The maximum recursion 100 has been exhausted before statement completion. |
1 авг 17, 17:08 [20692489] Ответить | Цитировать Сообщить модератору |
Browny Member Откуда: Сообщений: 53 |
Поставьте в самом конце, ниже сортировки: option (maxrecursion 32767). К сожалению, увеличить значение нельзя, да и рекурсия сильно напрягает сервер. Если нужен почасовой график за много лет, стройте его без рекурсии. |
1 авг 17, 17:56 [20692615] Ответить | Цитировать Сообщить модератору |
Minamoto Member Откуда: Москва Сообщений: 1162 |
С каких это пор maxrecursion 0 перестало работать? Другое дело, что выставлять его в 0 нужно на свой страх и риск, предохранившись от бесконечной рекурсии. |
||
1 авг 17, 18:13 [20692668] Ответить | Цитировать Сообщить модератору |
Browny Member Откуда: Сообщений: 53 |
Minamoto, информация была полезной, спасибо. Нет, в MSDN не забанили :) |
2 авг 17, 10:57 [20694093] Ответить | Цитировать Сообщить модератору |
rnk Member Откуда: Сообщений: 126 |
Почасовой график без рекурсии: with tnum as ( select ROW_NUMBER() OVER (order by object_id) as num from sys.all_columns ) select DATEADD(HH,num-1,convert(datetime,'20170101')) as datehour from tnum |
||
2 авг 17, 16:31 [20695536] Ответить | Цитировать Сообщить модератору |
Korzhech Member Откуда: Сообщений: 6 |
rnk, Спасибо! Только ни фига не понимаю как прикрутить к данным, через join ?? |
4 авг 17, 10:01 [20699731] Ответить | Цитировать Сообщить модератору |
rnk Member Откуда: Сообщений: 126 |
Угу. Так же, как было прикручено сверху |
||
4 авг 17, 10:35 [20699854] Ответить | Цитировать Сообщить модератору |
andrey odegov Member Откуда: Сообщений: 473 |
set nocount on; use tempdb; go declare @t table( dv int not null check(dv>0), bg datetime not null, fn datetime null, fid int not null, rcv bit not null, check(bg<=isnull(fn,getdate())) ); insert @t(dv,bg,fn,fid,rcv) values(11270,'20170109 08:35','20170109 19:35',101,1), (11270,'20170109 19:35','20170110 08:35',100,1), (11272,'20170110 08:35','20170110 19:35',1,1), (11272,'20170110 19:23','20170110 19:35',-10,1), (11272,'20170110 19:35','20170111 08:35',-10,1), (11272,'20170111 08:35','20170111 19:35',23,1), (11272,'20170111 19:35','20170112 08:35',58,1), (11270,'20170112 08:35','20170112 10:05',103,1), (11270,'20170112 10:05','20170112 10:15',34,1), (11270,'20170112 10:15','20170112 19:35',28,1), (42170,'20170323 23:59','20170324 00:05',170,1), (11270,'20170803 19:15',null,5,0); with b as(select dv,bg,fid,isnull(fn,getdate()) fn, dateadd(hh,datediff(hh,0,bg)+1,0) hh from @t union all select dv,bg,fid,fn,dateadd(hh,1,hh) from b where hh<fn), c as(select *, iif(datediff(mi,bg,fn)<60,datediff(mi,bg,fn), iif(fn<hh,datepart(mi,fn), iif(datediff(mi,bg,hh)<60,datediff(mi,bg,hh),60))) mi from b) select dv,fid,dateadd(hh,-1,hh) hh,sum(mi) mi from c --where fid in(100,101,1,170,34,-10) group by dv,bg,hh,fid order by dv,bg,hh option(maxrecursion 0); |
5 авг 17, 17:47 [20702707] Ответить | Цитировать Сообщить модератору |
andrey odegov Member Откуда: Сообщений: 473 |
В предыдущем варианте не правильно вычислял столбец mi (под спойлером)
|
|
6 авг 17, 09:13 [20703283] Ответить | Цитировать Сообщить модератору |
andrey odegov Member Откуда: Сообщений: 473 |
Вариант с виртуальной таблицей чисел:
|
|
6 авг 17, 09:53 [20703298] Ответить | Цитировать Сообщить модератору |
Korzhech Member Откуда: Сообщений: 6 |
andrey odegov, The INSERT statement conflicted with the CHECK constraint "CK__#BD716DF9__BF59B66B". The conflict occurred in database "tempdb", table "@t". Как победить ? |
9 авг 17, 10:58 [20710206] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
Korzhech, не совать bg<=isnull(fn,getdate()) |
9 авг 17, 11:31 [20710271] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |