Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
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] Ответить | Цитировать Сообщить модератору |
3unknown Member Откуда: New York Сообщений: 140 |
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] Ответить | Цитировать Сообщить модератору |
PisarevskiyRE Member Откуда: Сообщений: 12 |
3unknown, ЕКАРНЫЙ БАБАЙ!!! СПАСИБО ТЕБЕ БОЛЬШОЕ, ЧЕЛОВЕЧИЩЕ!!! ![]() |
17 июл 16, 23:18 [19421325] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
Guest |
PisarevskiyRE, для получения доступных интервалов совершенно ненужно генерировать весь интервал. достаточно преобразовать доступные и занятые интервалы в точки смены состояний и после из этих точек собрать новые интервалы. |
18 июл 16, 06:07 [19421488] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
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] Ответить | Цитировать Сообщить модератору |
Добрый Э - Эх
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] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
3unknown Member Откуда: New York Сообщений: 140 |
Попробуйте такой номер: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] Ответить | Цитировать Сообщить модератору |
andrey odegov Member Откуда: Сообщений: 473 |
а если так?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 | ![]() |