英文:
How to calcualte prior Year Cumulative QTD sales
问题
我需要计算去年累计季度销售额。
输入：[![enter image description here][1]][1]
期望输出：[![enter image description here][2]][2]
;with Data AS (
-- 输入数据
-- ...
)
SELECT [Year],[quarter],[Date],SalesID,
SUM(SalesAmt) OVER (PARTITION BY [Year], SalesId ORDER BY [quarter]) AS YTDSales,
LAG(SalesAmt, 4) OVER (PARTITION BY SalesId ORDER BY [Year]) AS PriorCumulativeQTDSales
FROM data a
问题：我无法得到PriorCumulativeQTDSales，请问如何修复我的查询？
注：对于2022年Q1的SalesId（1），PriorCumulativeQTDsales是前一年第1季度的销售额，即45。
对于2022 Q2，PriorCumulativeQTDsales是前一年第1季度销售额与第2季度销售额之和。
对于2022 Q3，PriorCumulativeQTDsales是前一年第1季度销售额与第2季度销售额与第3季度销售额之和。
对于SalesId（2），由于缺少季度数据（2021年第1季度，第2季度和2022年第3季度），PriorCumulativeQTDsales是前一年（2021年）第3季度销售额与第4季度销售额之和，应该在2022年第4季度填充，因为2022年第3季度数据缺失。
[1]: https://i.stack.imgur.com/ON8uj.jpg
[2]: https://i.stack.imgur.com/R4b6r.jpg
英文:
I need to calculate the prior year cumulate QTD sales.
Input:
expected output : 
;with Data AS (
select 2021 as [year], 1 as [Quarter] , 1 as SalesID,'1/29/2021' AS Date, 45 AS SalesAmt
UNION
select 2021 as [year], 2 as [Quarter] , 1 as SalesID,'4/26/2022' AS Date, 100 AS SalesAmt
UNION
select 2021 as [year], 3 as [Quarter] , 1 as SalesID,'8/29/2021' AS Date, 100 AS SalesAmt
UNION
select 2021 as [year], 4 as [Quarter] , 1 as SalesID,'11/26/2022' AS Date,50 AS SalesAmt
UNION
select 2022 as [year], 1 as [Quarter] , 1 as SalesID,'1/25/2022' AS Date,100 AS SalesAmt
UNION
select 2022 as [year], 2 as [Quarter] , 1 as SalesID,'5/22/2022' AS Date,200 AS SalesAmt
UNION
select 2022 as [year], 3 as [Quarter] , 1 as SalesID,'7/16/2022' AS Date,300 AS SalesAmt
UNION
select 2022 as [year], 4 as [Quarter] , 1 as SalesID,'12/16/2022' AS Date,400 AS SalesAmt
UNION--
select 2021 as [year], 3 as [Quarter] , 2 as SalesID,'1/26/2021' AS Date, 10 AS SalesAmt
UNION
select 2021 as [year], 4 as [Quarter] , 2 as SalesID,'12/15/2021' AS Date, 15 AS SalesAmt
UNION
select 2022 as [year], 1 as [Quarter] , 2 as SalesID,'1/10/2022' AS Date, 20 AS SalesAmt
UNION
select 2022 as [year], 2 as [Quarter] , 2 as SalesID,'4/10/2022' AS Date, 20 AS SalesAmt
UNION
select 2022 as [year], 4 as [Quarter] , 2 as SalesID,'10/24/2022' AS Date, 20 AS salesAmt
)
select [Year],[quarter],[Date],SalesID
,SUM(SalesAmt) OVER (Partition by [Year],SalesId order by [quarter]) As YTDSales
,LAG(SalesAmt,4) OVER ( Partition by SalesId order by [Year]) As PriorCumulativeQTDSales
from data a
Issue: I am not able to get the PriorCumulativeQTDSales, Any pointers on how to fix my query please ?
Note: For 2022 Q1 for SalesId (1), PriorCumulativeQTDsales is previous year Quarter1 sales which is 45.
For 2022 Q2, PriorCumulativeQTDsales is sum of previous year Quarter1 sales + Quarter 2 sales
For 2022 Q3, PriorCumulativeQTDsales is sum of previous year Quarter1 sales + Quarter 2 + Quarter 3 sales
In case of salesId (2), since quarters are missing (2021 quarter1 , Quarter2 and 2022 quarter3), PriorCumulativeQTDsales is sum of previous year(2021) Quarter 3 sales + Quarter 4 sales and it should be populated in 2022 Quarter 4 as 2022 quarter 3 is missing
答案1
得分: 0
我认为在这种情况下，不使用LAG而是进行自连接会更容易：
SELECT [Year],[quarter],[Date],SalesID
, SUM(SalesAmt) OVER (PARTITION BY [Year],SalesId ORDER BY [quarter]) AS YTDSales
, ISNULL(priorCumQTDSales, 0) AS PriorCumulativeQTDSales
FROM data a
OUTER APPLY (
SELECT SUM(SalesAmt) AS priorCumQTDSales
FROM data prev
WHERE prev.salesID = a.SalesID
AND prev.year = a.year -1
AND prev.quarter <= a.quarter
) prev
或者如果你更喜欢子查询的话：
SELECT [Year],[quarter],[Date],SalesID
, SUM(SalesAmt) OVER (PARTITION BY [Year],SalesId ORDER BY [quarter]) AS YTDSales
, ISNULL((
SELECT SUM(SalesAmt) AS priorCumQTDSales
FROM data prev
WHERE prev.salesID = a.SalesID
AND prev.year = a.year -1
AND prev.quarter <= a.quarter
), 0) AS PriorCumulativeQTDSales
FROM data a
英文:
I think in this case it's easier not to use LAG but to do a self join:
SELECT [Year],[quarter],[Date],SalesID
, SUM(SalesAmt) OVER (Partition BY [Year],SalesId ORDER BY [quarter]) AS YTDSales
, ISNULL(priorCumQTDSales, 0) AS PriorCumulativeQTDSales
FROM data a
OUTER APPLY (
SELECT sum(SalesAmt) AS priorCumQTDSales
FROM data prev
WHERE prev.salesID = a.SalesID
AND prev.year = a.year -1
AND prev.quarter <= a.quarter
) prev
Or a subquery if you swing that way:
SELECT [Year],[quarter],[Date],SalesID
, SUM(SalesAmt) OVER (Partition BY [Year],SalesId ORDER BY [quarter]) AS YTDSales
, ISNULL((
SELECT sum(SalesAmt) AS priorCumQTDSales
FROM data prev
WHERE prev.salesID = a.SalesID
AND prev.year = a.year -1
AND prev.quarter <= a.quarter
) , 0) AS PriorCumulativeQTDSales
FROM data a
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论