How to create Temp Table with month columns based on date parameters?

Question

I have a stored procedure that accepts two Dates. In my stored procedure, I need to create a temp table with the months in between the two dates as columns.

For example,

If the user passes in

1/1/2016 , 8/1/2016

I need a temp table with the columns:

January February March April May June July August

How could I create this type of temp table with columns created in this manner? With the columns being based on the two dates passed in?

Possible duplicate of [Months between two dates](http://stackoverflow.com/questions/7885851/months-between-two-dates) — Tab Alleman, Jan 06 '16 at 21:01
@TabAlleman This is not the answer for the issue i'm trying to figure out — Reeggiie, Jan 06 '16 at 21:08
@tab The difference is that he needs to create a temp table based off the parameters, not just output the month names — Mike, Jan 06 '16 at 21:14
Oh I see, you need columns, not rows. Ok, then you need to do this with Dynamic SQL. You can use the answer in the other question to build your dynamic sql string. — Tab Alleman, Jan 06 '16 at 21:14
@Reeggiie: what do you need in case when dates are from different years? Suppose 1/1/2010, 8/1/2016? — Alex Yu, Jan 07 '16 at 02:35

score 0 · Answer 1 · answered Jan 06 '16 at 22:48

The following script should get you started (and almost there):

declare @start_date DATE = '20160101' 
declare @end_date DATE = '20160801'

;WITH CTE AS
(
    SELECT @start_date AS cte_start_date, DATENAME(month, @start_date) AS Name,
        CAST(' ALTER TABLE #myTemp ADD ' + DATENAME(month, @start_date) + ' INT ' + CHAR(13) + CHAR(10) AS VARCHAR(8000)) AS SqlStr
    UNION ALL
    SELECT DATEADD(MONTH, 1, cte_start_date), DATENAME(month, DATEADD(MONTH, 1, cte_start_date)) AS Name, 
        CAST(SqlStr + ' ALTER TABLE #myTemp ADD ' + DATENAME(month, DATEADD(MONTH, 1, cte_start_date)) + ' INT ' + CHAR(13) + CHAR(10) AS VARCHAR(8000))
    FROM CTE
    WHERE DATEADD(MONTH, 1, cte_start_date) <= @end_date   
)
SELECT cte_start_date, Name, SqlStr 
FROM CTE

Using a recursive-CTE it generates a loop between start and end date, and for each month it computes its string representation and also creates a alter script to add the columns to a temporary table.

The CTE computes the SQL script gradually, so that the final script is on the last line.

score 0 · Answer 2 · answered Jan 07 '16 at 11:54

Try This ....

declare @start_date DATE = '20160101' 
declare @end_date DATE = '20160801'

;WITH CTE AS
(
    SELECT @start_date AS cte_start_date, DATENAME(month, @start_date) AS NAME , 0 AS Coun
    UNION ALL
    SELECT DATEADD(MONTH, 1, cte_start_date), DATENAME(month, DATEADD(MONTH, 1, cte_start_date)) AS NAME , 0 AS Coun
    FROM CTE
    WHERE DATEADD(MONTH, 1, cte_start_date) <= @end_date   
)

SELECT  Coun,Name
INTO #tmp1
FROM CTE

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + Name 
                 from #tmp1

        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT ' + @cols + ' from 
         (
            select Coun,Name
            from #tmp1
        ) x
        pivot 
        (
            MAX(Coun)
            for Name in (' + @cols + ')
        ) p '

execute(@query);
DROP TABLE #tmp1

It Will Return OutPut Like your expected output .......

How to create Temp Table with month columns based on date parameters?

2 Answers2