Query help with continuous start and end date

Question

Hello All -

My record data set is here,

SELECT '1' AS ID, '1/1/2020' AS START_DT, '1/31/2020' AS END_DT  
UNION ALL  
SELECT '1' AS ID, '1/31/2020' AS START_DT, '4/30/2020' AS END_DT  
UNION ALL  
SELECT '1' AS ID, '6/1/2020' AS START_DT, NULL AS END_DT  
  
UNION ALL  
  
SELECT '2' AS ID, '1/1/2020' AS START_DT, '1/31/2020' AS END_DT  
UNION ALL  
SELECT '2' AS ID, '2/1/2020' AS START_DT, '4/30/2020' AS END_DT  
UNION ALL  
SELECT '2' AS ID, '6/1/2020' AS START_DT, NULL AS END_DT  
  
UNION ALL  
  
SELECT '3' AS ID, '1/1/2020' AS START_DT, '1/31/2020' AS END_DT  
UNION ALL  
SELECT '3' AS ID, '2/4/2020' AS START_DT, '4/30/2020' AS END_DT  
UNION ALL  
SELECT '3' AS ID, '6/1/2020' AS START_DT, NULL AS END_DT  

Expected output is here,

--Expected output  
SELECT '1' AS ID, '1/1/2020' AS START_DT, '4/30/2020' AS END_DT  
UNION ALL  
SELECT '1' AS ID, '6/1/2020' AS START_DT, NULL AS END_DT  
  
UNION ALL  
  
SELECT '2' AS ID, '1/1/2020' AS START_DT, '4/30/2020' AS END_DT  
UNION ALL  
SELECT '2' AS ID, '6/1/2020' AS START_DT, NULL AS END_DT  
  
UNION ALL  
  
SELECT '3' AS ID, '1/1/2020' AS START_DT, '1/31/2020' AS END_DT  
UNION ALL  
SELECT '3' AS ID, '2/4/2020' AS START_DT, '4/30/2020' AS END_DT  
UNION ALL  
SELECT '3' AS ID, '6/1/2020' AS START_DT, NULL AS END_DT  

Can anybody help with the start & end date query? If the gap is more than 3 days between previous end date and latest start end date they it is consider as 2 records else it is consider as 1 record.

Thank You

Regards,
gk1393

Answer 1

 ;with mySource as (SELECT '1' AS ID, '1/1/2020' AS START_DT, '1/31/2020' AS END_DT  
 UNION ALL  
 SELECT '1' AS ID, '1/31/2020' AS START_DT, '4/30/2020' AS END_DT  
 UNION ALL  
 SELECT '1' AS ID, '6/1/2020' AS START_DT, NULL AS END_DT  
      
 UNION ALL  
      
 SELECT '2' AS ID, '1/1/2020' AS START_DT, '1/31/2020' AS END_DT  
 UNION ALL  
 SELECT '2' AS ID, '2/1/2020' AS START_DT, '4/30/2020' AS END_DT  
 UNION ALL  
 SELECT '2' AS ID, '6/1/2020' AS START_DT, NULL AS END_DT  
      
 UNION ALL  
      
 SELECT '3' AS ID, '1/1/2020' AS START_DT, '1/31/2020' AS END_DT  
 UNION ALL  
 SELECT '3' AS ID, '2/4/2020' AS START_DT, '4/30/2020' AS END_DT  
 UNION ALL  
 SELECT '3' AS ID, '6/1/2020' AS START_DT, NULL AS END_DT)  
  
, mycte AS  
(  
    SELECT *  
        , CASE  
            WHEN DATEDIFF(day, LAG(end_dt) OVER (PARTITION BY ID ORDER BY start_dt), start_dt) <= 3 THEN 0  
            ELSE 1  
        END AS [flag]  
    FROM mySource  
),  
mycte1 AS  
(  
    SELECT *, SUM([flag]) OVER (PARTITION BY ID ORDER BY start_dt) AS grp  
    FROM mycte  
)  
SELECT ID, MIN(start_dt) AS start_dt, MAX(end_dt) AS end_dt  
FROM mycte1  
GROUP BY ID, grp  
Order by ID, start_dt  
  

Answer 2

I suggest you read this:

https://www.mssqltips.com/sqlservertip/6848/create-a-calendar-table-in-sql-identify-date-gaps-islands/

Answer 3

Hi @Kaumil Gandhi
Try this query, based on what i comment above.

create table #temp (ID varchar(5),START_DT date,END_DT date)  
 --DROP TABLE #temp  
 insert into #temp  
 SELECT '1' AS ID, '1/1/2020' AS START_DT, '1/31/2020' AS END_DT  
 UNION ALL  
 SELECT '1' AS ID, '1/31/2020' AS START_DT, '4/30/2020' AS END_DT  
 UNION ALL  
 SELECT '1' AS ID, '5/1/2020' AS START_DT, '5/30/2020' AS END_DT  
 UNION ALL  
 SELECT '1' AS ID, '6/1/2020' AS START_DT, NULL AS END_DT  
      
 UNION ALL  
      
 SELECT '2' AS ID, '1/1/2020' AS START_DT, '1/31/2020' AS END_DT  
 UNION ALL  
 SELECT '2' AS ID, '2/1/2020' AS START_DT, '3/30/2020' AS END_DT  
 UNION ALL  
 SELECT '2' AS ID, '4/1/2020' AS START_DT, '4/30/2020' AS END_DT  
 UNION ALL  
 SELECT '2' AS ID, '6/1/2020' AS START_DT, NULL AS END_DT  
      
 UNION ALL  
      
 SELECT '3' AS ID, '1/1/2020' AS START_DT, '1/31/2020' AS END_DT  
 UNION ALL  
 SELECT '3' AS ID, '2/4/2020' AS START_DT, '4/30/2020' AS END_DT  
 UNION ALL  
 SELECT '3' AS ID, '6/1/2020' AS START_DT, NULL AS END_DT  
  
;WITH CTE AS  
(  
 SELECT ID,START_DT,END_DT  
       ,LAG(END_DT,1,'2000-1-1')OVER(PARTITION BY ID ORDER BY START_DT) AS Previous_END_DT  
	   ,LEAD(START_DT,1,'2100-1-1')OVER(PARTITION BY ID ORDER BY START_DT) AS Next_START_DT  
 FROM #temp  
)  
SELECT ID,START_DT,END_DT  
FROM CTE  
WHERE DATEDIFF(DAY,Previous_END_DT,START_DT) > 3 AND DATEDIFF(DAY,ISNULL(END_DT,START_DT),Next_START_DT) > 3  
UNION  
SELECT ID,MIN(START_DT)AS START_DT  
      ,CASE WHEN MAX(CASE WHEN END_DT IS NULL THEN 1 ELSE 0 END) = 0 THEN MAX(END_DT) END AS END_DT  
FROM CTE WHERE DATEDIFF(DAY,END_DT,Next_START_DT)<= 3 OR DATEDIFF(DAY,Previous_END_DT,START_DT)<= 3  
GROUP BY ID  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.