SQL join against date ranges-CodePudding

I need to find the difference between the date. Sample Data in the table

CREATE TABLE #TEMP
(

StartDate DATE,
EndDate DATE
)

INSERT INTO #temp
VALUES
('12-01-2021','12-02-2021'),
('12-02-2021','12-03-2021'),
('12-03-2021','12-04-2021'),
('12-13-2021','12-14-2021'),
('12-14-2021','12-15-2021'),
('12-28-2021','12-29-2021')

OutPut Needed:

StartDate EndDate
12-01-2021 12-04-2021
12-13-2021  12-15-2021
12-28-2021  12-29-2021

CodePudding user response：

You can use lag to find the rows for which end date of previous row is the start date using datediff, then keep a running total of this difference for each row. Rows having the same running total are in the same group, then for each group you can get max and min of start date and end date respectively to get your desired output.

If you're using sql server:

with u as 
(select StartDate,
EndDate,
case when
coalesce(datediff(day, lag(EndDate) over(order by StartDate, EndDate), StartDate), 0) = 0 then 0
else 1 end as change
from #TEMP),
v as
(select *, sum(change) over(order by StartDate, EndDate rows unbounded preceding) as g
from u)
select min(StartDate) as StartDate, max(EndDate) as EndDate from v group by g

Fiddle