SQL Puzzle | The Overlapping Dates Puzzle

In this puzzle you have to combine the overlapping dates in the data. End Date of first row + 1 will be the start date of the next row. Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE table1
(
	 [Key] INT
	,StartDate DATETIME
	,EndDate DATETIME
)
GO

INSERT INTO table1 VALUES       
(1,'1/1/2015','4/30/2015'),                                                    
(1,'5/1/2015','7/1/2015'),   
(1,'9/1/2015','12/31/9999'),  
(2,'7/1/2015','8/31/2015'),
(3,'12/1/2015','12/31/2015'),
(3,'1/1/2016','5/31/2016'),
(3,'8/1/2016','8/31/2016'),
(3,'9/1/2016','12/31/9999'),
(4,'7/1/2016','8/31/2016'),
(4,'8/25/2016','12/31/2016'),
(4,'2/1/2017','12/31/9999')
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--

select [key] , min (startdate) startdate , max (enddate) enddate
from 
(
	select [key], startdate, enddate, sum (rnk) over (partition by [key] order by startdate) rnk1
	from 
	(
		select [key],startdate, enddate	, case when startdate - lag (enddate) over (partition by [key] order by startdate ) <= 1
		then 0	else 1 end rnk
		from table1
	)u 
)t
group by [key], rnk1
order by [key],startdate

--

Output

--

/*------------------------
OUTPUT
------------------------*/
key         startdate               enddate
----------- ----------------------- -----------------------
1           2015-01-01 00:00:00.000 2015-07-01 00:00:00.000
1           2015-09-01 00:00:00.000 9999-12-31 00:00:00.000
2           2015-07-01 00:00:00.000 2015-08-31 00:00:00.000
3           2015-12-01 00:00:00.000 2016-05-31 00:00:00.000
3           2016-08-01 00:00:00.000 9999-12-31 00:00:00.000
4           2016-07-01 00:00:00.000 2016-12-31 00:00:00.000
4           2017-02-01 00:00:00.000 9999-12-31 00:00:00.000

(7 row(s) affected)


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com