Dynamic month year columns per date range

Question

I need a select statement for a stored procedure with @startDate and @endDate parameters. It needs to return a dynamic amount of columns that are labeled in a month year ("August 2011") format based on the date range parameters.

For example, if @startDate = 1/1/2011 and @endDate = 3/1/2011

the result set would look something like:

column headers ---->  January 2011 | February 2011 | March 2011
rows with data ---->       123     |      3456     |    793

a function taking the datetime corresponding to the column header will be used for rows of data

Is this possible? Use pivot? Thanks in advance for all of your responses!

Are they always going to be in the same year and/or starting from January, or might they traverse calendar years? And will the date provided always be the 1st day of the month and not contain any time? What version of SQL Server? — Aaron Bertrand
The date range might traverse calendar years. The parameters are DATETIME not containing any time. The day of month could be any valid day, not just the first. SQL Server 8.0.2039 is SQL Server 2000 — kenalacom
Will the count of columns always be the same, or is it dynamic? — Clockwork-Muse
The number of columns are dynamic based on how many months are in the date range. — kenalacom

Aaron Bertrand Aaron Bertrand · Accepted Answer · 2011-08-24T20:53:52

Assuming you don't need to touch any tables (all data comes from the function). Note that the limit on a local NVARCHAR variable in SQL Server 2000 is 4,000 characters, so you'll need to be careful about how long your range can be.

DECLARE 
    @startDate SMALLDATETIME, 
    @endDate   SMALLDATETIME;

SELECT 
    @startDate = '20110101', 
    @endDate   = '20110301';


DECLARE 
    @i       INT,
    @sd      SMALLDATETIME,
    @sql     NVARCHAR(MAX),
    @column  VARCHAR(32),
    @columns NVARCHAR(4000);

SELECT @i = 0, @columns = N'';

WHILE @i <= DATEDIFF(MONTH, @startDate, @endDate)
BEGIN
    SET @sd = DATEDIFF(DAY, 0, DATEADD(MONTH, @i, @startDate));

    SET @column = DATENAME(MONTH, @sd) 
        + ' ' + CONVERT(VARCHAR(20), YEAR(@sd));

    SET @columns = @columns + ',' + CHAR(13) + CHAR(10) 
        + ' [' + @column + ']' + ' = dbo.FunctionName(''' + @column + ''')';

    SET @i = @i + 1;
END

SET @sql = 'SELECT ' + STUFF(@columns, 1, 1, '') + ';';

PRINT @sql;
--EXEC sp_executesql @sql;

In this case this yields:

SELECT 
 [January 2011] = dbo.FunctionName('January 2011'),
 [February 2011] = dbo.FunctionName('February 2011'),
 [March 2011] = dbo.FunctionName('March 2011');

Dynamic month year columns per date range

1 Answers