Tags

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


T-SQL Query | [ The Overlapping Dates Puzzle ]

Puzzle Statement

1. We have a table named Overlap.
2. Here we have start date and end date , now if the end date + 1 = start date; These dates are called Overlapping dates, we have to club those rows into one row
3. For details please check out the sample input and expected output

OverlappingPuzzle

Sample Input

Id StartDate StopDate
10 25-01-2014 30-01-2014
11 21-02-2014 24-02-2014
12 07-04-2014 30-04-2014
13 01-11-2014 30-11-2014
14 01-12-2014 31-12-2014

Expected Output

ID StartDate EndDate
13 01-11-2014 01-12-2014

Rules/Restrictions

  • Your solution should be should use “SELECT” statement or “CTE”
  • Your solution should be generic in nature.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script Use the below script to generate the source table and fill them up with the sample data.


--

CREATE TABLE Overlap
(
	 Id INT
	,StartDate DATETIME
	,StopDate DATETIME
)
GO

INSERT INTO Overlap VALUES
(10     ,'2014-01-25 00:00:00.000'     ,'2014-01-30 00:00:00.000'),
(11     ,'2014-02-21 00:00:00.000'     ,'2014-02-24 00:00:00.000'),
(12     ,'2014-04-07 00:00:00.000'     ,'2014-04-30 00:00:00.000'),
(13     ,'2014-11-01 00:00:00.000'     ,'2014-11-30 00:00:00.000'),
(14     ,'2014-12-01 00:00:00.000'     ,'2014-12-31 00:00:00.000')


--

Update 2 May | Solution 1 


--

------------------ SOLUTION 1 ----------------------


;WITH CTE AS
(
	SELECT a.*,DATEADD(DAY, 1, A.StopDate) Ndt
	FROM Overlap a LEFT OUTER JOIN Overlap b
	ON a.Id = b.Id
)
SELECT b.ID, b.StartDate , b.Ndt EndDate FROM CTE c LEFT JOIN CTE b ON c.StartDate = b.Ndt WHERE b.Id IS NOT NULL

--

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

Keep Learning

Http://MSBISkills.com