CODEWITHSUNDEEP
sqlsql-serverdatabase
ajc101
ajc101

Reputation: 49

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 startyear of 2010 and an endyear of 2016, I want the list to show 2010, 2011, 2012, etc.

Do I have to somehow grab the oldest startyear and newest endyear throughout all rows?

 SELECT StartYear, EndYear
 FROM TestYear

Upvotes: 1

Views: 72

Answers (4)

M.Ali
M.Ali

Reputation: 69574

Sample Data

Declare @t Table (StartYear INT , EndYear INT)
INSERT INTO @t Values (2010 , 2011), (2012 , 2016);

Query

WITH X AS (

Select MIN(StartYear) MinYear
      ,MAX(EndYear)   MaxYear
FROM @t
) 
Select TOP ((Select MaxYear FROM X) 
           - (Select MinYear FROM X) + 1)
    ROW_Number() Over (Order by (Select NULL)) 
    + (Select MinYear FROM X) -1 rn
FROM master..spt_values a 
         Cross Join master..spt_values b

Upvotes: 1

Siyual
Siyual

Reputation: 16917

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

Upvotes: 3

Missy
Missy

Reputation: 1368

Try something like this in a Stored Procedure.

SELECT * INTO #FileDates
FROM (SELECT distinct year(startyear) as year from filename ) 
SELECT * INTO #FileDates2
    FROM (SELECT distinct year(endyear) as year from filename ) 

select distinct year from #FileDate, #FileDate2

Also you might want to check out: http://www.sqlservercentral.com/stairway/119892/

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use a recursive cte to do this.

declare @startyear int = 2010; --or any other number or a select query
declare @endyear int = 2020; --or any other number or a select query
with years(yr) as 
(
 select @startyear
 union all
 select yr+1 from years where yr < @endyear
)
select * from years;

Upvotes: 1

Related Questions