My Techy Memories

http://mocella.blogspot.com/2010/01/entity-framework-v4-object-graph.html

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