SQL sum by weekly buckets

Question

This is similar to some other questions on here however not close enough that I have all the info to do it myself. I want to pivot a date range with ability to limit by year. I'm not sure how they want to limit the data at the moment, maybe a year previous to a year forward for now.

I want the start day of the week to be Monday and end to be Sunday. Any quantities to fall between these days to be summed for the week per reftype with the date displaying as that starting Monday.

I have data below.

+---------+---------+------------------+-------------------------+------------------------+
| Itemid  | RefType | name             | OriginalReqDate         | Qty                    |
+---------+---------+------------------+-------------------------+------------------------+
| B406227 | 8       | Purchase order   | 2016-03-04 00:00:00.000 | 2346.0000000000000000  |
+---------+---------+------------------+-------------------------+------------------------+
| B406227 | 12      | Production order | 2016-03-04 00:00:00.000 | -1295.4000000000000000 |
+---------+---------+------------------+-------------------------+------------------------+
| B406227 | 12      | Production order | 2016-03-07 00:00:00.000 | -3651.6000000000000000 |
+---------+---------+------------------+-------------------------+------------------------+
| B406227 | 8       | Purchase order   | 2016-03-11 00:00:00.000 | 4692.0000000000000000  |
+---------+---------+------------------+-------------------------+------------------------+
| B406227 | 12      | Production order | 2016-03-14 00:00:00.000 | -1397.4000000000000000 |
+---------+---------+------------------+-------------------------+------------------------+
| B406227 | 12      | Production order | 2016-03-21 00:00:00.000 | -958.8000000000000000  |
+---------+---------+------------------+-------------------------+------------------------+
| B406227 | 45      | Formula line     | 2016-03-28 00:00:00.000 | -696.1700000000000000  |
+---------+---------+------------------+-------------------------+------------------------+
| B406227 | 45      | Formula line     | 2016-04-03 00:00:00.000 | -527.5500000000000000  |
+---------+---------+------------------+-------------------------+------------------------+
| B406227 | 8       | Purchase order   | 2016-04-07 00:00:00.000 | 7038.0000000000000000  |
+---------+---------+------------------+-------------------------+------------------------+
| B406227 | 45      | Formula line     | 2016-04-07 00:00:00.000 | -1186.5500000000000000 |
+---------+---------+------------------+-------------------------+------------------------+

I would like output as

+---------+---------+------------------------+------------------------+------------------------+------------------------+-----------------------+-----------------------+
| ItemId  | RefType | Name                   | 2016-03-04             | 2016-03-11             | 2016-03-18             | 2016-03-25            | 2016-04-01            |
+---------+---------+------------------------+------------------------+------------------------+------------------------+-----------------------+-----------------------+
| B406227 | 1       | On-hand                | 470.7600000000000000   | NULL                   | NULL                   | NULL                  | NULL                  |
+---------+---------+------------------------+------------------------+------------------------+------------------------+-----------------------+-----------------------+
| B406227 | 8       | Purchase order         | 2346.0000000000000000  | 4692.0000000000000000  | NULL                   | NULL                  | NULL                  |
+---------+---------+------------------------+------------------------+------------------------+------------------------+-----------------------+-----------------------+
| B406227 | 12      | Production order       | -1295.4000000000000000 | -3651.6000000000000000 | -1397.4000000000000000 | -958.8000000000000000 | NULL                  |
+---------+---------+------------------------+------------------------+------------------------+------------------------+-----------------------+-----------------------+
| B406227 | 33      | Planned purchase order | NULL                   | NULL                   | NULL                   | NULL                  | NULL                  |
+---------+---------+------------------------+------------------------+------------------------+------------------------+-----------------------+-----------------------+
| B406227 | 45      | Formula line           | NULL                   | NULL                   | NULL                   | NULL                  | -696.1700000000000000 |
+---------+---------+------------------------+------------------------+------------------------+------------------------+-----------------------+-----------------------+
| B406227 | 99      | Total for B406227      | 1992.1200000000000000  | 2561.7600000000000000  | 1164.3600000000000000  | 205.5600000000000000  | -490.6100000000000000 |
+---------+---------+------------------------+------------------------+------------------------+------------------------+-----------------------+-----------------------+

This is my attempt:

IF OBJECT_ID('tt', 'U') IS NOT NULL
    DROP TABLE tt;

DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

SELECT  @Columns = COALESCE(@Columns + ',', '') + QUOTENAME(ReqDate)
FROM    ( SELECT DISTINCT
                    CAST(ReqDate AS DATE) AS ReqDate
          FROM      SourceTable
        ) AS A
ORDER BY A.ReqDate;

SET @SQL = 'WITH PivotData AS (SELECT DataAreaId, ItemId, RefType, Name, ReqDate, Qty
FROM SourceTable) 
SELECT DataAreaId, ItemId, RefType, Name ' + @Columns + ' 
INTO tt
FROM PivotData

PIVOT
(SUM(Qty)
FOR ReqDate IN (' + @Columns + ')

) AS PivotResult ORDER BY DataAreaId, ItemId, RefType';

EXEC (@SQL);

IF OBJECT_ID('adhoc.V_tt', 'V') IS NOT NULL
    DROP VIEW adhoc.V_tt;

    GO

CREATE VIEW adhoc.V_tt
AS
    ( SELECT    *
      FROM      tt 
    );

SQL sum by weekly buckets

2 Answers