How can I get the desired Output.
Logic: IDs / Records should be partitioned based on Date range, but condition is record should fall within starting (First Start Date) and Ending (Latest End Date) Date Range.
| ID | Start_Date | End_Date |
|---|---|---|
| 1 | 2022-01-01 | 2022-03-31 |
| 1 | 2022-02-15 | 2022-04-15 |
| 1 | 2022-02-01 | 2022-03-31 |
| 1 | 2022-11-02 | 2022-11-06 |
| 1 | 2022-11-20 | 2022-11-23 |
OUTPUT
| ID | Start_Date | End_Date |
|---|---|---|
| 1 | 2022-01-01 | 2022-04-15 |
| 1 | 2022-11-02 | 2022-11-06 |
| 1 | 2022-11-20 | 2022-11-23 |
CodePudding user response：
Here is one approach to a date-ranged Gaps-and-Islands
Example
Select id
,Start_Date=min(D)
,End_Date =max(D)
From (
Select ID
,D
,Grp = dense_rank() over (partition by id order by D)
- datediff(day,0,D)
From YourTable A
Join ( Select Top 5000 N=-1 Row_Number() Over (Order By (Select NULL)) From master..spt_values n1, master..spt_values n2 ) B
on N<=datediff(day,[Start_date],[End_Date])
Cross Apply ( values ( convert(date,dateadd(DAY,N,[start_date])) ) ) C(D)
) A
Group By ID,Grp
Order By ID,min(D)
Results
id Start_Date End_Date
1 2022-01-01 2022-04-15
1 2022-11-02 2022-11-06
1 2022-11-20 2022-11-23