在接下来的12个月里按顺序计算项目

Question

我需要获得未来12个月订单中的项目数量，如下所示：

9月22日

项目编号	8月22日	10月22日>11月22日等
第1项	10	13	12	130
第2项	20	23	22	230

您还没有提供示例数据，但看起来您需要条件聚合(也称为数据透视(。与使用PIVOT运算符相比，使用SUM(CASE通常更容易做到这一点(如果有点冗长的话(。

SELECT
i.ItemNumber,
SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE()) THEN i.Quantity END) AS CurrentMonth,
SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 1) THEN i.Quantity END) AS Month1,
SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 2) THEN i.Quantity END) AS Month2,
SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 3) THEN i.Quantity END) AS Month3,
SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 4) THEN i.Quantity END) AS Month4,
SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 5) THEN i.Quantity END) AS Month5,
SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 6) THEN i.Quantity END) AS Month6,
SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 7) THEN i.Quantity END) AS Month7,
SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 8) THEN i.Quantity END) AS Month8,
SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 9) THEN i.Quantity END) AS Month9,
SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 10) THEN i.Quantity END) AS Month10,
SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 11) THEN i.Quantity END) AS Month11,
FROM Items i
GROUP BY
i.ItemNumber;

一个可能的选项，使用PIVOT关系运算符：

样本数据：

SELECT *
INTO Items
FROM (VALUES
('item1', CONVERT(date, '20220802'), 10),
('item1', CONVERT(date, '20220902'), 13),
('item1', CONVERT(date, '20221002'), 12),
('item1', CONVERT(date, '20221102'), 130),
('item2', CONVERT(date, '20220802'), 20),
('item3', CONVERT(date, '20220902'), 23),
('item4', CONVERT(date, '20221002'), 22),
('item5', CONVERT(date, '20221102'), 230)
) t (ItemNo, ShipmentDate, Quantity)

声明：

SELECT *
FROM (
SELECT ItemNo, Quantity, DATEDIFF(month, EOMONTH(GETDATE()), EOMONTH(ShipmentDate)) AS NextMonth
FROM Items
) t
PIVOT (
SUM(Quantity)
FOR NextMonth IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) p

结果：

<1><2>34<5>6>9>

ItemNo	0	7	18	10	11
第1项	10	13	120
第2项	20
第3项			23
第4项
第5项				230