gpt4 book ai didi

mysql - SQL - 根据 YTD 数字计算出 MTD 数字

转载 作者:行者123 更新时间:2023-11-29 10:47:58 49 4
gpt4 key购买 nike

我们有一份按月运行的年初至今报告。我想通过计算 Month+1 减去 Month0 = MTD 来计算每个月的 MTD 变动

数据如下表所示

Month | Account | YTD amount
Jan | AB123 | 100
Feb | AB123 | 200
Mar | AB123 | 350

我有很多帐户,我需要一个正在运行的 MTD,如下所示。

Month | Account | MTD Amount
Jan | AB123 | 100
Feb | AB123 | 100
Mar | AB123 | 150

我读到您可以进行自连接,它可以在同一个表中搜索不同的行。但我不太明白如果你有 3 个多月的时间,你怎么能多次这样做。一月和二月看起来很简单。

SELECT A.[Month]
,A.[Account]
,A.[YTD] FebYTD
,B.[YTD] JanYTD
,A.[YTD] - B.[YTD] MTD
FROM
(
SELECT [Month]
,[Account]
,[YTD]
FROM [PWC_2017].[dbo].[MTD test]
where [Month] = 'Feb'
) A
join
(
SELECT [Month]
,[Account]
,[YTD]
FROM [PWC_2017].[dbo].[MTD test]
Where [Month] = 'Jan'
) B
on A.[Account] = B.[Account]

结果如下

Month | Account | FebYTD | JanYTD | MTD
Feb | AB123 | 200 | 100 | 100

我想做一个可以每月运行的查询。例如,当 9 月份到来时,它将给我 9 行 MTD。一月至九月。基本上它将在当前表中可用的所有月份上运行。谢谢。

最佳答案

对上述内容进行一点补充。

基于与 YTD-1 进行比较并包含 MTD 案例的前提,尽管示例中解决了 1 月的问题,但它不起作用,例如仅在 3 月记录数据时。第一个 MTD 将为 NULL。

所以我简单地在不存在先前的 YTD 时添加一个零,

CASE CurrentYTD.Month_ID
WHEN 1 Then COALESCE (CurrentYTD.Amount,PreviousYTD.Amount)- COALESCE(PreviousYTD.Amount,0)
WHEN 2 Then COALESCE (CurrentYTD.Amount,PreviousYTD.Amount)- COALESCE(PreviousYTD.Amount,0)
WHEN 3 Then COALESCE (CurrentYTD.Amount,PreviousYTD.Amount)- COALESCE(PreviousYTD.Amount,0)
WHEN 4 Then COALESCE (CurrentYTD.Amount,PreviousYTD.Amount)- COALESCE(PreviousYTD.Amount,0)
WHEN 5 Then COALESCE (CurrentYTD.Amount,PreviousYTD.Amount)- COALESCE(PreviousYTD.Amount,0)
WHEN 6 Then COALESCE (CurrentYTD.Amount,PreviousYTD.Amount)- COALESCE(PreviousYTD.Amount,0)
WHEN 7 Then COALESCE (CurrentYTD.Amount,PreviousYTD.Amount)- COALESCE(PreviousYTD.Amount,0)
WHEN 8 Then COALESCE (CurrentYTD.Amount,PreviousYTD.Amount)- COALESCE(PreviousYTD.Amount,0)
WHEN 9 Then COALESCE (CurrentYTD.Amount,PreviousYTD.Amount)- COALESCE(PreviousYTD.Amount,0)
WHEN 10 Then COALESCE (CurrentYTD.Amount,PreviousYTD.Amount)- COALESCE(PreviousYTD.Amount,0)
WHEN 11 Then COALESCE (CurrentYTD.Amount,PreviousYTD.Amount)- COALESCE(PreviousYTD.Amount,0)
WHEN 12 Then COALESCE (CurrentYTD.Amount,PreviousYTD.Amount)- COALESCE(PreviousYTD.Amount,0)
END AS Amount_MTD

关于mysql - SQL - 根据 YTD 数字计算出 MTD 数字,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/44234180/

49 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com