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

Откуда:
Сообщений: 77
Помогите пожалуйста придумать мудреный запрос.

Задача:
Есть таблица (результат запроса) следующего типа
Картинка с другого сайта.
Столбцы: nothold и hold - время простоя с .. до. diff - их разница в минутах. Далее рабочее время оборудования d1-d2 с t1 до t2 (Например в данном случае ПН-ВС с 9:00 до 20:00).

Необходимо посчитать время простоя в рабочее время (т.е. сопоставить дни недели, время и т.п.)

Есть также оборудование 24х7:
Картинка с другого сайта.
Значит, что время простоя равно diff.

Еще пример:
Картинка с другого сайта.
20 янв 15, 00:14    [17140656]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
aleks2
Guest
Ну и чего тут загадочного?
1. Считаешь ЦЕЛЫЕ дни.
2. Обрабатываешь два граничных дня отдельно.
20 янв 15, 07:02    [17140899]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
Pim.
Member

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

Честно, не очень понял. Не могли бы вы пояснить, если не сложно.
20 янв 15, 07:05    [17140901]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
Pim.
aleks2,

Честно, не очень понял. Не могли бы вы пояснить, если не сложно.

a я вот не понял это

Pim.
Необходимо посчитать время простоя в рабочее время (т.е. сопоставить дни недели, время и т.п.)


что надо на выходе то КОНКРЕТНО ? (пример таблицы)

а то у Вас и во входных данных сопоставлены дни недели,время и т п
20 янв 15, 07:39    [17140926]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
aleks2
Guest
Pim.
aleks2,

Честно, не очень понял. Не могли бы вы пояснить, если не сложно.


1. Каждые ЦЕЛЫЕ сутки ( datediff(day, hold, nothold) - 2 ) станок стоял одинаково: c t1 до t2.
2. Двое пограничных суток hold и nothold станок стоял немножко меньше. Но вычислить - нема проблем.
3. Ну и есть особый случай сутки hold = nothold. Его считать надо отдельно.
20 янв 15, 07:51    [17140941]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
Pim.
Member

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

Посчитать время простоя в минутах в рабочее время.
У меня есть время, в которое оборудование простаивало (nothold-hold) и его рабочее время (d1-d2 с t1 до t2), т.е. в которое оно должно обязательно работать.
Мне нужно из всего времени простоя вычислить только то, которое входило в рабочее время (в минутах).

Например для этой таблицы
Картинка с другого сайта.

Результирующим столбцом будет следующее:
min
0
0
478
20 янв 15, 07:56    [17140949]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
Pim.
Member

Откуда:
Сообщений: 77
aleks2
1. Каждые ЦЕЛЫЕ сутки ( datediff(day, hold, nothold) - 2 ) станок стоял одинаково: c t1 до t2.
2. Двое пограничных суток hold и nothold станок стоял немножко меньше. Но вычислить - нема проблем.
3. Ну и есть особый случай сутки hold = nothold. Его считать надо отдельно.


Спасибо, буду пробовать.
20 янв 15, 07:58    [17140953]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
bald56rus
Member

Откуда:
Сообщений: 194
Pim.,

в будущем планируется подобная задача, если не сложно опубликуй что у Тебя получиться
20 янв 15, 11:17    [17141743]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
Pim.
Member

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

Хорошо.
20 янв 15, 11:36    [17141912]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
-- делаем календарь
IF OBJECT_ID('tempdb..#calendar') IS NOT NULL DROP TABLE #calendar
CREATE table #calendar ([date] datetime not null primary key,[day] varchar(10))
insert into #calendar
select '20140101'
,CASE DATEName(dw,'20140101')
 WHEN 'Monday'  THEN 'Пн'
 WHEN 'Tuesday' THEN 'Вт'
 WHEN 'Wednesday' THEN 'Ср'
 WHEN 'Thursday' THEN 'Чт'
 WHEN 'Friday' THEN 'Пт'
 WHEN 'Saturday' THEN 'Сб'
 WHEN 'Sunday' THEN 'Вс'
END
WHILE NOT EXISTS(SELECT * FROM #calendar WHERE [date] = cast(GETDATE() as date))
	INSERT INTO #calendar
	SELECT top 1 DATEADD(dd,1,[date])
	,CASE DATEName(dw,DATEADD(dd,1,[date]))
	 WHEN 'Monday'  THEN 'Пн'
	 WHEN 'Tuesday' THEN 'Вт'
	 WHEN 'Wednesday' THEN 'Ср'
	 WHEN 'Thursday' THEN 'Чт'
	 WHEN 'Friday' THEN 'Пт'
	 WHEN 'Saturday' THEN 'Сб'
	 WHEN 'Sunday' THEN 'Вс'
	END 
	from #calendar order by [date] desc  	


-- эмуляция данных
declare @table table (id int,nothold datetime,hold datetime,d1 varchar(10),d2 varchar(10),t1 varchar(10),t2 varchar(10))
insert into @table values(1,'2014-12-25 20:28:28.940','2014-12-25 20:28:59.493','Пн','Вс','09:00','20:00')
,(2,'2014-12-25 20:36:47.940','2014-12-25 21:18:19.493','Пн','Вс','09:00','20:00')
,(3,'2014-12-25 22:57:07.940','2014-12-26 16:58:11.493','Пн','Вс','09:00','20:00')
,(4,'2015-01-14 15:10:50.940','2015-01-19 09:51:37.493','24x7','24x7','00:00','23:59')


-- запрос простоев
select t.id,t.hold,t.nothold,t.d1,t.t1,t.d2,t.t2
,sum(case when Diff.val < 0 then 0 else Diff.val end) as DiffVal
from @table t
cross apply(select top 1 [date] from #calendar where ([day] = t.d1 or t.d1 = '24x7') and [date] < t.hold order by [DATE] desc) db
cross apply(select top 1 [date] from #calendar where ([day] = t.d2 or t.d2 = '24x7') and [date] > db.[date] order by [DATE] asc) de
cross apply(select [date] from #calendar where [date] between db.date and de.date) d
cross apply(select DATEDIFF(MI,case when d.date+cast(t1 as time) < t.nothold then t.nothold 
           else d.date+cast(t1 as time) end,case when d.date+cast(t2 as time) > t.hold then t.hold else d.date+cast(t2 as time) end) val) as Diff 
where t.id in (1,2,3,4)
group by t.id,t.hold,t.nothold,t.d1,t.t1,t.d2,t.t2
20 янв 15, 14:21    [17142999]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
Pim.
Member

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

Спасибо большое за решение.
Только вот моих знаний определенно не хватает, чтобы понять его полностью. Если Вам не сложно, не могли бы вкратце объяснить решение.
21 янв 15, 16:50    [17149985]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
Pim.
Member

Откуда:
Сообщений: 77
aleks2

1. Каждые ЦЕЛЫЕ сутки ( datediff(day, hold, nothold) - 2 ) станок стоял одинаково: c t1 до t2.
2. Двое пограничных суток hold и nothold станок стоял немножко меньше. Но вычислить - нема проблем.
3. Ну и есть особый случай сутки hold = nothold. Его считать надо отдельно.


Вы, кстати, никак не учли, что рабочее время оборудования не всегда с понедельника по воскресенье, т.е. надо еще учесть рабочие дни.
21 янв 15, 17:37    [17150397]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
Pim.
Member

Откуда:
Сообщений: 77
Pim.,

Вопрос все еще актуален. Пока мне не удалось решить задачу.
21 янв 15, 18:34    [17150691]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
aleks2
Guest
Pim.
aleks2
1. Каждые ЦЕЛЫЕ сутки ( datediff(day, hold, nothold) - 2 ) станок стоял одинаково: c t1 до t2.
2. Двое пограничных суток hold и nothold станок стоял немножко меньше. Но вычислить - нема проблем.
3. Ну и есть особый случай сутки hold = nothold. Его считать надо отдельно.


Вы, кстати, никак не учли, что рабочее время оборудования не всегда с понедельника по воскресенье, т.е. надо еще учесть рабочие дни.

Дарагуля, за скромную мзду - я фсе учту.
22 янв 15, 06:38    [17151931]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
bald56rus
Member

Откуда:
Сообщений: 194
Pim.
aleks2
1. Каждые ЦЕЛЫЕ сутки ( datediff(day, hold, nothold) - 2 ) станок стоял одинаково: c t1 до t2.
2. Двое пограничных суток hold и nothold станок стоял немножко меньше. Но вычислить - нема проблем.
3. Ну и есть особый случай сутки hold = nothold. Его считать надо отдельно.


Вы, кстати, никак не учли, что рабочее время оборудования не всегда с понедельника по воскресенье, т.е. надо еще учесть рабочие дни.


моих знаний тоже не совсем достаточно, я поступил следующим образом: "скопипастил" и запустил на выполнения что бы посмотреть что происходит. стало яснее как что происходит. на сколько я понял логика работы следующая создается временная таблица календарь, с необходимым периодом с по текущую дату:
+
-- делаем календарь
IF OBJECT_ID('tempdb..#calendar') IS NOT NULL DROP TABLE #calendar
CREATE table #calendar ([date] datetime not null primary key,[day] varchar(10))
insert into #calendar
select '20140101'
,CASE DATEName(dw,'20140101')
 WHEN 'Monday'  THEN 'Пн'
 WHEN 'Tuesday' THEN 'Вт'
 WHEN 'Wednesday' THEN 'Ср'
 WHEN 'Thursday' THEN 'Чт'
 WHEN 'Friday' THEN 'Пт'
 WHEN 'Saturday' THEN 'Сб'
 WHEN 'Sunday' THEN 'Вс'
END
цикл для заполнения календаря до текущего момента с '20140101'
WHILE NOT EXISTS(SELECT * FROM #calendar WHERE [date] = cast(GETDATE() as date)) --цикл для заполнения 
	INSERT INTO #calendar
	SELECT top 1 DATEADD(dd,1,[date])
	,CASE DATEName(dw,DATEADD(dd,1,[date]))
	 WHEN 'Monday'  THEN 'Пн'
	 WHEN 'Tuesday' THEN 'Вт'
	 WHEN 'Wednesday' THEN 'Ср'
	 WHEN 'Thursday' THEN 'Чт'
	 WHEN 'Friday' THEN 'Пт'
	 WHEN 'Saturday' THEN 'Сб'
	 WHEN 'Sunday' THEN 'Вс'
	END 
	from #calendar order by [date] desc 

следующую часть описывать не буду, временная таблица для примера
а вот дальше мне тоже было не понятно я понял это так:
+

select t.id,t.hold,t.nothold,t.d1,t.t1,t.d2,t.t2
,sum(case when Diff.val < 0 then 0 else Diff.val end) as DiffVal
from @table t -- главный запрос для вывода необходимой тебе информации
cross apply(select top 1 [date] from #calendar where ([day] = t.d1 or t.d1 = '24x7') and [date] < t.hold order by [DATE] desc) db --данный запрос выполняется для каждой строки и "главного запроса" он возвращает дату из календаря которая меньше даты начала простоя и день соответствует графику(d1)
cross apply(select top 1 [date] from #calendar where ([day] = t.d2 or t.d2 = '24x7') and [date] > db.[date] order by [DATE] asc) de -- следующая дата после начала простоя которая входит в рабочий период
cross apply(select [date] from #calendar where [date] between db.date and de.date) d -- получаем промежуток между дат из календаря
cross apply(select DATEDIFF(MI,case when d.date+cast(t1 as time) < t.nothold then t.nothold
else d.date+cast(t1 as time) end,case when d.date+cast(t2 as time) > t.hold then t.hold else d.date+cast(t2 as time) end) val) as Diff --здесь считается время простоя
where t.id in (1,2,3,4)
group by t.id,t.hold,t.nothold,t.d1,t.t1,t.d2,t.t2

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

P.S.: он работает не совсем так как мне необходимо(наверно и Тебе тоже) на эмулируемых данных для 4 записи время простоя в минутах 591, а должно быть больше(при графике работы 24*7 должна быть 6881)
22 янв 15, 06:43    [17151932]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
Pim.
Вы, кстати, никак не учли, что рабочее время оборудования не всегда с понедельника по воскресенье, т.е. надо еще учесть рабочие дни.

Сделайте в календаре поле IsWork и проставьте 1 у фактически рабочих дней (может вы и в день рождения директора не работаете :)) а в запросе выбирайте из календаря только рабочие дни
22 янв 15, 07:12    [17151955]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
aleks2
Guest
Ладно, тредстартеру повезло - на работе сервер не работает

SET DATEFIRST 1
set nocount on;

-- эмуляция данных
declare @table table (id int,nothold datetime,hold datetime,d1 varchar(10),d2 varchar(10),t1 time,t2 time)

-- слегка исправляем данные для 'Пн' = 1. Привыкай страдалец делать правильно
insert into @table values
 (1,'2014-12-25 20:28:28.940','2014-12-25 20:28:59.493',1,7,'09:00','20:00')
,(2,'2014-12-25 19:36:47.940','2014-12-25 21:18:19.493',1,7,'09:00','20:00')
,(3,'2014-12-25 22:57:07.940','2014-12-26 16:58:11.493',1,7,'09:00','20:00')
,(4,'2015-01-14 15:10:50.940','2015-01-19 09:51:37.493',1,7,'00:00','23:59')

select * from @table 

-- эту таблицу настоящие джигиты имеют постоянной
declare @nums table(n int primary key clustered);
declare @n int = 100;
while @n>0
begin
  insert @nums values(@n);
  set @n = @n - 1;
end;

-- вопщем то это можно сложить в более компактный запрос, но специально для чайника "по шагам"
;with
-- полные дни простоя
fulldays as (select id, 
                ( select count(*) 
                     from ( select (n + datepart(dw, t.nothold ) - 2) % 7 + 1 as i from @nums where n <= datediff(day, nothold, hold ) - 1 ) x
                     where i between t.d1 and t.d2
                ) * datediff(minute, t.t1, t.t2) as [простой]
  
               from @table t where datediff(day, nothold, hold ) > 1 
            )
,
-- простой в первый день интервала
firstday as (select id, 
                    datediff( minute, 
                               ( select max(tm) from ( values(t.t1),(cast(t.nothold as time)) ) x(tm) )
                             , t.t2
                             ) as [простой]
               from @table t where datepart(dw, t.nothold ) between t.d1 and t.d2
                                   and datediff(day, nothold, hold ) > 0
                                   and cast(t.nothold as time) <= t.t2
            )          
,            
-- простой в последний день интервала
lastday as (select id, 
                    datediff( minute, 
                              t.t1
                            , ( select min(tm) from ( values(t.t2),(cast(t.nothold as time)) ) x(tm) )
                            ) as [простой]
               from @table t where datepart(dw, t.hold ) between t.d1 and t.d2
                                   and datediff(day, nothold, hold ) > 0
                                   and cast(t.hold as time) > t.t1
            )          
,
-- простой, когда первый и последний день интервала совпадают
sameday as (select id, 
                    datediff( minute, 
                              ( select max(tm) from ( values(t.t1),(cast(t.nothold as time)) ) x(tm) )
                             ,( select min(tm) from ( values(t.t2),(cast(t.hold as time)) ) x(tm) )
                             ) as [простой]
               from @table t where datepart(dw, t.hold ) between t.d1 and t.d2
                                   and datediff(day, nothold, hold ) = 0
                                   and cast(t.nothold as time) <= t.t2
                                   and cast(t.hold as time) > t.t1
            )          
select id, sum([простой]) as [простой]
  from 
   ( select * from fulldays
     union all
     select * from firstday
     union all
     select * from lastday
     union all
     select * from sameday
   ) z
   group by id
22 янв 15, 07:41    [17151984]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
bald56rus
Member

Откуда:
Сообщений: 194
LexusR я здесь 17151932 правильно понял блок cross apply? не пойму почему не входит время за период с 19 по 20 т.е. по четвертой записи время простоя считается за минусом 591 минуты.
22 янв 15, 12:58    [17153606]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
Pim
P.S.: он работает не совсем так как мне необходимо(наверно и Тебе тоже) на эмулируемых данных для 4 записи время простоя в минутах 591, а должно быть больше(при графике работы 24*7 должна быть 6881)

bald56rus
я здесь 17151932 правильно понял блок cross apply? не пойму почему не входит время за период с 19 по 20 т.е. по четвертой записи время простоя считается за минусом 591 минуты.

поправил
-- делаем календарь
IF OBJECT_ID('tempdb..#calendar') IS NOT NULL DROP TABLE #calendar
CREATE table #calendar ([date] datetime not null primary key,[day] varchar(10))
insert into #calendar
select '20140101'
,CASE DATEName(dw,'20140101')
 WHEN 'Monday'  THEN 'Пн'
 WHEN 'Tuesday' THEN 'Вт'
 WHEN 'Wednesday' THEN 'Ср'
 WHEN 'Thursday' THEN 'Чт'
 WHEN 'Friday' THEN 'Пт'
 WHEN 'Saturday' THEN 'Сб'
 WHEN 'Sunday' THEN 'Вс'
END
WHILE NOT EXISTS(SELECT * FROM #calendar WHERE [date] = cast(GETDATE() as date))
	INSERT INTO #calendar
	SELECT top 1 DATEADD(dd,1,[date])
	,CASE DATEName(dw,DATEADD(dd,1,[date]))
	 WHEN 'Monday'  THEN 'Пн'
	 WHEN 'Tuesday' THEN 'Вт'
	 WHEN 'Wednesday' THEN 'Ср'
	 WHEN 'Thursday' THEN 'Чт'
	 WHEN 'Friday' THEN 'Пт'
	 WHEN 'Saturday' THEN 'Сб'
	 WHEN 'Sunday' THEN 'Вс'
	END 
	from #calendar order by [date] desc  	


-- эмуляция данных
declare @table table (id int,nothold datetime,hold datetime,d1 varchar(10),d2 varchar(10),t1 varchar(10),t2 varchar(10))
insert into @table values(1,'2014-12-25 20:28:28.940','2014-12-25 20:28:59.493','Пн','Вс','09:00','20:00')
,(2,'2014-12-25 20:36:47.940','2014-12-25 21:18:19.493','Пн','Вс','09:00','20:00')
,(3,'2014-12-25 22:57:07.940','2014-12-26 16:58:11.493','Пн','Вс','09:00','20:00')
,(4,'2015-01-14 15:10:50.940','2015-01-19 09:51:37.493','24x7','24x7','00:00','23:59')


-- запрос простоев
select t.id,t.nothold,t.hold,t.d1,t.t1,t.d2,t.t2
,sum(case when Diff.val < 0 then 0 else Diff.val end) as DiffVal
from @table t
cross apply(select top 1 [date] from #calendar where ([day] = t.d1 or t.d1 = '24x7') and [date] < t.nothold order by [DATE] desc) db
cross apply(select top 1 [date] from #calendar where ([day] = t.d2 or t.d2 = '24x7') and [date] > t.hold order by [DATE] asc) de
cross apply(select [date] from #calendar where [date] between db.date and de.date) d
cross apply(select DATEDIFF(MI,case when d.date+cast(t1 as time) < t.nothold then t.nothold 
           else d.date+cast(t1 as time) end,case when d.date+cast(t2 as time) > t.hold then t.hold else d.date+cast(t2 as time) end) val) as Diff 
where t.id in (1,2,3,4)
group by t.id,t.nothold,t.hold,t.d1,t.t1,t.d2,t.t2
22 янв 15, 13:46    [17154023]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
bald56rus
Member

Откуда:
Сообщений: 194
LexusR,
вот моя адаптация Вашего скрипта:
DECLARE @StartDate DATE
SET @StartDate='01.01.2015'
DECLARE @StartDownTime DATETIME
SET @StartDownTime = '14.01.2015 15:10:50'
DECLARE @StopDownTime DATETIME
SET @StopDownTime = '19.01.2015 09:51:37'
DECLARE @t1 TABLE([id] INT IDENTITY, [start] DATETIME, [stop] DATETIME, d1 VARCHAR(2), t1 TIME, d2 VARCHAR(2), t2 TIME)

IF OBJECT_ID('tempdb..#calendar') IS NOT NULL DROP TABLE #calendar

CREATE TABLE #calendar (
  [date] DATETIME NOT NULL PRIMARY KEY,
  [day] VARCHAR(20) COLLATE Cyrillic_General_CI_AS
)
INSERT INTO #calendar
  SELECT
    @StartDate,
    CASE DATENAME(WEEKDAY, @StartDate)
      WHEN 'Понедельник' THEN 'Пн'
      WHEN 'Вторник' THEN 'Вт'
      WHEN 'Среда' THEN 'Ср'
      WHEN 'Четверг' THEN 'Чт'
      WHEN 'Пятница' THEN 'Пт'
      WHEN 'Суббота' THEN 'Сб'
      WHEN 'Воскресенье' THEN 'Вс'
    END
WHILE NOT EXISTS
  (
    SELECT
      *
    FROM #calendar
    WHERE [date] = CAST(GETDATE() AS DATE)
  )
  INSERT INTO #calendar
    SELECT TOP 1
      DATEADD(DAY, 1, [date]),
      CASE DATENAME(WEEKDAY, DATEADD(dd, 1, [date]))
        WHEN 'Понедельник' THEN 'Пн'
        WHEN 'Вторник' THEN 'Вт'
        WHEN 'Среда' THEN 'Ср'
        WHEN 'Четверг' THEN 'Чт'
        WHEN 'Пятница' THEN 'Пт'
        WHEN 'Суббота' THEN 'Сб'
        WHEN 'Воскресенье' THEN 'Вс'
      END
    FROM #calendar
    ORDER BY [date] DESC

INSERT @t1 (start, [stop], d1,t1,d2,t2)
  VALUES (@StartDownTime, @StopDownTime,'пн','00:00','вс','23:59');

select
  t.id,
  t.[start],
  t.[stop],
  t.d1,
  t.t1,
  t.d2,
  t.t2,
  (
    case
      when diff.val < 0 then 0
      else diff.val
    end
  ) as diffval
from @t1 as t
cross apply ( select top 1 [date] from #calendar where [day] = t.d1 and [date] < t.start order by [date] desc) db
cross apply ( select top 1 [date] from #calendar where [day] = t.d2 and [date] > db.[date] order by [date] asc) de
cross apply ( select [date] from #calendar where [date] between db.date and de.[date] ) d
cross apply 
  (
  select
  datediff
    (
      mi,
      case
        when d.date + cast(t1 as datetime) < t.[start] then t.[start]
        else d.date + cast(t1 as datetime)  
      end,
      case
        when d.date + cast(t2 as datetime) > t.[stop] then t.[stop]
        else d.date + cast(t2 as datetime)
      end
    ) val) as diff

а это результат его работы:
idstartstopd1t1d2t2diffval
114.01.2015 15:10:5019.01.2015 9:51:37пн00:00:00вс23:59:000
114.01.2015 15:10:5019.01.2015 9:51:37пн00:00:00вс23:59:000
114.01.2015 15:10:5019.01.2015 9:51:37пн00:00:00вс23:59:00529
114.01.2015 15:10:5019.01.2015 9:51:37пн00:00:00вс23:59:001439
114.01.2015 15:10:5019.01.2015 9:51:37пн00:00:00вс23:59:001439
114.01.2015 15:10:5019.01.2015 9:51:37пн00:00:00вс23:59:001439
114.01.2015 15:10:5019.01.2015 9:51:37пн00:00:00вс23:59:001439

не понимаю почему нет периода простоя за период с 19.01.2015 00.00 по 19.01.2015 09:51?подскажите пож. что я делаю не так. заранее спасибо
22 янв 15, 14:42    [17154497]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
LexusR
Pim
P.S.: он работает не совсем так как мне необходимо(наверно и Тебе тоже) на эмулируемых данных для 4 записи время простоя в минутах 591, а должно быть больше(при графике работы 24*7 должна быть 6881)

bald56rus
я здесь 17151932 правильно понял блок cross apply? не пойму почему не входит время за период с 19 по 20 т.е. по четвертой записи время простоя считается за минусом 591 минуты.

еще раз поправил
IF OBJECT_ID('tempdb..#calendar') IS NOT NULL DROP TABLE #calendar
CREATE table #calendar ([date] datetime not null primary key,[day] varchar(10))
insert into #calendar
select '20140101'
,CASE DATEName(dw,'20140101')
 WHEN 'понедельник'  THEN 'Пн'
 WHEN 'вторник' THEN 'Вт'
 WHEN 'среда' THEN 'Ср'
 WHEN 'четверг' THEN 'Чт'
 WHEN 'пятница' THEN 'Пт'
 WHEN 'суббота' THEN 'Сб'
 WHEN 'воскресенье' THEN 'Вс'
END
WHILE NOT EXISTS(SELECT * FROM #calendar WHERE [date] = cast(GETDATE() as date))
	INSERT INTO #calendar
	SELECT top 1 DATEADD(dd,1,[date])
	,CASE DATEName(dw,DATEADD(dd,1,[date]))
	 WHEN 'понедельник'  THEN 'Пн'
	 WHEN 'вторник' THEN 'Вт'
	 WHEN 'среда' THEN 'Ср'
	 WHEN 'четверг' THEN 'Чт'
	 WHEN 'пятница' THEN 'Пт'
	 WHEN 'суббота' THEN 'Сб'
	 WHEN 'воскресенье' THEN 'Вс'
	END
	from #calendar order by [date] desc  	


-- эмуляция данных
declare @table table (id int,nothold datetime2,hold datetime2,d1 varchar(10),d2 varchar(10),t1 varchar(10),t2 varchar(10))
insert into @table values(1,'2014-12-25 20:28:28.940','2014-12-25 20:28:59.493','Пн','Вс','09:00','20:00')
,(2,'2014-12-25 20:36:47.940','2014-12-25 21:18:19.493','Пн','Вс','09:00','20:00')
,(3,'2014-12-25 22:57:07.940','2014-12-26 16:58:11.493','Пн','Вс','09:00','20:00')
,(4,'2015-01-14 15:10:50.940','2015-01-15 15:10:50.940','24x7','24x7','00:00','23:59')

-- запрос простоев
select t.id,t.nothold,t.hold,t.d1,t.t1,t.d2,t.t2
,sum(case when Diff.val < 0 then 0 else Diff.val end) as DiffVal
from @table t
cross apply(select [date] from #calendar where ([day] = t.d1 or t.d1 = '24x7') and [date] < t.hold) db
cross apply(select top 1 [date] from #calendar where ([day] = t.d2 and [date] > db.[date]) or (t.d2 = '24x7' and [date] = db.[date]) order by [DATE] asc) de
cross apply(select [date] from #calendar where [date] between db.date and de.date) d
cross apply(select DATEDIFF(MI,case when d.date + t1 < t.nothold then t.nothold 
           else d.date + t1 end,case when d.date + t2 > t.hold then t.hold else d.date + t2  end) val) as Diff 
where t.id in (1,2,3,4)
group by t.id,t.nothold,t.hold,t.d1,t.t1,t.d2,t.t2
22 янв 15, 17:32    [17155853]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
bald56rus
LexusR,
вот моя адаптация Вашего скрипта:

не понимаю почему нет периода простоя за период с 19.01.2015 00.00 по 19.01.2015 09:51?подскажите пож. что я делаю не так. заранее спасибо

у вас короткий календарь и последняя неделя не цепляется
сделайте запас на календаре
DECLARE @StartDate DATE
SET @StartDate='01.01.2015'
DECLARE @StartDownTime DATETIME
SET @StartDownTime = '14.01.2015 15:10:50'
DECLARE @StopDownTime DATETIME
SET @StopDownTime = '19.01.2015 09:51:37'
DECLARE @t1 TABLE([id] INT IDENTITY, [start] DATETIME, [stop] DATETIME, d1 VARCHAR(2), t1 TIME, d2 VARCHAR(2), t2 TIME)

IF OBJECT_ID('tempdb..#calendar') IS NOT NULL DROP TABLE #calendar

CREATE TABLE #calendar (
  [date] DATETIME NOT NULL PRIMARY KEY,
  [day] VARCHAR(20) COLLATE Cyrillic_General_CI_AS
)
INSERT INTO #calendar
  SELECT
    @StartDate,
    CASE DATENAME(WEEKDAY, @StartDate)
      WHEN 'Понедельник' THEN 'Пн'
      WHEN 'Вторник' THEN 'Вт'
      WHEN 'Среда' THEN 'Ср'
      WHEN 'Четверг' THEN 'Чт'
      WHEN 'Пятница' THEN 'Пт'
      WHEN 'Суббота' THEN 'Сб'
      WHEN 'Воскресенье' THEN 'Вс'
    END
WHILE NOT EXISTS
  (
    SELECT
      *
    FROM #calendar
    WHERE [date] = CAST(GETDATE()+7 AS DATE)  ---!!!!ЗАПАС НА КАЛЕНДАРЕ
  )
  INSERT INTO #calendar
    SELECT TOP 1
      DATEADD(DAY, 1, [date]),
      CASE DATENAME(WEEKDAY, DATEADD(dd, 1, [date]))
        WHEN 'Понедельник' THEN 'Пн'
        WHEN 'Вторник' THEN 'Вт'
        WHEN 'Среда' THEN 'Ср'
        WHEN 'Четверг' THEN 'Чт'
        WHEN 'Пятница' THEN 'Пт'
        WHEN 'Суббота' THEN 'Сб'
        WHEN 'Воскресенье' THEN 'Вс'
      END
    FROM #calendar
    ORDER BY [date] DESC


INSERT @t1 (start, [stop], d1,t1,d2,t2)
  VALUES (@StartDownTime, @StopDownTime,'пн','00:00','вс','23:59');

select
  t.id,
  t.[start],
  t.[stop],
  t.d1,
  t.t1,
  t.d2,
  t.t2,
  d.[date],
  d.[day],
  (
    case
      when diff.val < 0 then 0
      else diff.val
    end
  ) as diffval
from @t1 as t
cross apply(select [date] from #calendar where ([day] = t.d1 or t.d1 = '24x7') and [date] < t.stop) db
cross apply(select top 1 [date] from #calendar where ([day] = t.d2 and [date] > db.[date]) or (t.d2 = '24x7' and [date] = db.[date]) order by [DATE] asc) de
cross apply(select [date],[day] from #calendar where [date] between db.date and de.date) d
cross apply 
  (
  select
  datediff
    (
      mi,
      case
        when d.date + cast(t1 as datetime) < t.[start] then t.[start]
        else d.date + cast(t1 as datetime)  
      end,
      case
        when d.date + cast(t2 as datetime) > t.[stop] then t.[stop]
        else d.date + cast(t2 as datetime)
      end
    ) val) as diff
where diff.val>0
22 янв 15, 17:46    [17155925]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
bald56rus
Member

Откуда:
Сообщений: 194
LexusR
у вас короткий календарь и последняя неделя не цепляется
сделайте запас на календаре ...

спасибо, таким образом все работает, хотя не понимаю почему количество дней в календаре влияет на результат 19 число даже не крайняя дата.в общем будут пытаться до конца понять как это все работает
22 янв 15, 18:01    [17156005]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
bald56rus
LexusR
у вас короткий календарь и последняя неделя не цепляется
сделайте запас на календаре ...

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

пересечения берутся по периодам с понедельника по воскресенье(d1,d2)
а в вашем календаре последнее воскресенье 18.01.2015
и 19 число не попадает в расчет так как следующая неделя не формируется((хотя есть еще дни до сегодня)
22 янв 15, 18:09    [17156044]     Ответить | Цитировать Сообщить модератору
 Re: Время простоя в рабочее время  [new]
bald56rus
Member

Откуда:
Сообщений: 194
LexusR
bald56rus
пропущено...

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

пересечения берутся по периодам с понедельника по воскресенье(d1,d2)
а в вашем календаре последнее воскресенье 18.01.2015
и 19 число не попадает в расчет так как следующая неделя не формируется((хотя есть еще дни до сегодня)

еще раз спасибо, я написал так cross apply(конструкция стала немного проще)
cross apply(select [date] from #calendar where [day] = t.d1 and [date] < t.[stop] ) db
cross apply(select top 1 [date] from #calendar where [day] = t.d2 and [date] >= db.[date] order by [date] asc) de

LexusR,исходя из описания cross apply применяется к каждой строке главного запроса, в данном контексте первый cross возвращает три значения 5,12,19 января, второй cross уже применяется к каждому из этих строк(возвращает соответственно 11,18,25)... третий проверяет на вхождение в заданный интервал и возвращает разницу в минутах.

P.S.:если я неправильно описал, то пож. поправте меня. еще раз спасибо
22 янв 15, 18:29    [17156145]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить