Помощь с запросом / Microsoft SQL Server

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помощь с запросом

14 сообщений из 14, страница 1 из 1

Помощь с запросом

#40107558

Игорь86

Участник

Сообщения: 167

Рейтинг: 0 / 0

Добрый день!

Как не опытному sqlщику, подскажите, пожалуйста

Есть 2 таблицы

Table1
ID STARTDATE FINISHDATE INFO100 2021-01-01 2021-06-30 InfTab1_1100 2021-01-07 2099-12-31 InfTab1_2

Table2
ID STARTDATE FINISHDATE INFO100 2021-01-01 2021-05-05 InfTab2_1100 2021-05-06 2099-12-31 NULL

Из этих 2-х таблиц нужно получить одну, вида
Table3
ID STARTDATE FINISHDATE INFO_T1 INFO_T2100 2021-01-01 2021-05-05 InfTab1_1 InfTab2_1100 2021-05-06 2021-06-30 InfTab1_1 NULL100 2021-01-07 2099-12-31 InfTab1_2 NULL

Прошу помощи, как это можно сделать?

...

Рейтинг:

0 / 0

27.10.2021, 20:42

| Ответить | Цитировать | Написать

Помощь с запросом

#40107591

Владислав Колосов

Участник

Сообщения: 9 447

Рейтинг: 0 / 0

Игорь86,

интервалы пересекаются, если a1 <= b2 И b1 <= a2.
А если бы вторая таблица начиналась 2021-01-02, то добавили бы две строки в результат?

...

Рейтинг:

0 / 0

27.10.2021, 22:31

| Ответить | Цитировать | Написать

Помощь с запросом

#40107647

Игорь86

Участник

Сообщения: 167

Рейтинг: 0 / 0

Владислав Колосов,

Владислав КолосовА если бы вторая таблица начиналась 2021-01-02, то добавили бы две строки в результат?

То и первая таблица начиналась бы с 2021-01-02

...

Рейтинг:

0 / 0

28.10.2021, 10:17

| Ответить | Цитировать | Написать

Помощь с запросом

#40107842

DanilaSP

Гость

Игорь86,

Если есть возможность разбить таблицы построчно (по 1 дню на строку), то дальше всё просто:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

CREATE TABLE Table1 ([DATE] date, [INFO] nvarchar(255));
INSERT Table1 VALUES ('20210101', 'Tab1_1'),
 ('20210102', 'Tab1_1'),
 ('20210103', 'Tab1_2'),
 ('20210104', 'Tab1_2');
CREATE TABLE Table2 ([DATE] date, [INFO] nvarchar(255));
INSERT Table2 VALUES ('20210101', 'Tab2_1'),
 ('20210102', NULL),
 ('20210103', NULL),
 ('20210104', NULL);
WITH cte AS (SELECT t1.[DATE] as [DATE], t1.[INFO] as [INFO_T1], t2.[INFO] as [INFO_T2]
 FROM Table1 t1 INNER JOIN Table2 t2 ON t1.[DATE] = t2.[DATE])
 SELECT MIN([DATE]) AS [STARTDATE], MAX([DATE]) AS [FINISHDATE], [INFO_T1], [INFO_T2]
 FROM cte
 GROUP BY [INFO_T1], [INFO_T2]
 ORDER BY [STARTDATE];
DROP TABLE Table1;
DROP TABLE Table2;

Не знаю, сколько у Вас разных id, но в целом 365*100 вроде не так много получается.

Как разбить таблицы построчно сходу не скажу, но видимо нужно использовать функцию DATEADD(day, 1, DATE).

...

Рейтинг:

0 / 0

28.10.2021, 18:42

| Ответить | Цитировать | Написать

Помощь с запросом

#40107860

DanilaSP

Гость

Придумал, как разбить таблицы построчно с использованием recursive cte.
Глубины рекурсии как раз хватает на 365*80 дней.

Не знаю, будет ли работать с несколькими разными ID, может придётся допиливать.

Код: 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.

CREATE TABLE Table1 (ID integer, STARTDATE date, FINISHDATE date, INFO nvarchar(255) NULL);
INSERT Table1 VALUES (100, '20210101', '20210630', 'Tab1_1'),
 (100, '20210701', '20991231', 'Tab1_2');
CREATE TABLE Table2 (ID integer, STARTDATE date, FINISHDATE date, INFO nvarchar(255) NULL); 
INSERT Table2 VALUES (100, '20210101', '20210505', 'Tab2_1'),
 (100, '20210506', '20991231', NULL);

WITH cte(ID, FINISHDATE, [DATE], INFO) AS (SELECT ID, FINISHDATE, STARTDATE, INFO FROM Table1
 UNION ALL
 SELECT ID, FINISHDATE, DATEADD(day, 1, [DATE]), INFO
 FROM cte
 WHERE [DATE] < FINISHDATE
 )
SELECT ID, [DATE], INFO
INTO Table1_
FROM cte
ORDER BY [DATE]
OPTION (MAXRECURSION 32767);

WITH cte(ID, FINISHDATE, [DATE], INFO) AS (SELECT ID, FINISHDATE, STARTDATE, INFO FROM Table2
 UNION ALL
 SELECT ID, FINISHDATE, DATEADD(day, 1, [DATE]), INFO
 FROM cte
 WHERE [DATE] < FINISHDATE
 )
SELECT ID, [DATE], INFO
INTO Table2_
FROM cte
ORDER BY [DATE]
OPTION (MAXRECURSION 32767);

WITH cte AS (SELECT t1.[ID], t1.[DATE] as [DATE], t1.[INFO] as [INFO_T1], t2.[INFO] as [INFO_T2]
 FROM Table1_ t1 INNER JOIN Table2_ t2 ON t1.[DATE] = t2.[DATE] AND t1.[ID] = t2.[ID])
 SELECT [ID], MIN([DATE]) AS [STARTDATE], MAX([DATE]) AS [FINISHDATE], [INFO_T1], [INFO_T2]
 FROM cte
 GROUP BY [ID], [INFO_T1], [INFO_T2]
 ORDER BY [ID], [STARTDATE];

DROP TABLE Table1_;
DROP TABLE Table2_;
DROP TABLE Table1;
DROP TABLE Table2;

...

Рейтинг:

0 / 0

28.10.2021, 19:59

| Ответить | Цитировать | Написать

Помощь с запросом

#40107865

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Код: 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.

declare @Table1 table (ID integer, STARTDATE date, FINISHDATE date, INFO nvarchar(255) NULL);
INSERT @Table1 VALUES (100, '20210101', '20210630', 'Tab1_1'),
 (100, '20210701', '20991231', 'Tab1_2');
declare @Table2 table (ID integer, STARTDATE date, FINISHDATE date, INFO nvarchar(255) NULL); 
INSERT @Table2 VALUES (100, '20210101', '20210505', 'Tab2_1'),
 (100, '20210506', '20991231', NULL);


with t1 as ( select * from @Table1 )
   , t2 as ( select * from @Table2 )
   , d as ( select ID, dt = STARTDATE from t1 
            union 
            select ID , STARTDATE from t2 
            union 
            select ID , FINISHDATE from t1 
            union 
            select ID , FINISHDATE from t2 
          )
    , i as ( select id, b = dt, e = lead(dt) over(partition by id order by dt) from d )

    select i.id, i.b, i.e
         , INFO1 = t1.INFO
         , INFO2 = t2.INFO
      from i 
           left outer join t1 on t1.id = i.id and t1.STARTDATE <= i.e and i.b <= t1.FINISHDATE
           left outer join t2 on t2.id = i.id and t2.STARTDATE <= i.e and i.b <= t2.FINISHDATE
      where e > dateadd(day, 1, b)
            and ( t1.INFO is not null or t2.INFO is not null )
      order by i.id, i.b

...

Рейтинг:

0 / 0

28.10.2021, 20:43

| Ответить | Цитировать | Написать

Помощь с запросом

#40107894

DanilaSP

Гость

Красиво, но сработает только для случаев, когда в ответе STARTDATE != FINISHDATE (нет периодов длиной в 1 день) поскольку UNION схлопнет повторяющиеся даты.

...

Рейтинг:

0 / 0

28.10.2021, 23:41

| Ответить | Цитировать | Написать

Помощь с запросом

#40107921

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

DanilaSP

Это все решаемо без проблем.

...

Рейтинг:

0 / 0

29.10.2021, 05:26

| Ответить | Цитировать | Написать

Помощь с запросом

#40108054

Игорь86

Участник

Сообщения: 167

Рейтинг: 0 / 0

aleks222,

Спасибо, буду думать

...

Рейтинг:

0 / 0

29.10.2021, 13:07

| Ответить | Цитировать | Написать

Помощь с запросом

#40108411

DanilaSP

Гость

Я так понимаю нужно заменить соответствующую часть кода следующим:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

   , d as ( select ID, STARTDATE from t1 
            union 
            select ID , STARTDATE from t2
          )
   , e as ( select ID , FINISHDATE from t1 
            union 
            select ID , FINISHDATE from t2 
          )
   , d2 as (select ID, STARTDATE, ROW_NUMBER() over (order by ID, STARTDATE) as rn from d)
   , e2 as (select ID, FINISHDATE, ROW_NUMBER() over (order by ID, FINISHDATE) as rn from e)
   , i as (select d2.ID, b = STARTDATE, e = FINISHDATE from d2 join e2 on d2.rn = e2.rn)

Также убрать условие e > dateadd(day, 1, b).

Остаётся условие, которое по-моему можно убрать:

Код: sql

t1.INFO is not null or t2.INFO is not null

...

Рейтинг:

0 / 0

31.10.2021, 14:13

| Ответить | Цитировать | Написать

Помощь с запросом

#40108417

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

DanilaSP

Я так понимаю нужно заменить соответствующую часть кода следующим:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

   , d as ( select ID, STARTDATE from t1 
            union 
            select ID , STARTDATE from t2
          )
   , e as ( select ID , FINISHDATE from t1 
            union 
            select ID , FINISHDATE from t2 
          )
   , d2 as (select ID, STARTDATE, ROW_NUMBER() over (order by ID, STARTDATE) as rn from d)
   , e2 as (select ID, FINISHDATE, ROW_NUMBER() over (order by ID, FINISHDATE) as rn from e)
   , i as (select d2.ID, b = STARTDATE, e = FINISHDATE from d2 join e2 on d2.rn = e2.rn)

Также убрать условие e > dateadd(day, 1, b).

Остаётся условие, которое по-моему можно убрать:

Код: sql

t1.INFO is not null or t2.INFO is not null

Не понимаешь.

...

Рейтинг:

0 / 0

31.10.2021, 15:08

| Ответить | Цитировать | Написать

Помощь с запросом

#40108419

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

Код: 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.

declare @Table1 table (ID integer, STARTDATE date, FINISHDATE date, INFO nvarchar(255) NULL);
INSERT @Table1 VALUES (100, '20210101', '20210630', 'Tab1_1'),
 (100, '20210701', '20991231', 'Tab1_2');
declare @Table2 table (ID integer, STARTDATE date, FINISHDATE date, INFO nvarchar(255) NULL); 
INSERT @Table2 VALUES (100, '20210101', '20210505', 'Tab2_1'),
 (100, '20210506', '20991231', NULL);


with t1 as ( select * from @Table1 )
   , t2 as ( select * from @Table2 )
   , db as (select ID, dt = STARTDATE from t1 union select ID , STARTDATE from t2 )
   , de as (select ID , FINISHDATE from t1 union select ID , FINISHDATE from t2 )
   , dd as ( select *, de = 0 from db union all select *, de = 1 from de )
    , i0 as ( select id, b = dt, e = lead(dt) over(partition by id order by dt), de, ede = lead(de) over(partition by id order by dt)  from dd )
    , i as ( select id, 
                  b = iif( de = 0, b, dateadd( day, 1, b ) )
                , e = iif( ede = 1, e, dateadd( day, -1, e ) )
               from i0 
           ) -- это ВСЕ интервалы дат, в том числе "пустые и отсутствующие"
    select i.id, i.b, i.e
         , INFO1 = t1.INFO
         , INFO2 = t2.INFO
      from i 
           left outer join t1 on t1.id = i.id and t1.STARTDATE <= i.e and i.b <= t1.FINISHDATE
           left outer join t2 on t2.id = i.id and t2.STARTDATE <= i.e and i.b <= t2.FINISHDATE
      where e >= b -- нам не нужны "пустые интервалы"
            and ( t1.INFO is not null or t2.INFO is not null ) -- нам не нужны интервалы, где не было INFO
      order by i.id, i.b

...

Рейтинг:

0 / 0

31.10.2021, 15:30

| Ответить | Цитировать | Написать

Помощь с запросом

#40108493

DanilaSP

Гость

aleks222,

Согласен. Я не понимаю, ЧТО я не понимаю)

Вроде бы работает:

Код: 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.

declare @Table1 table (ID integer, STARTDATE date, FINISHDATE date, INFO nvarchar(255) NULL);
INSERT @Table1 VALUES (100, '20210101', '20210630', 'Tab1_1'),
 (100, '20210701', '20991231', 'Tab1_2');
declare @Table2 table (ID integer, STARTDATE date, FINISHDATE date, INFO nvarchar(255) NULL); 
INSERT @Table2 VALUES (100, '20210101', '20210505', 'Tab2_1'),
 (100, '20210506', '20991231', NULL);


with t1 as ( select * from @Table1 )
   , t2 as ( select * from @Table2 )
    , d as ( select ID, STARTDATE from t1 
            union 
            select ID , STARTDATE from t2
          )
   , e as ( select ID , FINISHDATE from t1 
            union 
            select ID , FINISHDATE from t2 
          )
   , d2 as (select ID, STARTDATE, ROW_NUMBER() over (order by ID, STARTDATE) as rn from d)
   , e2 as (select ID, FINISHDATE, ROW_NUMBER() over (order by ID, FINISHDATE) as rn from e)
   , i as (select d2.ID, b = STARTDATE, e = FINISHDATE from d2 join e2 on d2.rn = e2.rn)

    select i.id, i.b, i.e
         , INFO1 = t1.INFO
         , INFO2 = t2.INFO
      from i 
           left outer join t1 on t1.id = i.id and t1.STARTDATE <= i.e and i.b <= t1.FINISHDATE
           left outer join t2 on t2.id = i.id and t2.STARTDATE <= i.e and i.b <= t2.FINISHDATE
      order by i.id, i.b

...

Рейтинг:

0 / 0

31.10.2021, 20:10

| Ответить | Цитировать | Написать

Помощь с запросом

#40108673

aleks222

Участник

Сообщения: 2 564

Рейтинг: 0 / 0

DanilaSP

aleks222,

Согласен. Я не понимаю, ЧТО я не понимаю)

Вроде бы работает:

Код: 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.

declare @Table1 table (ID integer, STARTDATE date, FINISHDATE date, INFO nvarchar(255) NULL);
INSERT @Table1 VALUES (100, '20210101', '20210630', 'Tab1_1'),
 (100, '20210701', '20991231', 'Tab1_2');
declare @Table2 table (ID integer, STARTDATE date, FINISHDATE date, INFO nvarchar(255) NULL); 
INSERT @Table2 VALUES (100, '20210101', '20210505', 'Tab2_1'),
 (100, '20210506', '20991231', NULL);


with t1 as ( select * from @Table1 )
   , t2 as ( select * from @Table2 )
    , d as ( select ID, STARTDATE from t1 
            union 
            select ID , STARTDATE from t2
          )
   , e as ( select ID , FINISHDATE from t1 
            union 
            select ID , FINISHDATE from t2 
          )
   , d2 as (select ID, STARTDATE, ROW_NUMBER() over (order by ID, STARTDATE) as rn from d)
   , e2 as (select ID, FINISHDATE, ROW_NUMBER() over (order by ID, FINISHDATE) as rn from e)
   , i as (select d2.ID, b = STARTDATE, e = FINISHDATE from d2 join e2 on d2.rn = e2.rn)

    select i.id, i.b, i.e
         , INFO1 = t1.INFO
         , INFO2 = t2.INFO
      from i 
           left outer join t1 on t1.id = i.id and t1.STARTDATE <= i.e and i.b <= t1.FINISHDATE
           left outer join t2 on t2.id = i.id and t2.STARTDATE <= i.e and i.b <= t2.FINISHDATE
      order by i.id, i.b

Глазки следует протирать.
Вроде <> работает.
Увы.

...

Рейтинг:

0 / 0

01.11.2021, 18:06

| Ответить | Цитировать | Написать

14 сообщений из 14, страница 1 из 1

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помощь с запросом

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&fpage=10&tid=1684141]:	0ms
get settings:	8ms
get forum list:	13ms
check forum access:	3ms
check topic access:	3ms
track hit:	36ms
get topic data:	11ms
get forum data:	2ms
get page messages:	53ms
get tp. blocked users:	2ms
others:	12ms

total:	143ms