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

Откуда:
Сообщений: 12
2 день туплю, пытаюсь придумать как элегантно создать интервалы...
Условия такие - есть значит рабочее время (начало и конец) и есть промежутки в которые необходимо "вычесть из этих интервалов".

1 и 2 столбец это "занятые промежутки", 3 и 4 это время работы

Для примера

2016-05-24 08:15:00.000 | 2016-05-24 08:45:00.000 | 2016-05-24 08:00:00.000 | 2016-05-24 16:00:00.000
2016-05-26 09:00:00.000 | 2016-05-26 09:30:00.000 | 2016-05-26 08:00:00.000 | 2016-05-26 16:00:00.000
2016-05-26 08:30:00.000 | 2016-05-26 09:00:00.000 | 2016-05-26 08:00:00.000 | 2016-05-26 16:00:00.000
2016-06-09 09:45:00.000 | 2016-06-09 10:15:00.000 | 2016-06-09 08:00:00.000 | 2016-06-09 16:00:00.000


В итоге надо получить доступные интервалы что то типа -

с 8,00 до 8,15 - 24
с 8,45 до 16,00 - 24
с 8,00 до 8,30 - 26
с 9,30 до 16,00 - 26
с 8,00 до 9,45 - 09
с 10,15 до 16,00 - 09
17 июл 16, 21:42    [19421171]     Ответить | Цитировать Сообщить модератору
 Re: Генерация интервало дат  [new]
3unknown
Member

Откуда: New York
Сообщений: 141
declare @tbl table(date1 datetime,date2 datetime,date3 datetime,date4 datetime)

declare @date table(date datetime)

insert @tbl
values('2016-05-24 08:15:00.000','2016-05-24 08:45:00.000','2016-05-24 08:00:00.000','2016-05-24 16:00:00.000'),
('2016-05-26 09:00:00.000', '2016-05-26 09:30:00.000', '2016-05-26 08:00:00.000', '2016-05-26 16:00:00.000'),
('2016-05-26 08:30:00.000', '2016-05-26 09:00:00.000', '2016-05-26 08:00:00.000', '2016-05-26 16:00:00.000'),
('2016-06-09 09:45:00.000', '2016-06-09 10:15:00.000', '2016-06-09 08:00:00.000', '2016-06-09 16:00:00.000')


insert @date
select date
from(
select date1 as date
from @tbl
union
select date2 as date
from @tbl
union
select date3 as date
from @tbl
union
select date4 as date
from @tbl
) a
order by date



select distinct first_value(start) over(partition by datepart(dd,start) order by num) as start_time
,first_value(finish) over(partition by datepart(dd,start) order by num) as end_time
from(
select d.date start,min(d1.date) finish
,row_number() over(partition by datepart(dd,d.date) order by d.date) as num
from @date d
 join @date d1 on d.date < d1.date
 group by d.date
 having(datediff(dd,min(d1.date),d.date)) = 0
 ) b
 union
 select distinct first_value(start) over(partition by datepart(dd,start) order by num desc) as start_time
,first_value(finish) over(partition by datepart(dd,start) order by num desc) as end_time
from(
select d.date start,min(d1.date) finish
,row_number() over(partition by datepart(dd,d.date) order by d.date) as num
from @date d
 join @date d1 on d.date < d1.date
 group by d.date
 having(datediff(dd,min(d1.date),d.date)) = 0
 ) b
order by start_time
17 июл 16, 23:15    [19421321]     Ответить | Цитировать Сообщить модератору
 Re: Генерация интервало дат  [new]
PisarevskiyRE
Member

Откуда:
Сообщений: 12
3unknown, ЕКАРНЫЙ БАБАЙ!!!
СПАСИБО ТЕБЕ БОЛЬШОЕ, ЧЕЛОВЕЧИЩЕ!!!
17 июл 16, 23:18    [19421325]     Ответить | Цитировать Сообщить модератору
 Re: Генерация интервало дат  [new]
Добрый Э - Эх
Guest
PisarevskiyRE,

для получения доступных интервалов совершенно ненужно генерировать весь интервал.
достаточно преобразовать доступные и занятые интервалы в точки смены состояний и после из этих точек собрать новые интервалы.
18 июл 16, 06:07    [19421488]     Ответить | Цитировать Сообщить модератору
 Re: Генерация интервало дат  [new]
Добрый Э - Эх
Guest
3unknown,

завернул, конечно, знатно...
тут же всё гораздо банальнее:
--
-- Тестовые данные:
with
  t (date1, date2, date3, date4) as
    (
      select CAST(d1 as datetime)
           , CAST(d2 as datetime)
           , CAST(d3 as datetime)
           , CAST(d4 as datetime)
        from (
               values
                 ('2016-05-24 08:15:00.000', '2016-05-24 08:45:00.000', '2016-05-24 08:00:00.000', '2016-05-24 16:00:00.000'),
                 ('2016-05-26 09:00:00.000', '2016-05-26 09:30:00.000', '2016-05-26 08:00:00.000', '2016-05-26 16:00:00.000'),
                 ('2016-05-26 08:30:00.000', '2016-05-26 09:00:00.000', '2016-05-26 08:00:00.000', '2016-05-26 16:00:00.000'),
                 ('2016-06-09 09:45:00.000', '2016-06-09 10:15:00.000', '2016-06-09 08:00:00.000', '2016-06-09 16:00:00.000')
             ) v(d1,d2,d3,d4)
    )
--
-- Основной запрос:
select dt1, dt2, x_day
  from (
         select CAST(floor(CAST(dt AS FLOAT)) AS DATETIME) as x_day
              , dt as dt1
              , lead(dt)over(partition by floor(CAST(dt AS FLOAT))order by dt) as dt2
              , flag as flag1
              , lead(flag)over(partition by floor(CAST(dt AS FLOAT))order by dt) as flag2
           from (
                  select date1 as dt, 0 as flag from t union
                  select date2 as dt, 0 as flag from t union
                  select date3 as dt, 1 as flag from t union
                  select date4 as dt, 1 as flag from t
                ) v0
       ) v1
 where flag1 + flag2 > 0


З.Ы.
Сервера под рукой не было, поэтому работоспособность запроса не проверял...
18 июл 16, 08:34    [19421569]     Ответить | Цитировать Сообщить модератору
 Re: Генерация интервало дат  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх,

если lead/lag недоступны, то можно по старинке, на row_number-е:

--
-- Тестовые данные:
create table tbl# (date1 datetime,date2 datetime,date3 datetime,date4 datetime);
insert tbl#
values('2016-05-24 08:15:00.000','2016-05-24 08:45:00.000','2016-05-24 08:00:00.000','2016-05-24 16:00:00.000'),
('2016-05-26 09:00:00.000', '2016-05-26 09:30:00.000', '2016-05-26 08:00:00.000', '2016-05-26 16:00:00.000'),
('2016-05-26 08:30:00.000', '2016-05-26 09:00:00.000', '2016-05-26 08:00:00.000', '2016-05-26 16:00:00.000'),
('2016-06-09 09:45:00.000', '2016-06-09 10:15:00.000', '2016-06-09 08:00:00.000', '2016-06-09 16:00:00.000');

--
-- Основной запрос:
go
with
   x_t as
    (
      select ROW_NUMBER() over(partition by floor(CAST(dt AS FLOAT)) order by dt)  as rn
           , CAST(floor(CAST(dt AS FLOAT)) AS DATETIME) as x_day
           , dt
           , flag as flag1
        from (
               select date1 as dt, 0 as flag from tbl# union
               select date2 as dt, 0 as flag from tbl# union
               select date3 as dt, 1 as flag from tbl# union
               select date4 as dt, 1 as flag from tbl#
             ) v0
    )
--
-- Основной запрос:
select t0.dt as dt1, t1.dt as dt2, t0.x_day
  from  x_t t0
  join  x_t t1
    on t0.x_day = t1.x_day
   and t0.rn = t1.rn - 1
   and t0.flag1 + t1.flag1 > 0
 order by x_day, dt1
18 июл 16, 09:02    [19421625]     Ответить | Цитировать Сообщить модератору
 Re: Генерация интервало дат  [new]
PisarevskiyRE
Member

Откуда:
Сообщений: 12
Оба метода ломаются при

('2016-05-24 08:00:00.000', '2016-05-24 08:45:00.000', '2016-05-24 08:00:00.000', '2016-05-24 16:00:00.000'),
('2016-05-26 09:00:00.000', '2016-05-26 09:30:00.000', '2016-05-26 08:00:00.000', '2016-05-26 16:00:00.000'),
('2016-05-26 08:30:00.000', '2016-05-26 09:00:00.000', '2016-05-26 08:00:00.000', '2016-05-26 16:00:00.000'),
('2016-06-09 09:45:00.000', '2016-06-09 10:15:00.000', '2016-06-09 08:00:00.000', '2016-06-09 16:00:00.000')

когда начало интервала равно началу промежутку :3
26 июл 16, 05:27    [19454591]     Ответить | Цитировать Сообщить модератору
 Re: Генерация интервало дат  [new]
3unknown
Member

Откуда: New York
Сообщений: 141
Попробуйте такой номер:

declare @tbl table(date1 datetime,date2 datetime,date3 datetime,date4 datetime)

declare @date table(date datetime,rang int)

declare @date_fin table(date datetime,rang int)

insert @tbl
values ('2016-05-24 08:00:00.000', '2016-05-24 08:45:00.000', '2016-05-24 08:00:00.000', '2016-05-24 16:00:00.000'),
('2016-05-26 09:00:00.000', '2016-05-26 09:30:00.000', '2016-05-26 08:00:00.000', '2016-05-26 16:00:00.000'),
('2016-05-26 08:30:00.000', '2016-05-26 09:00:00.000', '2016-05-26 08:00:00.000', '2016-05-26 16:00:00.000'),
('2016-06-09 09:45:00.000', '2016-06-09 10:15:00.000', '2016-06-09 08:00:00.000', '2016-06-09 16:00:00.000')


insert @date
select date,rang
from(
select date1 as date,1 rang
from @tbl
union
select date2 as date,2 rang
from @tbl
union
select date3 as date,3 rang
from @tbl
union
select date4 as date,4 rang
from @tbl
) a
order by date

insert @date_fin
select date,rang
from(
select datepart(dd,date) as days,rang,case rang when 1 then min(date) when 2 then max(date) when 3 then min(date) when 4 then max(date) end date
from @date 
group by datepart(dd,date),rang
) a
order by date

select d.date as start,min(d1.date) as finish
from @date_fin d
join @date_fin d1 on d.date <= d1.date and abs(d.rang - d1.rang) = 2
group by d.date
having abs(datediff(dd,min(d1.date), d.date))<=1
order by d.date
26 июл 16, 11:00    [19455504]     Ответить | Цитировать Сообщить модератору
 Re: Генерация интервало дат  [new]
andrey odegov
Member

Откуда:
Сообщений: 474
а если так?
declare @ts table(to1 datetime,to2 datetime,lhb datetime,lhf datetime);
insert @ts
values('20160524 08:15','20160524 08:45','20160524 08:00','20160524 16:00'),
      ('20160526 09:00','20160526 09:30','20160526 08:00','20160526 16:00'),
      ('20160526 08:30','20160526 09:00','20160526 08:00','20160526 16:00'),
      ('20160609 09:45','20160609 10:15','20160609 08:00','20160609 16:00');
with t1 as(
  select min(to1)to1,max(to2)to2,lhb,lhf
  from @ts
  group by lhb,lhf
)
select t2.*
from t1
outer apply(
  values(lhb,to1,datepart(dd,to1)),(to2,lhf,datepart(dd,to1))
)t2(tob,tof,dd);
26 июл 16, 17:36    [19458376]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить