Блог

Я новичок в динамическом SQL. У меня есть следующая таблица:

Я пытаюсь составить сводный сводный календарь за последние 12 месяцев с суммированными часами.

Вот код, который я сделал:

 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;
 

Это работает «вроде» так, как я хочу, но столбцы не упорядочены с текущей даты в обратном порядке.

Я хочу, чтобы это началось в текущем месяце задом наперед. Так что если в этом месяце август, то это будет:

Есть ли хороший способ начать с текущего месяца в динамическом SQL в сводной таблице и вернуться на 12 месяцев назад?

Я попробовал «заказать по месяцам» и т. Д., Но это не работает

Вам просто нужно добавить WHERE фильтр. это должно быть в обеих частях запроса.

 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;
 

Вы даже можете пройти через параметр даты начала, как это:

 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;