ansaurus

Question

SQL to return list of years since a specific year

Answer 1

A:

Try this:

    declare @lowyear int
set @lowyear = 2004
declare @thisyear int
set @thisyear = year(getdate())
while @thisyear >= @lowyear
begin
print @thisyear
set @thisyear = (@thisyear - 1)
end

Returns

When you hit Jan 1, 2010. The same code will return:

Eppz 2009-03-09 15:57:40

This only works if there is a date in the table for each year. If there are no entries for 2006, say, then 2006 will not appear.

Jonathan Leffler 2009-03-09 16:00:40

@Jonathan, This code is independent of a table. This code prints "a list of years as a recordset starting with 2004 to current year (in desc order), without writing a stored procedure". While I believe that printing the values was the wrong choice, my solution does the same an returns the result set

scottm 2009-03-09 19:38:05

This would work, but I like Lievens answer above as it is one select statement and uses some SQL 2005 stuff I needed to learn :-)

Graeme 2009-03-10 07:13:36

Answer 2

A:

I think you need to create a dates table, then just select your range from it. It can also come in useful when you need to select a date range with X data attached and not have any missed days.

Andrew Clark 2009-03-09 15:58:34

How do you propose keeping this dates table up-to-date? Is your application going to crash on January 1 several years down the road because someone forgot to populate it with next year's dates?

bcwood 2009-07-22 17:18:15

Answer 3

+2 A:

Replace AnyTable with a table with at least the amount of rows as the amount of years you're asking. Replace AnyColumn with a column from the table.

SELECT *
FROM (
  SELECT TOP 100 2003 + ROW_NUMBER() OVER (ORDER BY <AnyColumn>) AS Yr
  FROM dbo.<AnyTable>
  ) Years
WHERE Yr <= YEAR(GETDATE())

Lieven 2009-03-09 16:01:46

That works - presumably I should choose a table with only a handful of rows, or does it make no difference?

Graeme 2009-03-09 16:04:51

@Graeme, by doing a TOP 100 it will make little difference wich table you choose but I'd use a small one.

Lieven 2009-03-09 16:09:36

You have to create a table, add the current year - 2004 number of rows, then run this query? What happens next year?

scottm 2009-03-09 16:10:32

@scotty2012. No table gets created. Next year gets included by the YEAR(GETDATE()) statement. The only thing fixed is the starting year. There are no 2004 rows, only 100 as specified in the TOP 100 clause. This query will work up until 2103

Lieven 2009-03-09 16:19:48

What happens when the table you're selecting from only has 2 rows in it?

Eppz 2009-03-09 18:08:52

Only two years will be returned.

Lieven 2009-03-09 20:28:11

So if only 2 rows would be returned, then this would not give the expected results.

Eppz 2009-03-10 13:28:35

@Eppz, I explained in my response that AnyTable should be replaced with a table with "at least" the amount of rows as the amount of years you're asking.

Lieven 2009-03-10 13:35:39

You need to add ORDER BY 1 desc to get the years in the correct order.

Eppz 2009-03-10 13:38:42

@Eppz. True, I left that as an excercise to the reader ;) (read as: I honestly forgot).

Lieven 2009-03-10 13:40:24

Answer 4

+3 A:

Updated to return current year plus previous 5 years. Should be very fast as this is a small recordset.

SELECT YEAR(GETDATE()) as YearNum
UNION
SELECT YEAR(GETDATE()) - 1 as YearNum
UNION
SELECT YEAR(GETDATE()) - 2 as YearNum
UNION
SELECT YEAR(GETDATE()) - 3 as YearNum
UNION
SELECT YEAR(GETDATE()) - 4 as YearNum
UNION
SELECT YEAR(GETDATE()) - 5 as YearNum
ORDER BY YearNum DESC

DJ 2009-03-09 16:03:36

Nathan Koop 2009-03-09 16:07:55

heh - missed the current year comment but the performance impact of something this small would be negligible

DJ 2009-03-09 16:13:53

@DJ sorry, I totally misread the intended query, I for some reason thought this was going to be joined with an order table or something. My mistake. Your query is perfectly fine.

Nathan Koop 2009-03-11 16:14:29

Answer 5

A:

DECLARE @YEARS TABLE (Y INT)
DECLARE @I INT, @NY INT
SELECT @I = 2004, @NY = YEAR(GETDATE())
WHILE @I <= @NY BEGIN
    INSERT @YEARS SELECT @I
    SET @I = @I + 1
END
SELECT Y 
FROM @YEARS
ORDER BY Y DESC

Max Gontar 2009-03-09 16:03:40

Answer 6

A:

Any particular reason a table-valued UDF is unacceptable? Do something like this otherwise.

Pontus Gagge 2009-03-09 16:05:01

I am not allowed to create database objects, so only select statement alowed.

Graeme 2009-03-10 07:10:59

Answer 7

A:

SET NOCOUNT ON
DECLARE @max int
set @max = DATEPART(year, getdate())

CREATE TABLE #temp (val int)

while @max >= 2004
  BEGIN
    insert #temp(val) values(@max)
    set @max = @max - 1
  END

SELECT * from #temp

scottm 2009-03-09 16:05:45

ansaurus

tags:

views:

answers:

SQL to return list of years since a specific year

related questions