SQL Rolling Date (Month) Columns in Pivot Table

Question

I am building a rolling 36 month sales history report.

What I currently have:

select 
"ItemTable"."ItemNum" 
, [1] MM01 
, [2] MM02 
, [3] MM03 
, ... 
from ( 
select 
"ItemTable"."ItemNum" 
, DateDiff(m, "SalesTable"."SalesDate", GetDate()) months_ago 
, "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" NetQty 
from your_table 
) as source 
pivot 
( 
sum(NetQty) For months_ago in ([1], [2], [3], ...) 
) as PivotTable 

Following is a pic comparing what I have (with the above code) and what I want:

How do I change this to get what I want?

Answer 1

It appears that you're using datediff to get the number of months since the sales date, but you could do a dynamic pivot on the sales date column instead. Here's the code:

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

select @cols = STUFF(
        (SELECT distinct ',['+datename(mm,SalesTable.SalesDate)+' of '+datename(year,SalesTable.SalesDate)+']' AS months_ago
              FROM your_table 
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')


set @query = 'SELECT ItemNum, ' + @cols + ' 
from ( select 
       ItemTable.ItemNum, 
       datename(mm,SalesTable.SalesDate)+'' of ''+datename(year,SalesTable.SalesDate)AS months_ago, SalesTable.SalesQty - SalesTable.ReturnsQty AS NetQty 
       from your_table 
) as source 
pivot 
( 
sum(NetQty) For months_ago  in (' + @cols + ')
) as PivotTable'

execute sp_executesql @query;