1

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...

enter image description here

  • Why do you need to PIVOT the data in your query? Can't you let SSRS do this by using a matrix? – mheptinstall Oct 27 '14 at 12:12
  • Hi i'm really just trying to get the query to work first... i'll worry about how SSRS is going to display it later... – jhowe Oct 27 '14 at 13:01
  • @jhowe Can you edit your post to include the desired result? It's not exactly clear what you want for the final product. – Taryn Oct 27 '14 at 14:49
  • hi @bluefeet! Yea sure i'll do that give me a few mins... basically I just want to sum orders in date 'buckets' sum orders between dates rolling back 6 weeks... – jhowe Oct 27 '14 at 15:02
  • 1
    @jhowe It seems like you could just use this demo -- sqlfiddle.com/#!3/8a382/3 – Taryn Oct 27 '14 at 15:33
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
  • Hi yes the problem is i'm deploying this code and someone else is formatting the report... they don't have the skills to do advanced things in SSRS, so I just want to make it easy for them. How do I ensure that there is no overlap in the date buckets i.e. start from midnight on the day to midnight at the end of the week? – jhowe Oct 27 '14 at 19:26
  • Performing a PIVOT in T-SQL is a lot more advanced than dragging and dropping a Matrix into SSRS. Plus your column names are going to keep changing if you use dynamic SQL so it won't work in your report, unless you use generic column names like Week1, Week2, Week3 etc. which will be meaningless in the report and require you to calculate the week dates again. In the code above I am performing a CAST on the DATETIME columns to a DATE data type which removes the time portion, so there would be no overlap. – mheptinstall Oct 27 '14 at 21:02
  • but i believe there is a way to set column headers = to column values by using expressions? I will always have a fixed amount of columns... I totally get you that I should be doing this in the presentation layer, but i'm not in control of creating the report, just the data... – jhowe Oct 28 '14 at 9:06
  • Yes you could use an expression to provide the column value if you were to use to a generic column name like Week1, Week2, Week3 etc. you would just need to ensure that the logic used in your query is the same as in your report. – mheptinstall Oct 28 '14 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.