T-SQL : Slicing time Islands and gaps

Question

Hello,

Consider the following table that gives the validity dates of an item :

create table #table1(  
 DD_S int,  
 DF_S int,  
 id_prod int,  
 is_valid bit  
 )  
 insert into #table1  
 values  
 (20190101,20190601,10,0),  
 (20190601,20190901,10,0),  
 (20190901,20200701,10,0),  
 (20200701,20211001,10,0),  
 (20211001,20211101,10,0),  
 (20211101,20220401,10,1),  
 (20220401,20291231,10,0),  
 (20190101,20200201,15,1),  
 (20200201,20301101,15,0)  

Now consider the following table that gives the identifier of a item throughout time:

create table #table2(  
  DD int,  
  DF int,  
  identifier int,  
  id_prod int  
 )  
   
 insert into #table2  
 VALUES  
  (20190101,20211001,5,10),      
  (20211001,20211101,4,10),    
  (20211101,20291231,5,10),   
  (20190101,20190501,6,15),  
  (20190501,20400101,7,15)  

The goal is to 'slice' the first table to associate to each Item , while taking into account the time periods. The result should look like this:

 DD_S          DF_S      id_Prod   identifier        DD           DF        IS_Valid  
 20190101    20190601    10          5             20190101    20211001       0  
 20190601    20190901    10          5             20190101    20211001       0  
 20190901    20200701    10          5             20190101    20211001   0  
 20200701    20211001    10          5             20190101    20211001       0  
 20211001    20211101    10          4             20211001    20211101       0  
 20211101    20220401    10          5             20211101    20291231       1  
 20220401    20291231    10          5             20211101    20291231       0  
 20190101    20190501    15          6             20190101    20190501       1  
 20190501  20200201    15          7             20190501    20400101       1    
 20200201  20301101    15          7             20190501    20400101       0  

The idea is Slicing time periods by related time

Any idea or best solution to do that ?

Thanks

Answer 1

Hi @Yassir
Check this query:

;WITH CTE1 AS  
(  
 SELECT DD,DF,DD_S,DF_S,T2.id_prod,identifier,is_valid  
 FROM #table2 T2 LEFT JOIN #table1 T1 ON T1.DD_S>=T2.DD AND T1.DD_S<T2.DF AND T2.id_prod=T1.id_prod  
),CTE2 AS  
(  
SELECT DD_S,CASE WHEN DF>=DF_S THEN DF_S ELSE DF END AS DF_S,id_prod,identifier,is_valid  
FROM CTE1   
UNION   
SELECT CASE WHEN DF < DF_S THEN DF END AS DD_S,DF_S,id_prod,LEAD(identifier)OVER(PARTITION BY id_prod ORDER BY DD) AS identifier,is_valid  
FROM CTE1  
)  
SELECT  *   
FROM CTE2  
WHERE DD_S IS NOT NULL  
ORDER BY id_prod,DD_S  

Best regards,
LiHong

Answer 2

Check an idea:

;  
with t2 as  
(  
    select DD, DF, identifier, id_prod  
    from #table2  
    union all  
    select max(DF), 99991231, 5, id_prod  
    from #table2  
    group by id_prod  
),  
C as  
(  
    select t1.*, t2.identifier,  
        iif(t1.DD_S >= t2.DD, t1.DD_S, t2.DD) as DD,  
        iif(t1.DF_S <= t2.DF, t1.DF_S, t2.DF) as DF  
    from #table1 t1  
    inner join t2 on t2.id_prod = t1.id_prod  
)  
select DD_S, DF_S, id_prod, identifier, DD, DF, is_valid  
from C  
where DD < DF  
order by id_prod, DD  

Answer 3

Hi,

I did the query as bellow i don't know if will perfectly work for another data but with my sample data is work fine just one problem when i do the union the first query doesn't work i don't know why but when i execute the query one by one i have the result.

Can you please tell me why the union doesn't work and if you have any elegant solution it will be great

;with cte as(  
     SELECT  a.[DD_S]  
           ,a.[DF_S]  
           ,a.[id_Prod]  
           ,b.[identifier]  
           ,b.[DD]   
    , b.[DF]  
    ,LAG([DD_S]) over (partition by a.id_prod order by DD_S) as LagDD_S  
    ,LAG([DF_S]) over (partition by a.id_prod order by DD_S) as LagDF_S  
    ,LEAD([DD_S]) over (partition by a.id_prod order by DD_S) as LeadDD_S  
    ,LEAD([DD]) over (partition by a.id_prod order by DD_S) as LeadDD  
  
       FROM table1 a   
       join table2 b  
      on a.[id_Prod]=b.[id_Prod]   
  )  
  
    select  DF as DD_S,LagDD_S as DF_S,id_prod,identifier  from cte  
   where   
   1=1  
    and DF<LagDF_S  
    and LeadDD_S is null /*Doesn't work with union but i execute the query one by one it works */  
  union  
  select DD_S,DF_S,id_prod,identifier from cte   
  where  ([DD_S]>=[DD] and [DF_S]<=[DF])  
  union  
  select DD_S,LeadDD as DF_S,id_prod,identifier  from cte  
  where LagDD_S is null   
   and  ([DD_S]<=[DD] and [DF_S]>=[DF])  
   order by id_prod,dd_s  

Thanks