CODEWITHSUNDEEP
sql-servert-sql
FastTrack
FastTrack

Reputation: 8980

Get result set of 10 years before and 10 years after current year

What I want: I would like a result set that shows me the 10 years prior to and the 10 years after the current year.

Example: Being that this year is 2014:

2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024

What I've tried: I'm not a huge SQL guy so bear with me here:

SELECT 
YEAR(DATEADD(year, -10, GETDATE())) AS prevYear,
YEAR(DATEADD(year, 10, GETDATE())) AS nextYear,
YEAR(GETDATE()) AS currentYear
WHERE currentYear BETWEEN prevYear AND nextYear

Can someone help me out? Am I even close??

Upvotes: 1

Views: 370

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280645

SELECT TOP (21) YEAR(DATEADD(YEAR, number-10, GETDATE())) 
FROM master.dbo.spt_values WHERE type = N'P' ORDER BY number;

Chances are you want to do something else with this data. One example might be to aggregate the data from some table, and include years in this range that aren't found in the table. Here's how you might do that:

;WITH y(d) AS 
(
  SELECT TOP (21) DATEADD(YEAR, number-10, DATEADD(YEAR, YEAR(GETDATE())-1900,0))
  FROM master.dbo.spt_values WHERE type = N'P' ORDER BY number
)
SELECT y.d, COUNT(o.key)
  FROM y 
  LEFT OUTER JOIN dbo.other_table AS o
  ON o.datetime_column >= d.d
  AND o.datetime_column < DATEADD(YEAR, 1, d.d)
GROUP BY y.d
ORDER BY y.d;

Upvotes: 6

Kaf
Kaf

Reputation: 33839

Assuming you need to get these records from an existing table:

declare @thisYear int =  year(getdate())

select yourYear
from table1
where yourYear between @thisYear -10 and @thisYear + 10 
      and yourYear <> @thisYear --Assuming you don't need the current year

SQL fiddle demo

Upvotes: 2

Related Questions