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

Popular posts from this blog

Windows Azure Package Build Error: The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.

Resource ID : 1. The request limit for the database is 180 and has been reached.

How to get Client's Location using IPAddress