Earliest Continuous date from set of start and end date

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

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.
12,817 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jingyang Li 5,891 Reputation points
    2023-01-12T19:41:40.4566667+00:00 
    
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
    1 person found this answer helpful.
  2. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-01-13T02:39:46.3266667+00:00

    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".

    0 comments
  3. Jingyang Li 5,891 Reputation points
    2023-03-22T18:52:41.5233333+00:00

    AmitPatel, I did not see EFFECTIVECONTINUOUS_DATE = '1/1/2017'

    Create table test (ID int, EFFECTIVE_DATE date, TERM_DATE date)

insert into test values(123,'01/01/2014','10/31/2014'),

(123, '1/11/2014', '1/11/2014'),
(123, '1/1/2015', '1/1/2015'),
(123, '1/1/2016', '1/1/2016'),
(123, '2/1/2016', '12/31/2016'),
(123, '1/1/2017', '12/31/2017'),
(123, '1/1/2018', '12/31/2018'),
(123, '1/1/2019', '12/31/2019'),
(123, '1/1/2020', '12/31/2020'),
(123, '1/1/2021', '12/31/2021'),
(123, '1/1/2022', '12/31/2022'),
(123, '1/1/2023', '')


--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;

/*
ID	EFFECTIVE_DATE	TERM_DATE	EFFECTIVE_CONTINUOUS_DATE
123	2014-01-01	2014-10-31	2014-01-01
123	2014-01-11	2014-01-11	2014-01-11
123	2015-01-01	2015-01-01	2015-01-01
123	2016-01-01	2016-01-01	2016-01-01
123	2016-02-01	2016-12-31	2016-02-01
123	2017-01-01	2017-12-31	2016-02-01
123	2018-01-01	2018-12-31	2016-02-01
123	2019-01-01	2019-12-31	2016-02-01
123	2020-01-01	2020-12-31	2016-02-01
123	2021-01-01	2021-12-31	2016-02-01
123	2022-01-01	2022-12-31	2016-02-01
123	2023-01-01	1900-01-01	2016-02-01
*/
    0 comments