ajc101 ajc101 - 7 months ago 20
SQL Question

List all dates between range

I've seen this asked before, but couldn't get an answer that worked. I have 2 columns- startyear and endyear. I want to create a list of all years between the start and end year. So if the row has a start year of 2010 and an end year of 2016, I want the list to show 2010, 2011, 2012, etc. Do I have to somehow grab the oldest start year and newest end year throughout all rows?

SELECT StartYear, EndYear
FROM TestYear


You can do this with a recursive CTE:

;With MaxMin As
    Select  Min(StartYear)  As First,
            Max(EndYear)    As Last
    From    TestYear
), Years (Year) As
    Select  First From MaxMin Union All
    Select  Year + 1 
    From    Years
    Where   Year < (Select Last From MaxMin)
Select  *
From    Years