时间戳

首页 > 解决方案 > TSQL - 用总计填写缺失的月份

sql-server - TSQL - 用总计填写缺失的月份

问题描述

我的表“金额”提供每月总计，但不是每个月：

Employee  Reference_no  YearMonth  Amount
1         1             202001     400
1         1             202002     600
1         1             202005     250
1         2             202001     100
1         2             202003     700

加入默认的“日历”表后，我可以显示缺失的月份：

Employee  Reference_no  YearMonth  Amount
1         1             202001     400
1         1             202002     600
1         1             202003     NULL
1         1             202004     NULL
1         1             202005     250
1         2             202001     100
1         2             202002     NULL
1         2             202003     700

不幸的是，总数丢失了，而它们应该从上一个填写的月份转移。因此，对于 202003 和 202004 (Ref_no_1)，金额应为 600。对于 202002 (Ref_no_2)，金额应为 100。

我已经挣扎了很长时间，但似乎无法让它发挥作用。我为 Amount 字段解决方案找到了 OUTER APPLY TOP (1) 和 SELECT TOP(1) ORDER BY，但由于数据集的大小，查询在尝试运行时会卡住。

任何建议都非常感谢！

标签： sql-servertsqlsum

解决方案

您需要做的就是将行从 Amount 转换为包含它们有效的范围，然后将它们连接到日历表。

这些行将在 YearMonth 列中隐式包含开始日期。要获取截止日期，您可以使用LEAD窗口函数按顺序从下一行获取日期。如果没有下一行，则只需将 1 添加到当前行的 YearMonth 以便仅使用该行。

SELECT amt.Employee, amt.Reference_no, cal.YearMonth, amt.Amount
FROM (
  SELECT *, 
    LEAD(YearMonth, 1, YearMonth + 1)
      OVER (PARTITION BY Employee, Reference_no ORDER BY YearMonth) [Cutoff]
  FROM Amount
) amt
INNER JOIN Calendar cal ON cal.YearMonth >= amt.YearMonth AND cal.YearMonth < amt.Cutoff

推荐阅读