SSRS pivot dynamic columns

Question

I've tried to port this from a similar post, Return a column per date in a range however I haven't been able to quite get it to do what I need... Please find some example code below :

What i'm after is summing the QTYOrdered between two dates looking back over a 6 week period. I'm not quite able to get this to work and need help! ;) I'm sure @Bluefeet would be able to sort this out in a min!

    DROP TABLE #Sales
DROP TABLE #TempDates

CREATE TABLE #Sales
    (
      ID INT ,
      ItemID NVARCHAR(6) ,
      [Description] NVARCHAR(10) ,
      QTYOrdered NUMERIC ,
      ReceiptDate DATETIME
    )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 1 , -- ID - int
          N'AAL100' , -- ItemID - nvarchar(6) ,
          'Ice Cream' , -- Description -- nvarchar(10) ,
          21 , -- QTYOrdered - numeric
          DATEADD(WEEK, -1, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 2 , -- ID - int
          N'AAL100' , -- ItemID - nvarchar(6)
          'Ice Cream' , -- Description -- nvarchar(10) ,
          40 , -- QTYOrdered - numeric
          DATEADD(WEEK, -1, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 3 , -- ID - int
          N'AAL100' , -- ItemID - nvarchar(6)
          'Ice Cream' , -- Description -- nvarchar(10) ,
          50 , -- QTYOrdered - numeric
          DATEADD(WEEK, -3, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 4 , -- ID - int
          N'AAL101' , -- ItemID - nvarchar(6)
          'Burger' , -- Description -- nvarchar(10) ,
          43 , -- QTYOrdered - numeric
          DATEADD(WEEK, -3, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 5 , -- ID - int
          N'AAL101' , -- ItemID - nvarchar(6)
          'Burger' , -- Description -- nvarchar(10) ,
          42 , -- QTYOrdered - numeric
          DATEADD(WEEK, -5, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 6 , -- ID - int
          N'AAL101' , -- ItemID - nvarchar(6)
          'Burger' , -- Description -- nvarchar(10) ,
          76 , -- QTYOrdered - numeric
          DATEADD(WEEK, -5, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 7 , -- ID - int
          N'AAL102' , -- ItemID - nvarchar(6)
          'Pizza' , -- Description -- nvarchar(10) ,
          65 , -- QTYOrdered - numeric
          DATEADD(WEEK, -7, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )  
VALUES  ( 8 , -- ID - int
          N'AAL102' , -- ItemID - nvarchar(6)
          'Pizza' , -- Description -- nvarchar(10) ,
          34 , -- QTYOrdered - numeric
          DATEADD(WEEK, -7, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,  
          QTYOrdered ,
          ReceiptDate
        )
VALUES  ( 9 , -- ID - int
          N'AAL102' , -- ItemID - nvarchar(6)
          'Pizza' , -- Description -- nvarchar(10) ,
          56 , -- QTYOrdered - numeric
          DATEADD(WEEK, -9, GETDATE())  -- ReceiptDate - datetime
        )

INSERT  INTO #Sales
        ( ID ,
          ItemID ,
          [Description] ,
          QTYOrdered ,
          ReceiptDate
        )  
VALUES  ( 10 , -- ID - int
          N'AAL103' , -- ItemID - nvarchar(6)
          'Burrito' , -- Description -- nvarchar(10) ,
          75 , -- QTYOrdered - numeric
          DATEADD(WEEK, -9, GETDATE())  -- ReceiptDate - datetime
        )
SELECT  *
FROM    #Sales

DECLARE @Cols AS NVARCHAR(MAX) ,
    @Query AS NVARCHAR(MAX);
WITH    CTE ( DateList, MaxDate )
          AS ( SELECT   DATEADD(WEEK, -6, CONVERT(DATE, GETDATE())) AS DateList ,
                        MAX(ReceiptDate) MaxDate
               FROM     #Sales
               UNION ALL
               SELECT   DATEADD(WEEK, 1, DateList) ,
                        MaxDate
               FROM     CTE
               WHERE    CTE.DateList < CTE.MaxDate
             )
    SELECT  C.DateList
    INTO    #TempDates
    FROM    CTE C 

SELECT  @Cols = STUFF((SELECT DISTINCT 
                                ',' + QUOTENAME(CONVERT(CHAR(10), DateList, 120))
                       FROM     #TempDates
        FOR           XML PATH('') ,
                          TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @Query = 'SELECT 
        ITEMID ,
        [Description] ,
        ' + @Cols
    + ' FROM 
             ( SELECT   
                        ITEMID ,
                        [Description] ,
                        QTYOrdered ,
                        CONVERT(CHAR(10), DateList, 120) PivotDate
                FROM    #TempDates D
                        INNER JOIN #Sales S ON ReceiptDate BETWEEN DateList and DATEADD(DAY, 6, DateList)
        ) X 
             PIVOT 
            (
                SUM(QTYOrdered)
                FOR PivotDate in (' + @Cols + ') 
            ) P ' 
PRINT @Query 
EXECUTE(@query) 

Example desired output :

ID  ITEMID  Description 2014-09-15  2014-09-22  2014-09-29  2014-10-06  2014-10-13  2014-10-20

1   AAL100  Ice Cream   0   0   0   0   50  0
2   AAL101  Burger      0   0   0   118 43  0
3   AAL102  Pizza       0   56  34  0   99  0
4   AAL103  Burrito     0   75  0   0   0   0

Excuse formatting... I don't know how to format text in the little editor window...

@bluefeet your fiddle seems close however when I run against my actual data i'm getting multiple lines and some of the summed qty's don't seem to be correct...

Answer 1

As suggested in my comment I would not PIVOT the data within the SQL and let SSRS do the PIVOTING by using a matrix. Why? Because your report will not be flexible should you choose to allow users to specify their own number of periods.

This kind of report is much easier with a Date dimension table so you can easily figure out the week start/end date between a set of dates, this is what happens in the first part of the solution but if you had a Date table permanently stored it would be as case of simply querying that table.

The CTEs named N1 through to N5 are creating a tally table, a table full of numbers, which I use to generate the CTE DateAndWeek which contains a list of dates and the week start date, based on the @@DATEFIRST value.

WITH N1 AS
(
    SELECT 1 AS N
    UNION ALL
    SELECT 1 AS N
)
, N2 AS
(
    SELECT A.N FROM N1 AS A
    CROSS JOIN N1 AS B
)
, N3 AS
(
    SELECT A.N FROM N2 AS A
    CROSS JOIN N2 AS B
)
, N4 AS
(
    SELECT A.N FROM N3 AS A
    CROSS JOIN N3 AS B
)
, N5 AS
(
    SELECT A.N FROM N4 AS A
    CROSS JOIN N4 AS B
)
, DateAndWeek AS
(
    SELECT TOP 365
        DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101') AS [Date]
        , DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
            + (1-DATEPART(WEEKDAY, DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101'))), '20140101') AS WeekStartDate
    FROM
        N5
)

Depending on your requirements you may or may not need to create dummy/blank rows for items which have no sales during the period and this is what happens in the ReportTemplate CTE. It returns a dataset with all the weeks within the period specified and performs a 'CROSS JOIN' against all unique ItemID and Description values.

, ReportTemplate AS
(
    SELECT DISTINCT
        WeekStartDate
        , ItemId
        , Description

    FROM
        DateAndWeek
        CROSS JOIN
        (
            SELECT DISTINCT
                ItemID
                , Description

            FROM
                #Sales
        ) AS Sales

    WHERE
        [Date] BETWEEN @StartDate AND @EndDate
)

The final CTE perform the AGGREGATION of your sale data by JOINING to the DateAndWeek CTE to figure out which week the sale belongs in.

, SalesInPeriod AS
(
    SELECT
        ItemID
        , WeekStartDate
        , SUM(QTYOrdered) AS Total

    FROM
        #Sales
        JOIN DateAndWeek
            ON  CAST(#Sales.ReceiptDate AS DATE) = DateAndWeek.Date

    WHERE
        DateAndWeek.Date BETWEEN @StartDate AND @EndDate

    GROUP BY
        ItemID
        , WeekStartDate
)

Finally, LEFT JOIN the ReportTemplate and SalesInPeriod CTEs to get the result set for your report.

Here is the full solution

IF OBJECT_ID('tempdb..#Sales', 'U') IS NOT NULL
    DROP TABLE #Sales
GO

DECLARE
    @StartDate      DATE = DATEADD(WEEK, -6, CAST(GETDATE() AS DATE))
    , @EndDate      DATE = CAST(GETDATE() AS DATE)


CREATE TABLE #Sales
(
    ID              INT
    , ItemID        NVARCHAR(6)
    , [Description] NVARCHAR(10)
    , QTYOrdered    NUMERIC
    , ReceiptDate   DATETIME
)

INSERT INTO #Sales
VALUES 
(1, N'AAL100', 'Ice Cream', 21 , DATEADD(WEEK, -1, GETDATE()))
, (2, N'AAL100', 'Ice Cream', 40 , DATEADD(WEEK, -1, GETDATE()))
, (3, N'AAL100', 'Ice Cream', 50 , DATEADD(WEEK, -3, GETDATE()))
, (4, N'AAL101', 'Burger', 43 , DATEADD(WEEK, -3, GETDATE()))
, (5, N'AAL101', 'Burger', 42 , DATEADD(WEEK, -5, GETDATE()))
, (6, N'AAL101', 'Burger', 76 , DATEADD(WEEK, -5, GETDATE()))
, (7, N'AAL102', 'Pizza', 76 , DATEADD(WEEK, -7, GETDATE()))
, (8, N'AAL102', 'Pizza', 34 , DATEADD(WEEK, -7, GETDATE()))
, (9, N'AAL103', 'Burrito', 56 , DATEADD(WEEK, -9, GETDATE()))
, (10, N'AAL103', 'Burrito', 75 , DATEADD(WEEK, -9, GETDATE()))
;

WITH N1 AS
(
    SELECT 1 AS N
    UNION ALL
    SELECT 1 AS N
)
, N2 AS
(
    SELECT A.N FROM N1 AS A
    CROSS JOIN N1 AS B
)
, N3 AS
(
    SELECT A.N FROM N2 AS A
    CROSS JOIN N2 AS B
)
, N4 AS
(
    SELECT A.N FROM N3 AS A
    CROSS JOIN N3 AS B
)
, N5 AS
(
    SELECT A.N FROM N4 AS A
    CROSS JOIN N4 AS B
)
, DateAndWeek AS
(
    SELECT TOP 365
        DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101') AS [Date]
        , DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
            + (1-DATEPART(WEEKDAY, DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101'))), '20140101') AS WeekStartDate
    FROM
        N5
)
, ReportTemplate AS
(
    SELECT DISTINCT
        WeekStartDate
        , ItemId
        , Description

    FROM
        DateAndWeek
        CROSS JOIN
        (
            SELECT DISTINCT
                ItemID
                , Description

            FROM
                #Sales
        ) AS Sales

    WHERE
        [Date] BETWEEN @StartDate AND @EndDate
)
, SalesInPeriod AS
(
    SELECT
        ItemID
        , WeekStartDate
        , SUM(QTYOrdered) AS Total

    FROM
        #Sales
        JOIN DateAndWeek
            ON  CAST(#Sales.ReceiptDate AS DATE) = DateAndWeek.Date

    WHERE
        DateAndWeek.Date BETWEEN @StartDate AND @EndDate

    GROUP BY
        ItemID
        , WeekStartDate
)

SELECT
    ReportTemplate.WeekStartDate
    , ReportTemplate.ItemID
    , ReportTemplate.Description
    , SalesInPeriod.Total

FROM
    ReportTemplate
    LEFT JOIN SalesInPeriod
        ON  ReportTemplate.ItemID = SalesInPeriod.ItemID
            AND ReportTemplate.WeekStartDate = SalesInPeriod.WeekStartDate