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

Откуда:
Сообщений: 20
Есть таблица, с данными об отпусках и больничных (Таблица №1)
Нужно получить итоговую таблицу за год (как пример 2017) с вставкой периодов работы, т.е. дополнить промежуточные периоды (Таблица №2). Т.е. добавится период до первого отпуска (если отпуск не начался 01.01.2017) и между периодами отсутствия.

Картинка с другого сайта.
23 янв 18, 12:59    [21130213]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
Версия сервера? LEAD/LAG понимает?
23 янв 18, 13:01    [21130218]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Добрый Э - Эх
Guest
undll,

разверни интервалы в точки. затем из точек снова собери интервалы, но уже правильные, с недостающими периодами работоспособности...
23 янв 18, 13:06    [21130237]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
undll
Member

Откуда:
Сообщений: 20
Akina
Версия сервера? LEAD/LAG понимает?

2008 R2, как я понимаю там нету LEAD'а
23 янв 18, 13:29    [21130361]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Добрый Э - Эх
Guest
undll,

зато есть outer apply и CTE (но CTE тут нужно исключительно для декомпозиции и упрощения исходного запроса) ;)
23 янв 18, 13:31    [21130379]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
Ну тогда самое простое решение - озвучено. Только при разборке обязательно пометить точки - где начало, а где конец отрезка. Перед сборкой - добавить точки начала и конца целевого периода, а после сборки - удалить интервалы нулевой длины.
23 янв 18, 13:32    [21130386]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Добрый Э - Эх
Guest
Akina,

на самом деле всё еще проще - достаточно получить УНИКАЛЬНЫЙ набор точек, без дублей. Далее - собрать точки в новые интервалы. Для определения типа интервала (пил/курил/болел) - пересечь левым джойном с исходными диапазонами... Что не найдет себе пересечения - то периоды работоспособности
23 янв 18, 13:38    [21130421]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх,

единственное, что нужно будет ввести "мнимую" начальную точку, которая будет предшествовать всем рассматриваемым интервалам отсутствия человека на работе... Чтобы первый интервал работоспособности собрать правильно...
23 янв 18, 13:41    [21130437]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
set dateformat ymd
declare @t table (date_from smalldatetime,
                  date_to smalldatetime,
				  type varchar(100));
insert into @t values
('2017-03-05','2017-04-20','Отпуск'),
('2017-04-20','2017-04-23','Больничный'),
('2017-06-01','2017-06-14','Отпуск'),
('2017-09-01','2017-09-01','Отпуск');

declare @period table (date_from smalldatetime,
                       date_to smalldatetime);
insert into @period values
('2017-01-01','2017-12-31');


with begins as (
Select a.date_to+1 as date_from
  From (Select date_to From @t 
         union 
		Select date_from-1 From @period
          ) a
Where not exists (Select 1 From @t b Where b.date_to>a.date_to and a.date_to between b.date_from and b.date_to)),
ends as (
Select a.date_from-1  as date_to
  From (Select date_from From @t 
         union 
		Select date_to+1 From @period
          ) a
Where not exists (Select 1 From @t b Where b.date_from<a.date_from and a.date_from between b.date_from and b.date_to))
Select * From @t union all
Select *,(Select min(ends.date_to) From ends WHere ends.date_to>begins.date_from) as Date_To,'Работал' as Type From begins 
Order by 1
23 янв 18, 14:13    [21130644]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
undll
Member

Откуда:
Сообщений: 20
Kopelly, огромное спасибо!
23 янв 18, 14:19    [21130693]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
undll,

так правильнее (в предыдущем вылазил интервал работы c 02.09.2018 по NULL)
set dateformat ymd
declare @t table (date_from smalldatetime,
                  date_to smalldatetime,
				  type varchar(100));
insert into @t values
('2016-03-05','2017-04-20','Отпуск'),
('2017-04-20','2017-04-23','Больничный'),
('2017-06-01','2017-06-14','Отпуск'),
('2017-09-01','2018-09-01','Отпуск');

declare @Begin smalldatetime = '2017-01-01',
        @End   smalldatetime = '2017-12-31';

with begins as (
Select a.date_to+1 as date_from
  From (Select date_to From @t 
         union 
		Select @Begin-1
          ) a
Where not exists (Select 1 From @t b 
                   Where b.date_to>a.date_to 
				     and a.date_to between b.date_from and b.date_to)
  and a.date_to-1 <= @End),
ends as (
Select a.date_from-1  as date_to
  From (Select date_from From @t 
         union 
		Select @End+1
          ) a
Where not exists (Select 1 From @t b 
                   Where b.date_from<a.date_from 
				     and a.date_from between b.date_from and b.date_to))
Select * From @t union all
Select *,(Select min(ends.date_to) From ends WHere ends.date_to>begins.date_from) as Date_To,'Работал' as Type From begins 
Order by 1
23 янв 18, 14:24    [21130729]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Добрый Э - Эх
Guest
Kopelly,

Много лишнего. Можно проще.
23 янв 18, 14:26    [21130738]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
undll
Member

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

там в 1 отпуске опечатка, период 05.03.2017 - 19.03.2017
23 янв 18, 14:27    [21130746]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
Добрый Э - Эх
Kopelly,

Много лишнего. Можно проще.

С удовольствием посмотрел бы на более красивое и оптимальное решение (без сарказма).
23 янв 18, 14:30    [21130765]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 289
undll
Kopelly,

там в 1 отпуске опечатка, период 05.03.2017 - 19.03.2017

Я специально изменил периоды, чтобы были пересекающиеся и выходящие за рамки года. Для твоих данных смотри заполнение таблицы @t из первого скрипта.
23 янв 18, 14:32    [21130776]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Добрый Э - Эх
Guest
Kopelly
так правильнее


Надо как-то учиться выделять код в тэге src, 
чтобы показывать изменения. 
А то ведь оно непросто искать десять отличий ;)

Попробуй управляющие конструкции:
==> (для подсветки строки жёлтым)
>>> <<< (для подсветки нескольких символов в строке)
23 янв 18, 14:55    [21130951]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Добрый Э - Эх
Guest
Kopelly
(без сарказма).
теперь уже завтра . Сегодня рабочий день закончился....
23 янв 18, 14:58    [21130973]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
Как уже сказали: "разверни интервалы в точки. затем из точек снова собери интервалы, но уже правильные, с недостающими периодами работоспособности... "

declare @p1 date = '20170101', @p2 date = '20171231';

with A as (--Таблица дат
	 select number as i, cast(dateadd(dd,number,@p1) as date) as dt from master.dbo.spt_values where type='P'
)
--select * from A;
,    B as (--Таблица № 1
     select *
       from (values ('20170305','20170319','Отпуск'),
                    ('20170420','20170423','Больничный'),
                    ('20170601','20170614','Отпуск'),
                    ('20170901','20170901','Отпуск')
            ) as T(          d1,        d2,   tp)
)
,    C as (--Разворачиваем интервалы в "точки"
     select i,dt, isnull(tp,'Работал') as tp 
	   from      A b
	   left join B c on dt between d1 and d2
	   where dt between @p1 and @p2
)
--select * from C;
,    D as (--Находим интервалы непрерывности tp
     select i,dt,tp,i-rank() over (partition by tp order by tp,i) as rnk
	   from      C b
)
--select * from D
select min(dt) as date_from, max(dt) as date_to, tp --Формируем периоды
  from D
  group by rnk,tp
  order by date_from;


Результат:
date_from	date_to	tp
2017-01-01 2017-03-04 Работал
2017-03-05 2017-03-19 Отпуск
2017-03-20 2017-04-19 Работал
2017-04-20 2017-04-23 Больничный
2017-04-24 2017-05-31 Работал
2017-06-01 2017-06-14 Отпуск
2017-06-15 2017-08-31 Работал
2017-09-01 2017-09-01 Отпуск
2017-09-02 2017-12-31 Работал
23 янв 18, 15:47    [21131311]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
LEAD и LAG для слабаков!
DECLARE @off TABLE ( [date_from] DATE, [date_to] DATE, [type] VARCHAR(50) )
INSERT INTO
  @off
VALUES 
  ( '20170305', '20170319', 'Сказочный Бали!' ),
  ( '20170420', '20170423', 'Что ж я маленьким не сдох?!' ),
  ( '20170601', '20170614', 'Тагиииил!' ),
  ( '20170901', '20170901', 'Дважды два - четыре!' )
;
WITH
t0 AS (
  SELECT
    [rn] = ROW_NUMBER() OVER ( ORDER BY [date_from] ),
    *
  FROM
    @off
),
t1 AS (
SELECT
  [date_from],
  [date_to],
  [type]
FROM
  t0
UNION ALL
SELECT
  [date_from] = CONVERT( DATE, '20170101' ),
  [date_to] = DATEADD( DAY, -1, tc.[date_from] ),
  [type] = 'Йа маленькая лааашадка'
FROM
  t0 tc
WHERE
  tc.[rn] = 1
UNION ALL
SELECT
  [date_from] = DATEADD( DAY, 1, tc.[date_to] ),
  [date_to] = DATEADD( DAY, -1, ISNULL( tn.[date_from], '20180101' ) ),
  [type] = 'Йа маленькая лааашадка'
FROM
  t0 tc
  LEFT JOIN t0 tn ON (
        tn.[rn] = tc.[rn] + 1 )
)
SELECT
  *
FROM
  t1
ORDER BY
  [date_from]
;
23 янв 18, 15:58    [21131373]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Добрый Э - Эх
Guest
Wlr-l,

Под «разверни» таки подразумевалось именно что - «разверни», а не «сгенерируй все даты в заданном диапазоне». То есть, любой отрезок из исходного набора данных даст две точки : «начало» и «кончало». Вот и нужно пересобрать в новые интервалы...
23 янв 18, 16:18    [21131467]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
Руслан Дамирович,

Наверно, ДА, для слабаков.

Например, если взять такую строку исходных данных
  ( '20170101', '20170319', 'Сказочный Бали!' ),

Получим
date_from	date_to	type
2017-01-01 2017-03-19 Сказочный Бали!
2017-01-01 2016-12-31 Йа маленькая лааашадка
2017-03-20 2017-04-19 Йа маленькая лааашадка
2017-04-20 2017-04-23 Что ж я маленьким не сдох?!

Т.е. имеем проблему на границе периода.
23 янв 18, 16:20    [21131480]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
Добрый Э - Эх,

Согласен. Неточность в терминологии. Будем считать, что я просто привел вариант решения.
23 янв 18, 16:24    [21131493]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Добрый Э - Эх
Guest
Wlr-l
Руслан Дамирович,

Наверно, ДА, для слабаков.

Например, если взять такую строку исходных данных
  ( '20170101', '20170319', 'СказочноЕ бали! :)' ),

Получим
date_from	date_to	type
2017-01-01 2017-03-19 Сказочный Бали!
2017-01-01 2016-12-31 Йа маленькая лааашадка
2017-03-20 2017-04-19 Йа маленькая лааашадка
2017-04-20 2017-04-23 Что ж я маленьким не сдох?!

Т.е. имеем проблему на границе периода.

"если вы понимаете, о чем я..."(с)
23 янв 18, 16:39    [21131547]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
Добрый Э - Эх, у меня тоже заканчивается рабочий день. Приведу еще один пример, разворота интервалов в точки:

declare @p1 date = '20170101', @p2 date = '20171231';

with B as (--Таблица № 1
     select *
       from (values ('20170305','20170319','Отпуск'),
                    ('20170420','20170423','Больничный'),
                    ('20170601','20170614','Отпуск'),
                    ('20170901','20170901','Отпуск')
            ) as T(          d1,        d2,   tp)
)
,    C as (--Разворачиваем интервалы в "точки"
	 select d1 as dt, 0 as b from B -- 0 = «начало»  
	 union
	 select d2,1      from B        -- 1 = «кончало»
	 union
	 select @p1,0
	 union
	 select @p2,1
)
--select * from C;
,    D as (--просто пронумеруем "точки"
	 select cast(dt as date) as dt, b, row_number() over (order by dt) rn
	   from C
)
--select * from D;
--Сформируем новые интервалы
select case when dl.b=0  then dl.dt else dateadd(dd, 1, dl.dt) end as date_from,
       case when dr.b<>0 then dr.dt else dateadd(dd,-1, dr.dt) end as date_to
  from D dl
  join D dr on dl.rn=dr.rn-1;

Результат:
2017-01-01 2017-03-04
2017-03-05 2017-03-19
2017-03-20 2017-04-19
2017-04-20 2017-04-23
2017-04-24 2017-05-31
2017-06-01 2017-06-14
2017-06-15 2017-08-31
2017-09-01 2017-09-01
2017-09-02 2017-12-31

Описание периода ТС добавит.
23 янв 18, 17:31    [21131801]     Ответить | Цитировать Сообщить модератору
 Re: Добавить недостающие периоды  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Wlr-l
Руслан Дамирович,
Например, если взять такую строку исходных данных
  ( '20170101', '20170319', 'Сказочный Бали!' ),

Получим
date_from	date_to	type
2017-01-01 2017-03-19 Сказочный Бали!
2017-01-01 2016-12-31 Йа маленькая лааашадка
2017-03-20 2017-04-19 Йа маленькая лааашадка
2017-04-20 2017-04-23 Что ж я маленьким не сдох?!
Т.е. имеем проблему на границе периода.

Проблемы нет. Достаточно добавить пару условий.
Ваше решение универсальное, мое - в лоб.
Я бы тоже решил его в виде HCTE, но зачем? :)

автор
СказочноЕ бали! :)

И все же, остров, а значит м.р. - сказочный. Но мы все понимаем, что же там происходило ;)
23 янв 18, 17:39    [21131830]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить