CODEWITHSUNDEEP
sql-serversql-server-2005t-sql
Graeme
Graeme

Reputation: 2667

SQL to return list of years since a specific year

I need a list of years as a recordset starting with 2004 to current year (in desc order), without writing a stored procedure. Is this possible? (SQL Server 2005). So it should return:

2009
2008
2007
2006
2005
2004

Upvotes: 33

Views: 73424

Answers (11)

Andres Martinez
Andres Martinez

Reputation: 244

Recursive CTE with no variables is a simpler way to go...

WITH Years AS (
    SELECT DATEPART(YEAR, GETDATE()) AS Year
    UNION ALL
    Select Year - 1 FROM Years
    WHERE Year > 2004
)
SELECT Year FROM Years
ORDER BY Year DESC

Upvotes: 0

Useless_Wizard
Useless_Wizard

Reputation: 191

My two cents:

The CTE is the best answer but I like @Lieven Keersmaekers answer as it doesn’t rely on recursion/loops or creating additional objects such as functions/date-tables. The key variation is mine simply uses the Top clause with an expression, instead of the top 100 (basically the whole table) I ask for N number of unordered rows first. This is quicker, if your base table happens to be quite large.

Tested on SQL Server 2016

DECLARE @startYear smallint;
SET @startYear = 2004;

DECLARE @endYear smallint;
SET @endYear = YEAR(GETDATE());

-- Top uses expression to bring in N number of rows. The (@startYear - 1) is to retain the start year
SELECT [Yr]
FROM (
    SELECT TOP (@endYear - (@startYear - 1)) 
          COUNT([object_id]) OVER (ORDER BY [object_id] DESC) + (@startYear - 1) AS [Yr] -- Add initial start year to Count
    FROM sys.all_objects
) AS T1
ORDER BY [Yr] DESC

The comments above are valid, but Since OP’s requirements is literally a small set of values from 2004, the sys.all_objects will suffice, as SQL Server install comes with a ton of system objects.

Hope that's a different take.

Upvotes: 1

sunny
sunny

Reputation: 21

WITH n(n) AS
(
    SELECT 0
    UNION ALL
    SELECT n+1 FROM n WHERE n < 10
)
SELECT year(DATEADD( YY, -n, GetDate())) 
FROM n ORDER BY n

Upvotes: 2

user3852207
user3852207

Reputation: 1

This is a simple query, check this 

(SELECT REPLACE((TO_CHAR(SYSDATE,'YYYY')-Rownum)+1,' ',NULL) yr FROM dual CONNECT BY LEVEL < 32) year

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

Using ROW_NUMBER on any column from any large enough (stable) table would be one way to do it. 

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

Note that <AnyTable> should contain at least the amount of rows equal to the amount of years you require.

Edit (Cudo's to Joshua)

  • Preferably, you'd select a table wich you know will not get truncated and/or deleted. A large enough system table should come to mind.
  • At present, being a lot older and wiser (older at least), I would implement this requirement using a CTE like mentioned in the answer given by Joshua. The CTE technique is far superior and less error prone than current given ROW_NUMBER solution.

Upvotes: 5

Joshua Carmody
Joshua Carmody

Reputation: 13740

This gets all years from 2004 to the present, using a recursive CTE:

with yearlist as 
(
    select 2004 as year
    union all
    select yl.year + 1 as year
    from yearlist yl
    where yl.year + 1 <= YEAR(GetDate())
)

select year from yearlist order by year desc;

Upvotes: 61

Eppz
Eppz

Reputation: 3226

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

2009
2008
2007
2006
2005
2004

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

2010
2009
2008
2007
2006
2005
2004

Upvotes: 2

DJ.
DJ.

Reputation: 16257

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

Upvotes: 6

scottm
scottm

Reputation: 28699

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

Upvotes: 0

Maksym Gontar
Maksym Gontar

Reputation: 22775

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

Upvotes: 3

Andrew Clark
Andrew Clark

Reputation: 1403

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.

Upvotes: 0

Related Questions