时间戳

首页 > 解决方案 > SQL - 每列显示特定范围的结果？

sql - SQL - 每列显示特定范围的结果？

问题描述

我正在尝试在 SAP 内部创建一个销售矩阵。

我要运行的 SQL 报告非常基础。它涉及以下内容：

SELECT 
    T1.ItemCode, SUM(T1.Quantity)
FROM
    OINV T0
INNER JOIN
    INV1 T1 ON T0.DocEntry = T1.DocEntry
WHERE
    ItemCode IS NOT NULL
    AND CardCode = 'C001500'
GROUP BY 
    T1.ItemCode
ORDER BY 
    T1.ItemCode

我希望将该SUM(t1.quantity)领域分解为数周，但无法完全弄清楚如何将其分解。

我想要Current Week一个daterangebetween Getdate()and Getdate()+7，然后Week1是Getdate()+8 ToGetDate()+14` 等等...这将持续 12 周。

谢谢你的帮助！

安迪

标签： sqlsql-servertsqldate

解决方案

我现在已经编写了这段代码，但是 SUM T1.Quantity 不是总计。我看到 AHV16 在第 5 周销售 1 件商品，然后是另一个实例 AHV16 在第 2 周销售一件商品。AHV 16 在第 1 - 12 周显示 NULL，并且在 12 周内显示 1（应该是第 2 周 + 第 5 周。

SELECT T1.ItemCode
, '12 Week Total' = (SELECT sum(T1.Quantity) WHERE (T0.DocDate>=DateAdd(Day,-84,Getdate()) AND T0.DocDate<=DateAdd(D,0,Getdate())))
, 'Current Week' = (SELECT sum(T1.Quantity) WHERE (T0.DocDate>=DateAdd(Day,-7,Getdate()) AND T0.DocDate<=DateAdd(D,0,Getdate())))
, 'Week 2' = (SELECT sum(T1.Quantity) WHERE (T0.DocDate>=DateAdd(Day,-14,Getdate()) AND T0.DocDate<=DateAdd(D,-8,Getdate())))
, 'Week 3' = (SELECT sum(T1.Quantity) WHERE (T0.DocDate>=DateAdd(Day,-21,Getdate()) AND T0.DocDate<=DateAdd(D,-15,Getdate())))
, 'Week 4' = (SELECT sum(T1.Quantity) WHERE (T0.DocDate>=DateAdd(Day,-28,Getdate()) AND T0.DocDate<=DateAdd(D,-22,Getdate())))
, 'Week 5' = (SELECT sum(T1.Quantity) WHERE (T0.DocDate>=DateAdd(Day,-35,Getdate()) AND T0.DocDate<=DateAdd(D,-29,Getdate())))
, 'Week 6' = (SELECT sum(T1.Quantity) WHERE (T0.DocDate>=DateAdd(Day,-42,Getdate()) AND T0.DocDate<=DateAdd(D,-36,Getdate())))
, 'Week 7' = (SELECT sum(T1.Quantity) WHERE (T0.DocDate>=DateAdd(Day,-49,Getdate()) AND T0.DocDate<=DateAdd(D,-43,Getdate())))
, 'Week 8' = (SELECT sum(T1.Quantity) WHERE (T0.DocDate>=DateAdd(Day,-56,Getdate()) AND T0.DocDate<=DateAdd(D,-50,Getdate())))
, 'Week 9' = (SELECT sum(T1.Quantity) WHERE (T0.DocDate>=DateAdd(Day,-63,Getdate()) AND T0.DocDate<=DateAdd(D,-57,Getdate())))
, 'Week 10' = (SELECT sum(T1.Quantity) WHERE (T0.DocDate>=DateAdd(Day,-70,Getdate()) AND T0.DocDate<=DateAdd(D,-64,Getdate())))
, 'Week 11' = (SELECT sum(T1.Quantity) WHERE (T0.DocDate>=DateAdd(Day,-77,Getdate()) AND T0.DocDate<=DateAdd(D,-71,Getdate())))
, 'Week 12' = (SELECT sum(T1.Quantity) WHERE (T0.DocDate>=DateAdd(Day,-84,Getdate()) AND T0.DocDate<=DateAdd(D,-78,Getdate())))


    FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

WHERE ItemCode IS NOT NULL and CardCode = 'C001500'
GROUP BY T1.ItemCode, T0.Docdate

Order By T1.ItemCode

推荐阅读