ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Минимальная и максимальная дата
7 сообщений из 7, страница 1 из 1
Минимальная и максимальная дата
    #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, 13:45
| Ответить | Цитировать | Написать  
Минимальная и максимальная дата
    #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, 14:42
| Ответить | Цитировать | Написать  
Минимальная и максимальная дата
    #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:09
| Ответить | Цитировать | Написать  
Минимальная и максимальная дата
    #39603075
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
assmsk,

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

Спасибо! Пока не могу запустить, так как у меня стоит Microsoft SQL Server 2008. Запущу на другом компе. Из за этого пишет "Функции параллельного хранилища данных (PDW) не включены.".
...
Рейтинг: 0 / 0
16.02.2018, 15:28
| Ответить | Цитировать | Написать  
Минимальная и максимальная дата
    #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:35
| Ответить | Цитировать | Написать  
Минимальная и максимальная дата
    #39603112
ferzmikk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ferzmikk
Участник
KopellyС учетом что интервалы мероприятий пересекаются:
...В данной задаче пересечения не должны быть. У пары ТТ+SKU в один день либо есть мероприятие, либо нет. В один день больше одного мероприятия у ТТ+SKU не может быть. Хотя не помешает в запросе дополнительную проверку прописать.

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


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