SQL Server - grouping overlapping dates-CodePudding

I currently have a table that looks like so:

| ID | Start_Date | End_Date   |
| -- | ---------- | ---------- |
| 1  | 2021-01-10 | 2021-01-11 |
| 1  | 2021-01-12 | 2021-01-13 |
| 1  | 2021-04-14 | 2021-04-15 |
| 1  | 2021-10-01 | 2021-10-02 |
| 1  | 2021-10-03 | 2021-10-04 |
| 1  | 2021-10-05 | 2021-10-06 |

I need to transform so that any dates that overlap are grouped together like so:

| ID | Start_Date | End_Date   |
| -- | ---------- | ---------- |
| 1  | 2021-01-10 | 2021-01-13 |
| 1  | 2021-04-14 | 2021-04-15 |
| 1  | 2021-10-01 | 2021-10-06 |

Any help at all would be greatly appreciated.

CodePudding user response：

That is what you need

WITH cte AS (
    SELECT 
        *,
        SUM(CASE WHEN start_date = DATEADD(day, 1, prev_end_date) THEN 0 ELSE 1 END) OVER (PARTITION BY id ORDER BY rn) AS group_id
    FROM (        
        SELECT 
            *,
            LAG(end_date) OVER (PARTITION BY id ORDER BY start_date) AS prev_end_date,
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY start_date) AS rn
        FROM data        
    ) groups
)
SELECT 
    id,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date
FROM cte
GROUP BY id, group_id

You can check a working demo here

CodePudding user response：

You may use a script as below:

select year(start_date),month(start_date),min(start_date), year(end_date),month(end_date),max(end_date)  
from yourtable
group by year(start_date),month(start_date),year(end_date),month(end_date) ;