sql-server - 按周存储桶的 SQL 总和

Question

这与这里的其他一些问题类似，但不够接近，我没有自己做的所有信息。我想旋转一个能够按年份限制的日期范围。我不确定他们目前想如何限制数据，也许是一年前或一年后的数据。

我希望一周的开始日是星期一，结束日是星期日。这些天之间的任何数量都会根据引用类型对一周进行求和，日期显示为从星期一开始。

我有以下数据。

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

我想输出为

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

这是我的尝试:

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 
    );

Answer 1

编辑:动态 SQL

这样，您将获得动态指向星期日的列标题...注意:根据您的系统文化，您可能需要查看 SET DATEFIRST 和 @@DATEFIRST...

CREATE TABLE #TestTbl(Itemid VARCHAR(100),RefType INT,name VARCHAR(100),OriginalReqDate DATETIME,Qty DECIMAL(8,2));
INSERT INTO #TestTbl VALUES
 ('B406227',8,'Purchase order','2016-03-04T00:00:00.000',2346.0000000000000000)
,('B406227',12,'Production order','2016-03-04T00:00:00.000',-1295.4000000000000000)
,('B406227',12,'Production order','2016-03-07T00:00:00.000',-3651.6000000000000000)
,('B406227',8,'Purchase order','2016-03-11T00:00:00.000',4692.0000000000000000)
,('B406227',12,'Production order','2016-03-14T00:00:00.000',-1397.4000000000000000)
,('B406227',12,'Production order','2016-03-21T00:00:00.000',-958.8000000000000000)
,('B406227',45,'Formula line','2016-03-28T00:00:00.000',-696.1700000000000000)
,('B406227',45,'Formula line','2016-04-03T00:00:00.000',-527.5500000000000000)
,('B406227',8,'Purchase order','2016-04-07T00:00:00.000',7038.0000000000000000)
,('B406227',45,'Formula line','2016-04-07T00:00:00.000',-1186.5500000000000000);

DECLARE @colNames VARCHAR(MAX)=
STUFF
(
    (
        SELECT DISTINCT ',[' + CONVERT(VARCHAR(10),DATEADD(DAY,DATEPART(DW,OriginalReqDate) * (-1),OriginalReqDate),120) + ']'
        FROM #TestTbl
        FOR XML PATH('')
    ),1,1,''
);

DECLARE @cmd VARCHAR(MAX)=
'
SELECT p.*
FROM
(
    SELECT tt.Itemid
          ,tt.RefType
          ,tt.name
          ,SUM(Qty) AS SumQty
          ,CONVERT(VARCHAR(10),DATEADD(DAY,DATEPART(DW,OriginalReqDate) * (-1),OriginalReqDate),120) AS ColumName 
    FROM #TestTbl AS tt
    GROUP BY ItemId,RefType,name,CONVERT(VARCHAR(10),DATEADD(DAY,DATEPART(DW,OriginalReqDate) * (-1),OriginalReqDate),120)
) AS tbl
PIVOT
(
    SUM(SumQty) FOR ColumName IN(' +  @colNames + ')
) AS p
';

EXEC (@cmd);

DROP TABLE #TestTbl;

结果:

Itemid  RefType name             2016-02-28 2016-03-06  2016-03-13  2016-03-20  2016-03-27  2016-04-03
B406227 8       Purchase order   2346.00    4692.00     NULL        NULL        NULL         7038.00
B406227 12      Production order -1295.40   -3651.60    -1397.40    -958.80     NULL         NULL
B406227 45      Formula line      NULL      NULL        NULL        NULL       -1223.72     -1186.55

上一页

这是针对给定示例数据的硬编码方法。如果您希望列获得像 2016-03-04 这样的标题，您可能会考虑动态 SQL 或者创建 columnName (和 IN() 列表)以获得正确的输出。

CREATE TABLE #TestTbl(Itemid VARCHAR(100),RefType INT,name VARCHAR(100),OriginalReqDate DATETIME,Qty DECIMAL(8,2));
INSERT INTO #TestTbl VALUES
 ('B406227',8,'Purchase order','2016-03-04T00:00:00.000',2346.0000000000000000)
,('B406227',12,'Production order','2016-03-04T00:00:00.000',-1295.4000000000000000)
,('B406227',12,'Production order','2016-03-07T00:00:00.000',-3651.6000000000000000)
,('B406227',8,'Purchase order','2016-03-11T00:00:00.000',4692.0000000000000000)
,('B406227',12,'Production order','2016-03-14T00:00:00.000',-1397.4000000000000000)
,('B406227',12,'Production order','2016-03-21T00:00:00.000',-958.8000000000000000)
,('B406227',45,'Formula line','2016-03-28T00:00:00.000',-696.1700000000000000)
,('B406227',45,'Formula line','2016-04-03T00:00:00.000',-527.5500000000000000)
,('B406227',8,'Purchase order','2016-04-07T00:00:00.000',7038.0000000000000000)
,('B406227',45,'Formula line','2016-04-07T00:00:00.000',-1186.5500000000000000);

SELECT p.*
FROM
(
    SELECT tt.Itemid
          ,tt.RefType
          ,tt.name
          ,SUM(Qty) AS SumQty
          ,'w' + CAST(DATEPART(WEEK,OriginalReqDate) AS VARCHAR(MAX)) AS ColumName 
    FROM #TestTbl AS tt
    GROUP BY ItemId,RefType,name,DATEPART(WEEK,OriginalReqDate) 
) AS tbl
PIVOT
(
    SUM(SumQty) FOR ColumName IN(w10,w11,w12,w13,w14,w15)
) AS p

DROP TABLE #TestTbl;

结果:

Itemid  RefType name              w10       w11        w12      w13     w14     w15
B406227 8       Purchase order    2346.00   4692.00    NULL     NULL    NULL    7038.00
B406227 12      Production order  -1295.40  -3651.60   -1397.40 -958.80 NULL    NULL  
B406227 45      Formula line      NULL      NULL       NULL     NULL  -1223.72 -1186.55