用于计算 mtd、ytd 值的 SQL 查询

Question

我有一个表格，列有 ID ， Title ， Date ， Amount 。

我需要根据 Title ， Date 获取每笔交易的 MTD、YTD 金额值。

以前有人做过吗？

原文由 Hashir Rizwan 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

Select t.title, t.Date,
  Sum(y.Amount) YTD,
  Sum(m.Amount) MTD
From table t
   left join table y
      on y.Title = t.Title
         and datediff(year, y.Date, t.Date) = 0
         and y.Date <= t.Date
  left join table m
      on m.Title = t.Title
         and datediff(month, m.Date, t.Date) = 0
         and m.Date <= t.Date
         and m.Date <> y.Date
Group by t.title, t.Date

原文由 Charles Bretana 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

接受的解决方案是不正确的。假设我们有一个下表：

 ID   Title   Date        Amount
---  ------  ----------  ------
  1  Cust A  2020-01-01    2.00
  2  Cust A  2020-01-05    3.00
  3  Cust A  2020-02-01    5.00

接受的答案会给我们这些结果：

 Title   Date        YTD    MTD
------  ----------  -----  -----
Cust A  2021-01-01   2.00   2.00
Cust A  2021-01-05  10.00  10.00
Cust A  2021-02-01  10.00  15.00

这是因为每个连接都会将记录数乘以匹配记录数。删除聚合后可以很容易地看到这一点：

 Select t.title, t.Date, y.Date, m.Date,
  y.Amount,
  m.Amount
From [table] t
   join [table] y
      on y.Title = t.Title
         and datediff(year, y.Date, t.Date) = 0
         and y.Date <= t.Date
   join [table] m
      on m.Title = t.Title
         and datediff(month, m.Date, t.Date) = 0
         and m.Date <= t.Date
Order by t.title, t.Date, y.Date, m.Date

结果：

 Title   t.Date      y.Date      m.Date      y.Amount    m.Amount
-----   ----------  ----------  ----------  --------    --------
Cust A  2021-01-01  2021-01-01  2021-01-01         2           2
Cust A  2021-01-05  2021-01-01  2021-01-01         2           2
Cust A  2021-01-05  2021-01-01  2021-01-05         2           3
Cust A  2021-01-05  2021-01-05  2021-01-01         3           2
Cust A  2021-01-05  2021-01-05  2021-01-05         3           3
Cust A  2021-02-01  2021-01-01  2021-02-01         2           5
Cust A  2021-02-01  2021-01-05  2021-02-01         3           5
Cust A  2021-02-01  2021-02-01  2021-02-01         5           5

这是一个修改后的选择，可以产生正确的结果：

 Select a.title, a.Date,
  Sum(Case When datediff(year, b.Date, a.Date) = 0 Then b.Amount Else 0 End) YTD,
  Sum(Case When datediff(month, b.Date, a.Date) = 0 Then b.Amount Else 0 End) MTD
From [table] a
   join [table] b
      on a.Title = b.Title
         and b.Date <= a.Date
Group by a.title, a.Date

结果：

 Title   Date        YTD    MTD
------  ----------  -----  -----
Cust A  2021-01-01   2.00   2.00
Cust A  2021-01-05   5.00   5.00
Cust A  2021-02-01  10.00   5.00

这是一个包含所有当前答案以及新解决方案的 SQL Fiddle 。

原文由 Peter 发布，翻译遵循 CC BY-SA 4.0 许可协议