2 added 2 characters in body
source | link
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'AAL102'N'AAL103', 'Burrito', 56 , DATEADD(WEEK, -9, GETDATE()))
, (10, N'AAL103', 'Pizza''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
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'AAL102', 'Burrito', 56 , DATEADD(WEEK, -9, GETDATE()))
, (10, N'AAL103', 'Pizza', 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
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
1
source | link

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'AAL102', 'Burrito', 56 , DATEADD(WEEK, -9, GETDATE()))
, (10, N'AAL103', 'Pizza', 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