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

Откуда:
Сообщений: 107
подскажите, пожалуйста, как решить такую задачу:
необходимо посчитать общую сумму часов в наложениях рабочего графика.
наложения - это когда в одно и тоже время работает более одного сотрудника.


таблица имеет вид:
EmployeeID
DateStart
DateEnd

где DateStart - дата и время начала работы в графике,
DateEnd - дата и время окончания работы в графике
28 авг 19, 11:39    [21958923]     Ответить | Цитировать Сообщить модератору
 Re: посчитать кол-во часов в наложениях рабочего графика  [new]
aleks222
Member

Откуда:
Сообщений: 926
set nocount on;

declare @t table( EmployeeID int, DateStart datetime, DateEnd datetime);

insert @t
  select 1, '20190101 10:07', '20190101 20:07'
  union all
  select 1, '20190102 10:07', '20190102 20:07'
  union all
  select 2, '20190102 15:07', '20190102 20:07'
  union all
  select 3, '20190102 15:07', '20190102 18:07'
  union all
  select 1, '20190103 10:07', '20190103 20:07'
  union all
  select 2, '20190103 14:07', '20190103 20:07'
  union all
  select 3, '20190103 15:07', '20190103 18:07'
  union all
  select 3, '20190103 18:10', '20190103 19:07'

-- начала/концы наложений
declare @b table( Date datetime primary key, n int identity unique );
insert @b( Date)
  select DateStart 
  from @t as t where exists( select * from @t as x where x.EmployeeID <> t.EmployeeID and t.DateStart between x.DateStart and x.DateEnd )
  union
  select DateEnd
    from @t as t where exists( select * from @t as x where x.EmployeeID <> t.EmployeeID and t.DateEnd between x.DateStart and x.DateEnd )
  order by DateStart
;
select * from @b;

-- интервалы перекрытий
with t as ( select d1 = b.Date, d2 = e.Date
                 , dT = datediff(minute, b.Date, e.Date) from @b as b inner join @b as e on e.n = b.n + 1 )
 select * from t
   where exists( select * from @t as x where dateadd( minute, datediff(minute, d1, d2), d1) between x.DateStart and x.DateEnd )
 ;

-- сумма интервалов перекрытий
with t as ( select d1 = b.Date, d2 = e.Date
                 , dT = datediff(minute, b.Date, e.Date) from @b as b inner join @b as e on e.n = b.n + 1 )
 select minutes = sum(dT) from t
   where exists( select * from @t as x where dateadd( minute, datediff(minute, d1, d2) / 2, d1) between x.DateStart and x.DateEnd )
 ;
28 авг 19, 13:23    [21959027]     Ответить | Цитировать Сообщить модератору
 Re: посчитать кол-во часов в наложениях рабочего графика  [new]
ant_sol
Member

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

спасибо !!
29 авг 19, 09:17    [21959538]     Ответить | Цитировать Сообщить модератору
 Re: посчитать кол-во часов в наложениях рабочего графика  [new]
aleks222
Member

Откуда:
Сообщений: 926
ant_sol
aleks222,

спасибо !!

Ну... там, ваще то, дефект есть.
29 авг 19, 09:22    [21959542]     Ответить | Цитировать Сообщить модератору
 Re: посчитать кол-во часов в наложениях рабочего графика  [new]
Focha
Member

Откуда: Москва
Сообщений: 499
ant_sol
aleks222,

спасибо !!

и как устроился на работу?
9 апр 20, 11:37    [22113374]     Ответить | Цитировать Сообщить модератору
 Re: посчитать кол-во часов в наложениях рабочего графика  [new]
nullin
Member

Откуда: pullin
Сообщений: 172
Focha, временные таблицы там нафиг не нужны!
только сделай
cross apply(values (1, DateStart), (-1, DateEnd)
так разу сам догадаешься! Картинка с другого сайта.
9 апр 20, 14:34    [22113536]     Ответить | Цитировать Сообщить модератору
 Re: посчитать кол-во часов в наложениях рабочего графика  [new]
aleks222
Member

Откуда:
Сообщений: 926
nullin
Focha, временные таблицы там нафиг не нужны!
только сделай
cross apply(values (1, DateStart), (-1, DateEnd)
так разу сам догадаешься! Картинка с другого сайта.


Наивняк.
9 апр 20, 19:21    [22113788]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить