This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to find the date record where EndDate +1 = Start date of next record for removing the overlapping issue.
Input Data:
|
1 2 3 4 5 6 7 |
Id StartDate EndDate ----------- ---------- ---------- 1 2017-01-20 2017-02-26 2 2017-03-08 2017-05-16 3 2017-08-24 2017-09-30 4 2017-10-01 2017-11-26 5 2017-11-28 2017-12-30 |
Expected Output:
|
1 2 3 |
StartDate EndDate ---------- ---------- 2017-08-24 2017-10-01 |
Create a table with data:
|
1 2 3 |
StartDate EndDate ---------- ---------- 2017-08-24 2017-10-01 |
Solution 1:
|
1 2 3 |
select StartDate, (select StartDate from Dates where Id = a.Id+1) as EndDate from Dates a where 1 in (select DATEDIFF(DAY,a.EndDate,b.StartDate) from Dates b ) |
Solution 2:
|
1 2 3 4 5 6 7 8 9 10 |
;WITH CTE AS ( SELECT a.*,DATEADD(DAY, 1, A.EndDate) as EDate FROM Dates a LEFT OUTER JOIN Dates b ON a.Id = b.Id ) SELECT b.StartDate , b.EDate AS EndDate FROM CTE c LEFT JOIN CTE b ON c.StartDate = b.EDate WHERE b.Id IS NOT NULL |
Please try the different solution for this puzzle and share it via comment...
