Friday, January 16, 2009

Dynamic list of years in T-SQL

I have many SQL Server 2005 Reporting Services reports that include a year value as a parameter. Recently I was asked to make this more dynamic, allowing for a rolling list of years from which the user can select.

Here is the table function I created, which I think should only work in SQL Server 2005 and up because of the parametrized TOP value:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.udtf_GetDynamicYearList
(
    @StartYear INT -- The first year you want in the list
    , @NumberofYears INT -- The total number of years you want (from the Start Year)
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        TOP (@NumberofYears) *
    FROM
    (
        SELECT
            @StartYear + n1.num AS [Year]
        FROM
        (
            SELECT 0 AS num UNION ALL
            SELECT 1 UNION ALL
            SELECT 2 UNION ALL
            SELECT 3 UNION ALL
            SELECT 4 UNION ALL
            SELECT 5 UNION ALL
            SELECT 6 UNION ALL
            SELECT 7 UNION ALL
            SELECT 8 UNION ALL
            SELECT 9
        ) n1
    ) GenCalendar
    ORDER BY 1
)
GO
Simple! Now you can call it like so:
SELECT * FROM dbo.udtf_GetDynamicYearList(2006, 10)
If you want it to be more dynamic (say, to start from last year), you can do:
SELECT * FROM dbo.udtf_GetDynamicYearList(YEAR(GETDATE())-1, 10)

No comments:

Post a Comment