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

Откуда: Москва
Сообщений: 11
Уважаемые эксперты!

Почитал форум, но не смог найти ничего похожего.

Поэтому прошу помочь с запросом:

Есть таблица:

Сотрудники Назначения Дата назначения с Дата назначения до Дата приема Дата увольнения
EMPLOYE_ID ASSIGN_ID EMPLOYE_START_DATE EMPLOYE_FINISH_DATE PERSON_START_DATE PERSON_FINISH_DATE
1 2 01.01.2013 25.02.2013 01.01.2013 NULL
1 10 26.02.2013 10.06.2013 01.01.2013 NULL
1 48 11.06.2013 01.12.2013 01.01.2013 NULL
1 3 02.12.2013 NULL 01.01.2013 NULL
2 2 01.06.2013 01.09.2013 01.01.2013 NULL
2 25 02.09.2013 15.10.2013 01.01.2013 NULL
2 10 16.10.2013 NULL 01.01.2013 NULL
3 2 10.11.2013 10.12.2013 01.01.2013 NULL
3 3 11.12.2013 01.01.2014 01.01.2013 NULL
3 25 02.01.2014 01.02.2014 01.01.2013 NULL
3 38 02.02.2014 NULL 01.01.2013 NULL
4 2 01.02.2014 01.04.2014 01.02.2014 NULL
4 25 02.04.2014 01.05.2014 01.02.2014 01.05.2014


NULL - текущее назначение, т.е. незакрытое.

Нужно получить только строки где Назначения = 2,3,25

т.е. делаем WHERE ASSIGN_ID IN (2,3,25) - получаем все поля как надо,
кроме EMPLOYE_FINISH_DATE так как образуются разрывы в периодах - это поле должно заполняться как в таблице результат.


ОК ОК ОК не ОК ОК ОК
EMPLOYE_ID ASSIGN_ID EMPLOYE_START_DATE EMPLOYE_FINISH_DATE PERSON_START_DATE PERSON_FINISH_DATE
1 2 01.01.2013 25.02.2013 01.01.2013 NULL
1 3 02.12.2013 NULL 01.01.2013 NULL
2 2 01.06.2013 01.09.2013 01.01.2013 NULL
2 25 02.09.2013 15.10.2013 01.01.2013 NULL
3 2 10.11.2013 10.12.2013 01.01.2013 NULL
3 3 11.12.2013 01.01.2014 01.01.2013 NULL
3 25 02.01.2014 01.02.2014 01.01.2013 NULL
4 2 01.02.2014 01.04.2014 01.02.2014 NULL
4 25 02.04.2014 01.05.2014 01.02.2014 01.05.2014


Нужно получить такой результат:

EMPLOYE_ID ASSIGN_ID EMPLOYE_START_DATE EMPLOYE_FINISH_DATE PERSON_START_DATE PERSON_FINISH_DATE
1 2 01.01.2013 01.12.2013 01.01.2013 NULL
1 3 02.12.2013 NULL 01.01.2013 NULL
2 2 01.06.2013 01.09.2013 01.01.2013 NULL
2 25 02.09.2013 NULL 01.01.2013 NULL
3 2 10.11.2013 10.12.2013 01.01.2013 NULL
3 3 11.12.2013 01.01.2014 01.01.2013 NULL
3 25 02.01.2014 NULL 01.01.2013 NULL
4 2 01.02.2014 01.04.2014 01.02.2014 NULL
4 25 02.04.2014 01.05.2014 01.02.2014 01.05.2014


Сервер: MS SQL 2008R2 (если на 2008R2 не получается, то можно решение для 2012).

Хочется красивое решение желательно без курсоров, циклов и cte.

Заранее спасибо!

Тестовые данные:

declare @t table
(
EMPLOYE_ID bigint,
ASSIGN_ID bigint,
EMPLOYE_START_DATE datetime,
EMPLOYE_FINISH_DATE datetime,
PERSON_START_DATE datetime,
PERSON_FINISH_DATE datetime
)

INSERT INTO @t
(EMPLOYE_ID
,ASSIGN_ID
,EMPLOYE_START_DATE
,EMPLOYE_FINISH_DATE
,PERSON_START_DATE
,PERSON_FINISH_DATE)
VALUES
(1,2,'2013-01-01','2013-02-25','2013-01-01',NULL),
(1,10,'2013-02-26','2013-06-10','2013-01-01',NULL),
(1,48,'2013-06-11','2013-12-01','2013-01-01',NULL),
(1,3,'2013-12-02',NULL,'2013-01-01',NULL),
(2,2,'2013-06-01','2013-09-01','2013-01-01',NULL),
(2,25,'2013-09-02','2013-10-15','2013-01-01',NULL),
(2,10,'2013-10-16',NULL,'2013-01-01',NULL),
(3,2,'2013-11-10','2013-12-10','2013-01-01',NULL),
(3,3,'2013-12-11','2014-01-01','2013-01-01',NULL),
(3,25,'2014-01-02','2014-02-01','2013-01-01',NULL),
(3,38,'2014-02-02',NULL,'2013-01-01',NULL),
(4,2,'2014-02-01','2014-04-01','2014-02-01',NULL),
(4,25,'2014-04-02','2014-05-01','2014-02-01','2014-05-01')	
29 апр 14, 23:37    [15955466]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
negof
запрос на заполнение разрывов в периодах (диапазоны дат)

кроме EMPLOYE_FINISH_DATE так как образуются разрывы в периодах - это поле должно заполняться как в таблице результат.
Моя твоя не панимать.

На основе данных угадываю задание:
Нужно сгруппировать список периодов назначений разделённые (начинающиеся с) типами 2,3,25, по каждому сотруднику.
Это многократно обсуждалось на форуме.
Есть несколько вариантов ответов.

PERSON_START_DATE, PERSON_FINISH_DATE - уже не вписывается в нормальную форму. Поэтому выкидываем из запроса. За-JOIN-ить всегда успеется.
30 апр 14, 01:59    [15955679]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
negof
Member

Откуда: Москва
Сообщений: 11
Mnior
negof
запрос на заполнение разрывов в периодах (диапазоны дат)

кроме EMPLOYE_FINISH_DATE так как образуются разрывы в периодах - это поле должно заполняться как в таблице результат.
Моя твоя не панимать.

На основе данных угадываю задание:
Нужно сгруппировать список периодов назначений разделённые (начинающиеся с) типами 2,3,25, по каждому сотруднику.
Это многократно обсуждалось на форуме.
Есть несколько вариантов ответов.

PERSON_START_DATE, PERSON_FINISH_DATE - уже не вписывается в нормальную форму. Поэтому выкидываем из запроса. За-JOIN-ить всегда успеется.



Mnior,

Спасибо за ответ, но это чуть – чуть не то.

Попробую сформулировать задачу иначе:

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

Например:

2 - Принять на работу
3 - Перевести на должность
10 - Назначить оклад
25 - Перевести в другое структурное подразделение
38 - Об изменении оклада
48 - Изменить режим работы
и т.д.

Нужно получить полные периоды по назначениям 2,3,25, т.е. результат запроса должен быть как в таблице «Нужно получить такой результат:».

Спасибо!
30 апр 14, 08:47    [15955971]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
aleks2
Guest
Гуманитарий, чтоле?

>>Нужно получить такой результат:
первая строка
Откуда там 01.12.2013

ЗЫ. Ну я то могу пованговать, но мне лениво.
30 апр 14, 08:55    [15955995]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
Добрый Э - Эх
Guest
negof,

ну так-то выходит, что назначение действует до тех пор, пока не случится другое назначение? тогда с датами окончания не совсем понятно. почему для 1,2,3 они пустые, а для 4 - нет? Ведь для 4 следующего за 25 назначения не было. стало быть оно действует?
Или всё логика несколько более хитрая?
30 апр 14, 09:01    [15956019]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
negof
Member

Откуда: Москва
Сообщений: 11
aleks2
Гуманитарий, чтоле?

>>Нужно получить такой результат:
первая строка
Откуда там 01.12.2013

ЗЫ. Ну я то могу пованговать, но мне лениво.



Так как после фильтра следующее назначение у этого сотрудника начинается с 02.12.2013
30 апр 14, 09:39    [15956228]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
negof
Member

Откуда: Москва
Сообщений: 11
Добрый Э - Эх
negof,

ну так-то выходит, что назначение действует до тех пор, пока не случится другое назначение? тогда с датами окончания не совсем понятно. почему для 1,2,3 они пустые, а для 4 - нет? Ведь для 4 следующего за 25 назначения не было. стало быть оно действует?
Или всё логика несколько более хитрая?



У сотрудников 1,2,4 в исходных данных (до применения фильтра 2,3,25) есть незакрытое назначение т.е. они еще работают, а у 4 все закрыто т.е. он уволился.
30 апр 14, 09:43    [15956262]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
declare @t table
(
EMPLOYE_ID bigint,
ASSIGN_ID bigint,
EMPLOYE_START_DATE datetime,
EMPLOYE_FINISH_DATE datetime,
PERSON_START_DATE datetime,
PERSON_FINISH_DATE datetime
)

INSERT INTO @t
(EMPLOYE_ID
,ASSIGN_ID
,EMPLOYE_START_DATE
,EMPLOYE_FINISH_DATE
,PERSON_START_DATE
,PERSON_FINISH_DATE)
VALUES
(1,2,'2013-01-01','2013-02-25','2013-01-01',NULL),
(1,10,'2013-02-26','2013-06-10','2013-01-01',NULL),
(1,48,'2013-06-11','2013-12-01','2013-01-01',NULL),
(1,3,'2013-12-02',NULL,'2013-01-01',NULL),
(2,2,'2013-06-01','2013-09-01','2013-01-01',NULL),
(2,25,'2013-09-02','2013-10-15','2013-01-01',NULL),
(2,10,'2013-10-16',NULL,'2013-01-01',NULL),
(3,2,'2013-11-10','2013-12-10','2013-01-01',NULL),
(3,3,'2013-12-11','2014-01-01','2013-01-01',NULL),
(3,25,'2014-01-02','2014-02-01','2013-01-01',NULL),
(3,38,'2014-02-02',NULL,'2013-01-01',NULL),
(4,2,'2014-02-01','2014-04-01','2014-02-01',NULL),
(4,25,'2014-04-02','2014-05-01','2014-02-01','2014-05-01')	


SELECT t1.EMPLOYE_ID,t1.ASSIGN_ID,t1.EMPLOYE_START_DATE 
,ISNULL(t1.PERSON_FINISH_DATE, t3.EMPLOYE_START_DATE-1) as EMPLOYE_FINISH_DATE
FROM @t  t1
OUTER APPLY( SELECT TOP 1 * FROM @t  t2  WHERE t2.EMPLOYE_ID = t1.EMPLOYE_ID 
AND t2.ASSIGN_ID IN (2,3,25)
AND t2.EMPLOYE_START_DATE > t1.EMPLOYE_START_DATE
ORDER BY t2.EMPLOYE_START_DATE) t3
WHERE t1.ASSIGN_ID IN (2,3,25)
ORDER BY t1.EMPLOYE_ID,t1.EMPLOYE_START_DATE
30 апр 14, 09:43    [15956264]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
aleks2
Guest
negof
Так как после фильтра следующее назначение у этого сотрудника начинается с 02.12.2013


1. Фильтруешь Назначения = 2,3,25
2. Нумеруешь в порядке возрастания даты rownumber() over(partition by EMPLOYE_ID order by 01.01.2013).
3. Берешь дату закрытия периода со следующего номера.
30 апр 14, 09:45    [15956278]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
negof
Member

Откуда: Москва
Сообщений: 11
LexusR,

Спасибо интересный подход!

Это работает для исходных данных.

Но если, например, уволить 3 – го сотрудника, то это не сработает т.к. дата увольнения находится в строке с назначением 38

Новые исходные данные:

declare @t table
(
EMPLOYE_ID bigint,
ASSIGN_ID bigint,
EMPLOYE_START_DATE datetime,
EMPLOYE_FINISH_DATE datetime,
PERSON_START_DATE datetime,
PERSON_FINISH_DATE datetime
)

INSERT INTO @t
(EMPLOYE_ID
,ASSIGN_ID
,EMPLOYE_START_DATE
,EMPLOYE_FINISH_DATE
,PERSON_START_DATE
,PERSON_FINISH_DATE)
VALUES
(1,2,'2013-01-01','2013-02-25','2013-01-01',NULL),
(1,10,'2013-02-26','2013-06-10','2013-01-01',NULL),
(1,48,'2013-06-11','2013-12-01','2013-01-01',NULL),
(1,3,'2013-12-02',NULL,'2013-01-01',NULL),
(2,2,'2013-06-01','2013-09-01','2013-01-01',NULL),
(2,25,'2013-09-02','2013-10-15','2013-01-01',NULL),
(2,10,'2013-10-16',NULL,'2013-01-01',NULL),
(3,2,'2013-11-10','2013-12-10','2013-01-01',NULL),
(3,3,'2013-12-11','2014-01-01','2013-01-01',NULL),
(3,25,'2014-01-02','2014-02-01','2013-01-01',NULL),
(3,38,'2014-02-02','2014-05-01','2013-01-01','2014-05-01'), --Уволен с 2014-05-01
(4,2,'2014-02-01','2014-04-01','2014-02-01',NULL),
(4,25,'2014-04-02','2014-05-01','2014-02-01','2014-05-01')



Спасибо!
30 апр 14, 10:15    [15956461]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
negof
Уважаемые эксперты!

Нужно получить только строки где Назначения = 2,3,25

[i]т.е. делаем WHERE ASSIGN_ID IN (2,3,25) - получаем все поля как надо,


для назначений 38 доработайте скрипт сами по аналогии
30 апр 14, 11:48    [15957185]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
negof
Member

Откуда: Москва
Сообщений: 11
LexusR,

Доработал предложенный Вами скрипт (см. по тексту доработанный скрипт)

Протестировал на разных данных, работает вроде как надо!

Одно смущает, что он получился «шестиэтажным»?!

Люди кто знает, может его как-то упростить?

Заранее спасибо!

Доработанный скрипт:

--Новые тестовые данные
declare @t table
(
EMPLOYE_ID bigint,
ASSIGN_ID bigint,
EMPLOYE_START_DATE datetime,
EMPLOYE_FINISH_DATE datetime,
PERSON_START_DATE datetime,
PERSON_FINISH_DATE datetime
)

INSERT INTO @t
(EMPLOYE_ID
,ASSIGN_ID
,EMPLOYE_START_DATE
,EMPLOYE_FINISH_DATE
,PERSON_START_DATE
,PERSON_FINISH_DATE)
VALUES
(1,2,'2013-01-01','2013-02-25','2013-01-01',NULL),
(1,10,'2013-02-26','2013-06-10','2013-01-01',NULL),
(1,48,'2013-06-11','2013-12-01','2013-01-01',NULL),
(1,3,'2013-12-02','2013-12-05','2013-01-01','2013-12-05'),
(2,2,'2013-06-01','2013-09-01','2013-06-01',NULL),
(2,25,'2013-09-02','2013-10-15','2013-06-01',NULL),
(2,20,'2013-10-16','2013-10-19','2013-06-01',NULL),
(2,10,'2013-10-20','2013-11-30','2013-06-01',NULL),
(2,3,'2013-12-01',NULL,'2013-06-01',NULL),
(3,2,'2013-11-10','2013-12-10','2013-11-10',NULL),
(3,3,'2013-12-11','2014-01-01','2013-11-10',NULL),
(3,25,'2014-01-02','2014-02-01','2013-11-10',NULL),
(3,38,'2014-02-02','2014-05-01','2013-11-10','2014-05-01'),
(4,2,'2014-02-01','2014-04-01','2014-02-01',NULL),
(4,25,'2014-04-02','2014-05-01','2014-02-01',NULL),
(4,3,'2014-05-02',NULL,'2014-02-01',NULL)


--Запрос
SELECT 
	  t6.EMPLOYE_ID
	 ,t6.ASSIGN_ID
	 ,t6.EMPLOYE_START_DATE 
	 ,ISNULL(t6.EMPLOYE_FINISH_DATE, t6.PERSON_FINISH_DATE) AS EMPLOYE_FINISH_DATE
	 ,t6.PERSON_START_DATE
	 ,t6.PERSON_FINISH_DATE
FROM
(SELECT 
	  t4.EMPLOYE_ID
	 ,t4.ASSIGN_ID
	 ,t4.EMPLOYE_START_DATE 
	 ,t4.EMPLOYE_FINISH_DATE
	 ,t4.PERSON_START_DATE
	 ,t5.PERSON_FINISH_DATE 
FROM
(SELECT 
	  t1.EMPLOYE_ID
	 ,t1.ASSIGN_ID
	 ,t1.EMPLOYE_START_DATE 
	 ,ISNULL(t1.PERSON_FINISH_DATE, t3.EMPLOYE_START_DATE - 1) AS EMPLOYE_FINISH_DATE
	 ,t1.PERSON_START_DATE
	 ,t1.PERSON_FINISH_DATE 
FROM @t t1
OUTER APPLY(
   SELECT TOP 1 * FROM @t t2
   WHERE t2.EMPLOYE_ID = t1.EMPLOYE_ID 
   AND t2.ASSIGN_ID IN (2,3,25)
   AND t2.EMPLOYE_START_DATE > t1.EMPLOYE_START_DATE
   ORDER BY t2.EMPLOYE_START_DATE) t3
   WHERE t1.ASSIGN_ID IN (2,3,25)
   --ORDER BY t1.EMPLOYE_ID, t1.EMPLOYE_START_DATE
   ) AS t4
INNER JOIN
 (SELECT EMPLOYE_ID, MAX(PERSON_FINISH_DATE) AS PERSON_FINISH_DATE FROM @t
  GROUP BY EMPLOYE_ID
  ) AS t5
  ON t4.EMPLOYE_ID = t5.EMPLOYE_ID) AS t6
30 апр 14, 12:37    [15957654]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
SELECT 
	  t1.EMPLOYE_ID
	 ,t1.ASSIGN_ID
	 ,t1.EMPLOYE_START_DATE 
	 ,COALESCE(t1.PERSON_FINISH_DATE, t3.EMPLOYE_START_DATE - 1, t4.PERSON_FINISH_DATE) AS EMPLOYE_FINISH_DATE
	 ,t1.PERSON_START_DATE
	 ,t4.PERSON_FINISH_DATE 
FROM @t t1
OUTER APPLY(
   SELECT TOP 1 * FROM @t t2
   WHERE t2.EMPLOYE_ID = t1.EMPLOYE_ID 
   AND t2.ASSIGN_ID IN (2,3,25)
   AND t2.EMPLOYE_START_DATE > t1.EMPLOYE_START_DATE
   ORDER BY t2.EMPLOYE_START_DATE) t3
OUTER APPLY(
	SELECT MAX(PERSON_FINISH_DATE) AS PERSON_FINISH_DATE FROM @t 
		WHERE EMPLOYE_ID = t1.EMPLOYE_ID) t4 
WHERE t1.ASSIGN_ID IN (2,3,25)
ORDER BY t1.EMPLOYE_ID, t1.EMPLOYE_START_DATE
30 апр 14, 13:06    [15957886]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
negof
Member

Откуда: Москва
Сообщений: 11
LexusR,

Спасибо огромное!

Все работает и красиво!!!

P.S.
LexusR, Вы походу гений в SQL !? :)
30 апр 14, 13:44    [15958343]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
aleks2
Guest
negof
LexusR,

Спасибо огромное!

Все работает и красиво!!!

P.S.
LexusR, Вы походу гений в SQL !? :)


В царстве слепых и кривой - король.
30 апр 14, 14:30    [15958725]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
negof
Member

Откуда: Москва
Сообщений: 11
aleks2
negof
Так как после фильтра следующее назначение у этого сотрудника начинается с 02.12.2013


1. Фильтруешь Назначения = 2,3,25
2. Нумеруешь в порядке возрастания даты rownumber() over(partition by EMPLOYE_ID order by 01.01.2013).
3. Берешь дату закрытия периода со следующего номера.


aleks2,

Покажите, пожалуйста, Ваше решение?

Заранее спасибо!
30 апр 14, 14:41    [15958798]     Ответить | Цитировать Сообщить модератору
 Re: Сложный запрос на заполнение разрывов в периодах (диапазоны дат)  [new]
aleks2
Guest
negof
aleks2,

Покажите, пожалуйста, Ваше решение?

Заранее спасибо!

Извиняй, дарагуля, ищи сам.
Мне уже надоело писать прописные истины.
30 апр 14, 15:09    [15959091]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить