Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

key startdate enddate
1 2015-01-01 00:00:00.000 2015-04-30 00:00:00.000
1 2015-05-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 2015-12-31 00:00:00.000
3 2016-01-01 00:00:00.000 2016-05-31 00:00:00.000
3 2016-08-01 00:00:00.000 2016-08-31 00:00:00.000
3 2016-09-01 00:00:00.000 9999-12-31 00:00:00.000
4 2016-07-01 00:00:00.000 2016-08-31 00:00:00.000
4 2016-08-25 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

Expected 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

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

Advertisements