Earliest Continuous date from set of start and end date

asked 2023-01-12T19:15:54.32+00:00
ace_Friends22 0 Reputation points

Hi All,

I have 3 columns. ID, Effective Date and Term Date in the source

I have 4 columns ID, Effective Date, Term Date and Earliest Continuous Date in the target

ID, Effective date and term date will remain same between source and target, but for Earliest Continuous Date, I need to populate min effective date which have continuous eff and term date.

For example. I have uploaded file with expected target data. If you notice continuation of Effective Date and Term Date

record 1, 2 & 3 are continuous from 02/01/2016 to 01/01/2017 hence earliest continuous date for these 3 records is 02/01/2016

record 4 has gap of 5 months and 06/01/2017 to 06/01/2017, hence earliest continuous date for the record is 06/01/2017

records 5,6,7,8 are continuous from 09/01/2017 to 02/01/2019, hence earliest continuous date for these 3 record is 09/01/2017.

I have tried in google to find solution, but not getting it. Can somebody please help me to solve this?

ID,EFFECTIVE_DATE, TERM_DATE, EFFECTIVE_CONTINUOUS_DATE
123,02/01/2016,02/29/2016,02/01/2016
123,03/01/2016,12/31/2016,02/01/2016
123,01/01/2017,01/01/2017,02/01/2016
123,06/01/2017,06/01/2017,06/01/2017
123,09/01/2017,12/31/2017,09/01/2017
123,01/01/2018,12/31/2018,09/01/2017
123,01/01/2019,01/31/2019,09/01/2017
123,02/01/2019,02/01/2019,09/01/2

017

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,476 questions
{count} votes

2 answers

Sort by: Most helpful
  1. answered 2023-01-12T19:41:40.4566667+00:00
    Jingyang Li 4,371 Reputation points
    
    Create table test (ID int,EFFECTIVE_DATE date, TERM_DATE date
    , EFFECTIVE_CONTINUOUS_DATE date)
    insert into test values(123,'02/01/2016','02/29/2016','02/01/2016'),
    (123,'03/01/2016','12/31/2016','02/01/2016'),
    (123,'01/01/2017','01/01/2017','02/01/2016'),
    (123,'06/01/2017','06/01/2017','06/01/2017'),
    (123,'09/01/2017','12/31/2017','09/01/2017'),
    (123,'01/01/2018','12/31/2018','09/01/2017'),
    (123,'01/01/2019','01/31/2019','09/01/2017'),
    (123,'02/01/2019','02/01/2019','09/01/2017')
    
    --select * from test
    ;WITH mycte AS
    (
        SELECT *,LAG(TERM_DATE) OVER (PARTITION BY ID ORDER BY EFFECTIVE_DATE) lagdt,  
     CASE
                WHEN abs(DATEDIFF(day, LAG(TERM_DATE) OVER (PARTITION BY ID ORDER BY EFFECTIVE_DATE), EFFECTIVE_DATE) )<= 1 THEN 0
                ELSE 1
            END AS [flag]
    		,DATEDIFF(day, LAG(TERM_DATE) OVER (PARTITION BY ID ORDER BY EFFECTIVE_DATE), EFFECTIVE_DATE)  d
        FROM test
    ),
    mycte1 AS
    (
        SELECT *, SUM([flag]) OVER (PARTITION BY ID ORDER BY EFFECTIVE_DATE) AS grp
        FROM mycte
    )
    
     
    SELECT ID, EFFECTIVE_DATE, TERM_DATE
    , MIN(EFFECTIVE_DATE) over(partition by ID, grp)  AS EFFECTIVE_CONTINUOUS_DATE
     
    FROM mycte1
     
    Order by ID ,EFFECTIVE_DATE 
    
    drop table test
    
    No comments

  2. answered 2023-01-13T02:39:46.3266667+00:00
    PercyTang-MSFT 1,111 Reputation points Microsoft Employee

    Hi @ace_Friends22

    According to your needs, I wrote the following code, you can give it a try.

    Create table test (ID int,EFFECTIVE_DATE date, TERM_DATE date)
    insert into test values
    (123,'02/01/2016','02/29/2016'),
    (123,'03/01/2016','12/31/2016'),
    (123,'01/01/2017','01/01/2017'),
    (123,'06/01/2017','06/01/2017'),
    (123,'09/01/2017','12/31/2017'),
    (123,'01/01/2018','12/31/2018'),
    (123,'01/01/2019','01/31/2019'),
    (123,'02/01/2019','02/01/2019');
    
    ;with T1 as(
      select A.*,case when B.ID is null then A.EFFECTIVE_DATE
                 else B.EFFECTIVE_DATE end as DATE1
      from test A left outer join test B
      on A.EFFECTIVE_DATE = DATEADD(DAY,1,B.TERM_DATE)
    ),T2 as (
      select C.*,case when D.ID is null then C.DATE1
                 else D.DATE1 end as EFFECTIVE_CONTINUOUS_DATE
      from T1 C left outer join T1 D
      on C.DATE1 = DATEADD(DAY,1,D.TERM_DATE))
    select ID,EFFECTIVE_DATE,TERM_DATE,EFFECTIVE_CONTINUOUS_DATE from T2;
    

    Best regards,

    Percy Tang

    -

    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    No comments