присоединиться к таблице Gap и Islands SQL Teradata

Я надеюсь, что этот запрос поможет вам. Разбивка кода и объяснение приведены ниже

select a.*,B.ORIGINAL_STATUS
from mytable2 a
join (select *,LAG(CHANGE_DATE) OVER(ORDER BY CHANGE_DATE) as previous_date_value
from mytable)b
on a.Subs_ID =b.Subs_ID and a.CONNECT_DATE >b.previous_date_value and a.CONNECT_DATE<= b.CHANGE_DATE

UNION

select * from (
select a.*,CASE WHEN A.CONNECT_dATE<B.CHANGE_DATE THEN b.ORIGINAL_STATUS ELSE NULL END AS STATUS
from mytable2 a
join (
select Subs_ID,ORIGINAL_STATUS,NEW_STATUS,CHANGE_DATE from
(
select Subs_ID,ORIGINAL_STATUS,NEW_STATUS,CHANGE_DATE,row_number() over(partition by Subs_ID order by CHANGE_DATE)rn
from mytable
)mytable_less
where rn=1)b
on a.Subs_ID=b.Subs_ID)less_than_available_date
where status is not null

UNION

select * from (
select a.*,CASE WHEN A.CONNECT_dATE>B.CHANGE_DATE THEN b.NEW_STATUS ELSE NULL END AS STATUS
from mytable2 a
join (
select Subs_ID,ORIGINAL_STATUS,NEW_STATUS,CHANGE_DATE from
(
select Subs_ID,ORIGINAL_STATUS,NEW_STATUS,CHANGE_DATE,row_number() over(partition by Subs_ID order by CHANGE_DATE desc)rn
from mytable
)mytable_less
where rn=1)b
on a.Subs_ID=b.Subs_ID)greater_than_available_date
where status is not null

Объяснение: Первый блок кода возвращает статус даты подключения, если дата подключения присутствует в диапазоне доступных дат изменения. Я использую функцию задержки для получения предыдущего значения даты изменения и сравнения с датой подключения.

select a.*,B.ORIGINAL_STATUS
from mytable2 a
join (select *,LAG(CHANGE_DATE) OVER(ORDER BY CHANGE_DATE) as previous_date_value
from mytable)b
on a.Subs_ID =b.Subs_ID and a.CONNECT_DATE >b.previous_date_value and a.CONNECT_DATE<= b.CHANGE_DATE

Это дает нам следующий набор результатов

+---------+--------------+-----------------+
| Subs_ID | CONNECT_DATE | ORIGINAL_STATUS |
+---------+--------------+-----------------+
|  123456 | 2017-12-03   |               2 |
|  123456 | 2018-11-04   |               2 |
|  123456 | 2019-10-05   |               2 |
|  123456 | 2019-12-30   |               8 |
|  123456 | 2020-10-04   |               8 |
+---------+--------------+-----------------+

Теперь нам нужно найти статус на 01.12.2017, который меньше доступной даты изменения. таблица 2 объединяется с таблицей 1, которая имеет строку минимальной даты изменения, и ORIGINAL_STATUS берется, если дата подключения меньше даты изменения.

select * from (
select a.*,CASE WHEN A.CONNECT_dATE<B.CHANGE_DATE THEN b.ORIGINAL_STATUS ELSE NULL END AS STATUS
from mytable2 a
join (
select Subs_ID,ORIGINAL_STATUS,NEW_STATUS,CHANGE_DATE from
(
select Subs_ID,ORIGINAL_STATUS,NEW_STATUS,CHANGE_DATE,row_number() over(partition by Subs_ID order by CHANGE_DATE)rn
from mytable
)mytable_less
where rn=1)b
on a.Subs_ID=b.Subs_ID)less_than_available_date
where status is not null

+---------+--------------+--------+
| Subs_ID | CONNECT_DATE | STATUS |
+---------+--------------+--------+
|  123456 | 2017-12-01   |      1 |
+---------+--------------+--------+

Остаются записи, у которых дата подключения больше доступной даты изменения. Это достигается с помощью приведенного ниже кода, и таблица 2 объединяется с таблицей 1, имеющей строку с максимальной датой изменения, и новый статус принимается, если дата подключения больше даты изменения.

select * from (
select a.*,CASE WHEN A.CONNECT_dATE>B.CHANGE_DATE THEN b.NEW_STATUS ELSE NULL 
END AS STATUS
from mytable2 a
join (
select Subs_ID,ORIGINAL_STATUS,NEW_STATUS,CHANGE_DATE from
(
select Subs_ID,ORIGINAL_STATUS,NEW_STATUS,CHANGE_DATE,row_number() 
over(partition by Subs_ID order by CHANGE_DATE desc)rn
from mytable
)mytable_less
where rn=1)b
on a.Subs_ID=b.Subs_ID)greater_than_available_date
where status is not null

    +---------+--------------+--------+
    | Subs_ID | CONNECT_DATE | STATUS |
    +---------+--------------+--------+
    |  123456 | 2021-05-21   |      9 |
    +---------+--------------+--------+

Наконец, после применения union мы получаем требуемый набор результатов.

+---------+--------------+-----------------+
| Subs_ID | CONNECT_DATE | ORIGINAL_STATUS |
+---------+--------------+-----------------+
|  123456 | 2017-12-01   |               1 |
|  123456 | 2017-12-03   |               2 |
|  123456 | 2018-11-04   |               2 |
|  123456 | 2019-10-05   |               2 |
|  123456 | 2019-12-30   |               8 |
|  123456 | 2020-10-04   |               8 |
|  123456 | 2021-05-21   |               9 |
+---------+--------------+-----------------+

Обычно я решаю такие проблемы, избегая неэквивалентного соединения:

SELECT
   Subs_ID
  ,dt AS CONNECT_DATE
 -- fill the NULLs
 -- if the Connect_date more than the Change_Date will take the last NEW_STATUS  
  ,Coalesce(Lag(NEW_STATUS IGNORE NULLS)
            Over (PARTITION BY Subs_ID
                  ORDER BY dt)
 -- if the Connect_date less than the Change_Date will take the first ORIGINAL_STATUS 
           ,Lead(ORIGINAL_STATUS IGNORE NULLS)
            Over (PARTITION BY Subs_ID
                  ORDER BY dt)
           )
FROM 
 ( -- combine both tables
   SELECT
      1 AS x -- flag indicating the source tables
     ,Subs_ID
     ,ORIGINAL_STATUS
     ,NEW_STATUS
     ,CHANGE_DATE AS dt
   FROM t1
   UNION ALL
   SELECT
      2 AS x
     ,Subs_ID
     ,NULL -- to get the same number of columns
     ,NULL -- to get the same number of columns
     ,CONNECT_DATE 
   FROM t2
 ) AS t
QUALIFY x = 2 -- return only rows from t2
ORDER BY CONNECT_DATE
;

Чтобы увидеть, как это работает, прокомментируйте QUALIFY.

Если ваши данные позволяют удалить IGNORE NULLS в LEAD, это будет более эффективно (только один шаг STAT вместо двух).