Find Date gap between SQL rows
DECLARE @Table TABLE
(
Id
INT IDENTITY(1,1),
Idn
INT,
StartDate DATE,
EndDate
DATE
)
INSERT INTO @Table
SELECT 1,'2013-07-01', '2014-06-30'
UNION
SELECT 1,'2014-07-01', '2015-06-30'
UNION
SELECT 1,'2015-07-10', '2016-06-30'
UNION
SELECT 1,'2016-07-05', '2017-06-30'
Select * From @Table
;WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY Idn ORDER BY StartDate) rownumber
FROM @Table
)
SELECT A.Id,
A.Idn, A.StartDate, A.EndDate, B.StartDate AS NextStartDate, DATEDIFF(D, A.EndDate, B.StartDate) AS Gap
FROM CTE AS A
JOIN CTE AS B ON B.Idn = A.Idn AND B.RowNumber = A.RowNumber + 1
WHERE DATEDIFF(D, A.EndDate, B.StartDate) > 1
Comments