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

Откуда:
Сообщений: 2031
Здравствуйте!

Есть такая задачка.

Есть первая таблица - Таблица расчетов.
Номер расчета Код ТТ SKU Дата расчета Кол-во недель
Номер расчета1 00001 SKU1 2018-02-08 1
Номер расчета1 00001 SKU2 2018-02-08 1
Номер расчета1 00002 SKU1 2018-02-08 1
Номер расчета1 00002 SKU2 2018-02-08 1
Номер расчета2 00001 SKU1 2018-02-08 1
Номер расчета2 00003 SKU1 2018-02-08 1
Номер расчета2 00003 SKU2 2018-02-08 1

Есть вторая таблица - Таблица мероприятий
Код мероприятия Код ТТ SKU Дата начала мероприятия Дата окончания мероприятия
1 00001 SKU1 2018-02-02 2018-02-03
2 00001 SKU1 2018-02-05 2018-02-06
3 00001 SKU2 2018-02-01 2018-02-03
4 00003 SKU1 2018-02-02 2018-02-02

Каждый [Номер расчета] + [Код ТТ] + [SKU] имеет свой диапазон периода: начиная [Дата расчета] - ([Количество недель] * 7 -1) и заканчивая на дату расчета. Но следует учесть, если у [Номер расчета] + [Код ТТ] + [SKU] в периоде были дни с мероприятиями в соответствии второй таблицы, то начальный период сдвигается на количество этих же дней назад.

Например, возьмем первую строку, [Номер расчета1]+[00001]+[SKU1]. [Дата расчета] = 8.02.2018. Для этой строки максимальная дата это 8.02.2018. Минимальная дата равно [Дата расчета] - ([Количество недель] * 7 -1) , то есть 8.02.2018 - (1*7 - 1) = 02.02.2018. Поскольку в этом периоде были мероприятия 2 и 3 числа, а также 5 и 6 февраля 2018 для соответствующей [Код ТТ] + [SKU], то всего дней 4. Это [Количество дней сдвига]. Далее [Минимальная дата со сдвигом] = [Минимальная дата] - [Кол-во дней для сдвига], то есть 8.02.2018 - 4 = 29.01.2018.

Аналогично для остальных строк первой таблицы. Потом из всех строк находим минимальную и максимальную дату.

Это нужно для того, чтобы знать за какой период потом выгружать данные (отгрузки, продажи, возвраты и другие показатели) исходя первой таблицы.

Написал запрос. Работает так как надо. Но для простой задачи получился длинный запрос. Чую что можно как то покороче написать, но пока не нашел решение, алгоритм. А в рабочей базе первые и вторые таблицы большие. Следовательно, будет тормозить очень сильно.

Как можно написать запрос более оптимальнее и короче для данной задачи?

+SQL-запрос с исходными данными
DECLARE @ТаблицаРасчетов table(
	[Номер расчета] varchar(15),
	[Код ТТ] varchar(8),
	[SKU] varchar(8),
	[Дата расчета] date,
	[Количество недель] integer)
;

INSERT INTO
  @ТаблицаРасчетов
VALUES 
('Номер расчета1','00001','SKU1','08.02.2018',1),
('Номер расчета1','00001','SKU2','08.02.2018',1),
('Номер расчета1','00002','SKU1','08.02.2018',1),
('Номер расчета1','00002','SKU2','08.02.2018',1),
('Номер расчета2','00001','SKU1','08.02.2018',1),
('Номер расчета2','00003','SKU1','08.02.2018',1),
('Номер расчета2','00003','SKU2','08.02.2018',1)
;

DECLARE @ТаблицаМероприятий table(
	[Код мероприятия] integer,
	[Код ТТ] varchar(8),
	[SKU] varchar(8),
	[Дата начала мероприятия] date,
	[Дата окончания мероприятия] date
)
;

INSERT INTO
  @ТаблицаМероприятий
VALUES 
(1,'00001','SKU1','02.02.2018','03.02.2018'),
(2,'00001','SKU1','05.02.2018','06.02.2018'),
(3,'00001','SKU2','01.02.2018','03.02.2018'),
(4,'00003','SKU1','02.02.2018','02.02.2018')
;

DECLARE @ДатаНачала date
DECLARE @ДатаОкончания date
;

WITH Таблица AS
(
	SELECT
		Таб1.[Номер расчета],
		Таб1.[Код ТТ],
		Таб1.[SKU],
		Таб1.[Дата расчета],
		Таб1.[Количество недель],
		Таб1.[Дата расчета],	
		[Дата] = Таб1.[Дата расчета]
	FROM
		@ТаблицаРасчетов Таб1	
	UNION ALL
		SELECT
			Таблица.[Номер расчета],
			Таблица.[Код ТТ],
			Таблица.[SKU],
			Таблица.[Дата расчета],
			Таблица.[Количество недель],
			Таблица.[Дата расчета],			
			[Дата] = DATEADD( DAY, -1, Таблица.[Дата])
		FROM
			Таблица
		WHERE
			Таблица.[Дата] >DATEADD( DAY, -1 * (Таблица.[Количество недель]*7-1), Таблица.[Дата расчета])
),

Таблица2 AS
(
	SELECT
		Таб1.[Номер расчета],
		Таб1.[Код ТТ],
		Таб1.[SKU],
		Таб1.[Дата],
		(CASE
			WHEN Таб1.[Дата] Between Таб2.[Дата начала мероприятия] AND Таб2.[Дата окончания мероприятия] THEN
				1
			ELSE
				0				
		END) AS [Кол-во дней для сдвига]
	FROM
		Таблица	Таб1
	LEFT JOIN
		@ТаблицаМероприятий Таб2
	ON
		Таб1.[Код ТТ] = Таб2.[Код ТТ]
		AND
		Таб1.[SKU] = Таб2.[SKU]	
)
,

Таблица3 AS (
	SELECT
		Таб2.[Номер расчета],
		Таб2.[Код ТТ],
		Таб2.[SKU],
		Таб2.[Минимальная дата],
		Таб2.[Максимальная дата],
		Таб2.[Кол-во дней для сдвига],
		DATEADD( DAY, -1 * Таб2.[Кол-во дней для сдвига], Таб2.[Минимальная дата]) AS [Минимальная дата со сдвигом]
	FROM
		(SELECT
			Таб1.[Номер расчета],
			Таб1.[Код ТТ],
			Таб1.[SKU],
			MIN(Таб1.Дата) AS [Минимальная дата],
			MAX(Таб1.Дата) AS [Максимальная дата],
			SUM(Таб1.[Кол-во дней для сдвига]) AS [Кол-во дней для сдвига]
		FROM 
			Таблица2 Таб1
		GROUP BY
			Таб1.[Номер расчета],
			Таб1.[Код ТТ],
			Таб1.[SKU]) AS Таб2
),

Таблица4 AS (
	SELECT
		MIN([Минимальная дата со сдвигом]) AS [Минимальная дата],
		MAX ([Максимальная дата]) AS [Максимальная дата]
	FROM
		Таблица3
)

SELECT * FROM Таблица4

OPTION (
  MAXRECURSION 0 )
16 фев 18, 13:45    [21196813]     Ответить | Цитировать Сообщить модератору
 Re: Минимальная и максимальная дата  [new]
assmsk
Member

Откуда:
Сообщений: 169
With cte as (
Select
a.[Номер расчета]
,a.[Код ТТ]
,a.SKU
,dateadd(dd,((a.[Количество недель]*7)-1)*-1,a.[Дата расчета]) as Start_data
,a.[Дата расчета] as mx_dd
,sum(datediff(dd,b.[Дата начала мероприятия],b.[Дата окончания мероприятия])+1) as sdvig
from @ТаблицаРасчетов a
left join @ТаблицаМероприятий b
on a.[Код ТТ] =b.[Код ТТ]
and a.SKU = b.SKU
and b.[Дата начала мероприятия] between dateadd(dd,((a.[Количество недель]*7)-1)*-1,a.[Дата расчета]) and a.[Дата расчета]
group by a.[Номер расчета]
,a.[Код ТТ]
,a.SKU
,dateadd(dd,((a.[Количество недель]*7)-1)*-1,a.[Дата расчета])
,a.[Дата расчета])


Select min(dateadd(dd,isnull(sdvig,0)*-1,start_data)) as min_dd ,max(mx_dd) as max_dd from cte
16 фев 18, 14:42    [21197036]     Ответить | Цитировать Сообщить модератору
 Re: Минимальная и максимальная дата  [new]
Kopelly
Member

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

Без рекурсии С учетом что интервалы мероприятий пересекаются:

DECLARE @ТаблицаРасчетов table(
	[Номер расчета] varchar(15),
	[Код ТТ] varchar(8),
	[SKU] varchar(8),
	[Дата расчета] date,
	[Количество недель] integer)
;

INSERT INTO
  @ТаблицаРасчетов
VALUES 
('Номер расчета1','00001','SKU1','08.02.2018',1),
('Номер расчета1','00001','SKU2','08.02.2018',1),
('Номер расчета1','00002','SKU1','08.02.2018',1),
('Номер расчета1','00002','SKU2','08.02.2018',1),
('Номер расчета2','00001','SKU1','08.02.2018',1),
('Номер расчета2','00003','SKU1','08.02.2018',1),
('Номер расчета2','00003','SKU2','08.02.2018',1)
;

DECLARE @ТаблицаМероприятий table(
	[Код мероприятия] integer,
	[Код ТТ] varchar(8),
	[SKU] varchar(8),
	[Дата начала мероприятия] date,
	[Дата окончания мероприятия] date
)
;

INSERT INTO
  @ТаблицаМероприятий
VALUES 
(1,'00001','SKU1','02.02.2018','03.02.2018'),
(2,'00001','SKU1','05.02.2018','06.02.2018'),
(3,'00001','SKU2','01.02.2018','03.02.2018'),
(4,'00003','SKU1','02.02.2018','02.02.2018')
;

DECLARE @ДатаНачала date;
DECLARE @ДатаОкончания date;

With НепересекПериодыМероприятий as (
Select [Код ТТ],[SKU],[Дата начала мероприятия],[Дата окончания мероприятия],[Длительность],
       Sum([Длительность]) Over (Partition By [Код ТТ],[SKU] Order By [Дата окончания мероприятия] Desc) ДлительностьПоследующихМероприятий
From (
Select distinct [Код ТТ],[SKU],min([Дата начала мероприятия]) as [Дата начала мероприятия],max([Дата окончания мероприятия]) as [Дата окончания мероприятия],
DATEDIFF(DAY, min([Дата начала мероприятия]),max([Дата окончания мероприятия]))+ 1 as [Длительность]

From (
Select [Код ТТ],[SKU],[Дата начала мероприятия],[Дата окончания мероприятия],
       sum(IsStart) over (Partition by  [Код ТТ],[SKU] Order by [Дата начала мероприятия],[Дата окончания мероприятия]) as GrpID
From (
Select [Код ТТ],[SKU],[Дата начала мероприятия],[Дата окончания мероприятия],
       case When max([Дата окончания мероприятия]) 
	                  Over (Partition by  [Код ТТ],[SKU] Order by [Дата начала мероприятия],[Дата окончания мероприятия]
	                             Rows between unbounded preceding and 1 preceding) >=  [Дата начала мероприятия]
			Then 0 else 1 End as IsStart
  From @ТаблицаМероприятий tm ) tmg) tm_Periods
  Group by [Код ТТ],[SKU],GrpID) tm_Periods_Long)
	SELECT
		Таб1.[Номер расчета],
		Таб1.[Код ТТ],
		Таб1.[SKU],
		Таб1.[Дата расчета],
		Таб1.[Количество недель],
		Таб1.[Дата расчета] as [Дата],
		DATEADD( DAY, 1-Таб1.[Количество недель]*7, Таб1.[Дата расчета]),
		Сдвиг.НоваяДатаНачалаРасчета
	FROM
		@ТаблицаРасчетов Таб1	
	outer apply (
	Select top 1 
	       DATEADD( DAY, 1-Таб1.[Количество недель]*7 - [ДлительностьПоследующихМероприятий], Таб1.[Дата расчета]) as НоваяДатаНачалаРасчета 
	 From НепересекПериодыМероприятий Пер
	 Where Пер.[SKU] = Таб1.[SKU] and Пер.[Код ТТ] = Таб1.[Код ТТ]
	   and DATEADD( DAY, 1-Таб1.[Количество недель]*7 - [ДлительностьПоследующихМероприятий], Таб1.[Дата расчета]) < Пер.[Дата начала мероприятия]
	  Order by [Дата начала мероприятия]
	) Сдвиг
16 фев 18, 15:09    [21197179]     Ответить | Цитировать Сообщить модератору
 Re: Минимальная и максимальная дата  [new]
ferzmikk
Member

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

О, супер! Спасибо!
16 фев 18, 15:27    [21197250]     Ответить | Цитировать Сообщить модератору
 Re: Минимальная и максимальная дата  [new]
ferzmikk
Member

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

Спасибо! Пока не могу запустить, так как у меня стоит Microsoft SQL Server 2008. Запущу на другом компе. Из за этого пишет "Функции параллельного хранилища данных (PDW) не включены.".
16 фев 18, 15:28    [21197256]     Ответить | Цитировать Сообщить модератору
 Re: Минимальная и максимальная дата  [new]
ferzmikk
Member

Откуда:
Сообщений: 2031
Можно еще добавить в коде присвоение в переменные
SELECT @ДатаНачала  = min(dateadd(dd,isnull(sdvig,0)*-1,start_data)) from cte
SELECT @ДатаОкончания = max(mx_dd) from cte
16 фев 18, 15:35    [21197280]     Ответить | Цитировать Сообщить модератору
 Re: Минимальная и максимальная дата  [new]
ferzmikk
Member

Откуда:
Сообщений: 2031
Kopelly
С учетом что интервалы мероприятий пересекаются:
...
В данной задаче пересечения не должны быть. У пары ТТ+SKU в один день либо есть мероприятие, либо нет. В один день больше одного мероприятия у ТТ+SKU не может быть. Хотя не помешает в запросе дополнительную проверку прописать.

Но Ваш алгоритм так интересен и возможно использовать для решения последующих подобных задач.
16 фев 18, 15:45    [21197327]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить