1

We have a date field in one of our tables and i need to create a filter from this column. I need to get the min year and the max year and then populate the years between them. I can easily create a stored proc that gets the min and max and then create a loop in my script to achieve this, but wondered if there is a better way to do this in sql. I am using sql server 2000.

Richard Banks
  • 2,946
  • 5
  • 34
  • 71

2 Answers2

2

You can use a numbers/tally table. SQL, Auxiliary table of numbers

Here is a sample using master..spt_values.

select number as [Year] 
from master..spt_values
where number between 2008 and 2011
  and type = 'P'

Result:

number
------
2008
2009
2010
2011

Just replace the values in the between clause with your min and max year.

Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
0

Here's an example of how @Mikael's suggestion could be applied to your particular problem:

SELECT
  v.number AS Year
FROM master..spt_values v
  INNER JOIN (
    SELECT
      YEAR(MIN(Date)) AS MinYear,
      YEAR(MAX(Date)) AS MaxYear
    FROM atable
  ) ON v.number BETWEEN MinYear AND MaxYear
WHERE v.type = 'P'
Community
  • 1
  • 1
Andriy M
  • 76,112
  • 17
  • 94
  • 154