Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Минимальная и максимальная дата / 7 сообщений из 7, страница 1 из 1
16.02.2018, 13:45
    #39602951
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Минимальная и максимальная дата
Здравствуйте!

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

Есть первая таблица - Таблица расчетов.
Номер расчета Код ТТ 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-032 00001 SKU1 2018-02-05 2018-02-063 00001 SKU2 2018-02-01 2018-02-034 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-запрос с исходными данными
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
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 )

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.02.2018, 14:42
    #39603004
assmsk
assmsk
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Минимальная и максимальная дата
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.02.2018, 15:09
    #39603039
Kopelly
Kopelly
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Минимальная и максимальная дата
ferzmikk,

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
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 [Дата начала мероприятия]
	) Сдвиг
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.02.2018, 15:27
    #39603075
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Минимальная и максимальная дата
assmsk,

О, супер! Спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.02.2018, 15:28
    #39603077
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Минимальная и максимальная дата
Kopelly,

Спасибо! Пока не могу запустить, так как у меня стоит Microsoft SQL Server 2008. Запущу на другом компе. Из за этого пишет "Функции параллельного хранилища данных (PDW) не включены.".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.02.2018, 15:35
    #39603090
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Минимальная и максимальная дата
Можно еще добавить в коде присвоение в переменные
Код: sql
1.
2.
SELECT @ДатаНачала  = min(dateadd(dd,isnull(sdvig,0)*-1,start_data)) from cte
SELECT @ДатаОкончания = max(mx_dd) from cte
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.02.2018, 15:45
    #39603112
ferzmikk
ferzmikk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Минимальная и максимальная дата
KopellyС учетом что интервалы мероприятий пересекаются:
...В данной задаче пересечения не должны быть. У пары ТТ+SKU в один день либо есть мероприятие, либо нет. В один день больше одного мероприятия у ТТ+SKU не может быть. Хотя не помешает в запросе дополнительную проверку прописать.

Но Ваш алгоритм так интересен и возможно использовать для решения последующих подобных задач.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Минимальная и максимальная дата / 7 сообщений из 7, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]