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

Question

我们有一份按月运行的年初至今报告。我想通过计算 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。一月至九月。基本上它将在当前表中可用的所有月份上运行。谢谢。

Answer 1

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

基于与 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