Menu
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
789
It simply uses the Top clause. DECLARE @startYear smallint; SET @startYear = YEAR ('2006-12-25'); DECLARE @endYear smallint; SET @endYear = YEAR('2013-11-14'); -- Top uses expression to bring in N number of rows.
The EXTRACT() function returns a number which represents the year of the date. The EXTRACT() function is a SQL standard function supported by MySQL, Oracle, PostgreSQL, and Firebird. If you use SQL Server, you can use the YEAR() or DATEPART() function to extract the year from a date.
SQL Fiddle. The trick is to - per contact - subtract the running count ( row_number() ) from each year. Consecutive rows produce the same group number ( grp ). the number itself has no meaning, it just identifies groups per contact . Then count, sort, get the maximum count.
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; 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
