CODEWITHSUNDEEP
sqlsql-serverpivotdynamic-sql
Jim Dover
Jim Dover

Reputation: 623

Dynamic SQL Rolling 12 Months Pivot

I am new to Dynamic SQL. I have the following table:

CustName Date Hours
First 01/01/2021 12
Second 01/01/2021 10
Second 05/02/2021 1
Second 10/11/2021 14

I am trying to make a rolling calendar Pivot for the last 12 months of summed hours.

Here is the code I have done:

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

    select @cols = STUFF(
    (SELECT distinct ',' + QUOTENAME(DATENAME(mm,Date) + ' of ' 
    + DATENAME(year,Date)) AS months_ago
          FROM [TimeEntryList]  
          WHERE Date > DATEADD(year, -1, 
    DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
,1,1,'')


 set @query = 'SELECT CustName, ' + @cols + ' 
 from ( select 
   [CustName], 
   datename(mm,[Date])+'' of ''+datename(year,[Date])AS 
   months_ago, [Hours] AS NetQty 
   from [TimeEntryList] 
    WHERE [Date] > DATEADD(year, -1, 
  DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
 ) as source 
 pivot 
 ( 
 sum(NetQty) For months_ago  in (' + @cols + ')
 ) as PivotTable'

execute sp_executesql @query;

This works 'kind of' how I want it, but the columns are not ordered from current date backwards.

I'm wanting it to start at the current month backwards. So if this month is August it would be:

CustName Sep 2020 .... months August 2021
First 12 14
Second 3 12

Is there a good way to start by current month in Dynamic SQL into a pivot and go back 12 months?

I tried 'order by months_ago desc' etc, but that doesn't work

Upvotes: 0

Views: 958

Answers (1)

Charlieface
Charlieface

Reputation: 71638

You simply need to add a WHERE filter. This needs to be in both parts of the query.

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

SET @cols = STUFF(
    (SELECT ',' + QUOTENAME(DATENAME(mm, EOMONTH(Date)) + ' of ' + DATENAME(year, EOMONTH(Date))) AS months_ago
    FROM [TimeEntryList]  
    WHERE [Date] > DATEADD(year, -1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
    GROUP BY EOMONTH(Date)
    ORDER BY EOMONTH(Date)
    FOR XML PATH(''), TYPE
    ).value('text()[1]', 'NVARCHAR(MAX)') 
,1,1,'');

SET @query = '
SELECT
  CustName,
  ' + @cols + ' 
from (
  select 
    [CustName], 
    datename(mm,[Date])+'' of ''+datename(year,[Date])AS months_ago, [Hours] AS 
    NetQty 
   from [TimeEntryList]
   WHERE [Date] > DATEADD(year, -1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
 ) as source 
pivot 
  ( 
   sum(NetQty) For months_ago  in (' + @cols + ')
  ) as PivotTable;
';

execute sp_executesql @query;

You could even pass through a starting date parameter, like this:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @dateFrom datetime;

SET @dateFrom = DATEADD(year, -1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1));

SET @cols = STUFF(
    (SELECT ',' + QUOTENAME(DATENAME(mm,EOMONTH(Date)) + ' of ' + DATENAME(year,EOMONTH(Date)) AS months_ago
    FROM [TimeEntryList]  
    WHERE [Date] > @dateFrom
    GROUP BY EOMONTH(Date)
    ORDER BY EOMONTH(Date)
    FOR XML PATH(''), TYPE
    ).value('text()[1]', 'NVARCHAR(MAX)') 
,1,1,'');

SET @query = '
SELECT
  CustName,
  ' + @cols + ' 
from (
  select 
    [CustName], 
    datename(mm,[Date])+'' of ''+datename(year,[Date])AS months_ago, [Hours] AS 
    NetQty 
   from [TimeEntryList]
   WHERE [Date] > @dateFrom
 ) as source 
pivot 
  ( 
   sum(NetQty) For months_ago  in (' + @cols + ')
  ) as PivotTable;
';

execute sp_executesql
    @query
    N'@dateFrom datetime',
    @dateFrom = @dateFrom;

Upvotes: 3

Related Questions