FBryant87 FBryant87 - 2 months ago 8
SQL Question

SQL - Select dates, omitting those after a gap appears

I have some ordered dates:

2013-01-01
2013-02-01
2014-01-01
2014-06-01
2016-01-01
2016-03-01


I want to take only those which exist before a gap of a year or more appears, so my output should be:

2013-01-01
2013-02-01
2014-01-01
2014-06-01


(jump between 2014-06-01 and 2016-01-01 is greater than 1 year)

Is there a simple elegant way to do this in SQL? Normally I would separate the data into 2 tables, assign row numbers with one table offset by one, and calculate the difference between each date, noting the row number where the first gap appears.

It's an issue I seem to run into often and would greatly benefit from a simple solution.

Answer

If you have Window Functions available to you

Declare @YourTable table (SomeDate Date)
Insert Into @YourTable values
('2013-01-01'),
('2013-02-01'),
('2014-01-01'),
('2014-06-01'),
('2016-01-01'),
('2016-03-01')


;with cteBase as (
    Select *,PrevYear=Lag(Year(SomeDate),1,Year(SomeDate)) over (Order By SomeDate)
     From  @YourTable
)
Select * from cteBase where Year(SomeDate)-PrevYear=0

Returns

SomeDate    PrevYear
2013-01-01  2013
2013-02-01  2013
2014-06-01  2014
2016-03-01  2016