SQL按日期将多个值求和为按日期的新值

Question

嗨，我有一些定期发布的报告，但当报告值为空时，它们具有帐户的初始值。我想创建一个新的变量Accts_N，它针对学校、年份和日期，是该日期的Accts值和日期为空时Accts值的总和。因此，使用下面的示例表，学校A Year 2017的2016-01-10的Accts_N值为8,2016-02-10的值为12。

School | Year | Accts | ReportDate
-------|------|-------|-----------
A      | 2017 | 2     | null
A      | 2017 | 6     | 2016-01-10
A      | 2017 | 10    | 2016-02-10
A      | 2018 | 0     | 2016-01-10
A      | 2018 | 4     | 2016-02-10
B      | 2017 | 9     | null
B      | 2018 | 3     | 2016-2-10

我已经尝试了几个不同的SUM案例，但我认为这不是正确的方法。有人能给我指个方向吗？谢谢

Answer 1

如果您想要添加一个新列，那么就会想到一个相关子查询：

select r.*,
       (select sum(r2.accts)
        from reports r2
        where r2.school = r.school and
              r2.year = r.year and
              (r2.reportdate = r.reportdate or r2.reportdate is null)
      ) as accts_n
from reports r;

Answer 2

这个怎么样？

  declare @t table(School nvarchar(50), Year datetime, Accts int, ReportDate datetime)

    insert into @t
    values
        ('A','2017',2,null),
        ('A','2017',6,'2016-01-10'),
        ('A','2017',10,'2016-02-10'),
        ('A','2018',0,'2016-01-10'),
        ('A','2018',4,'2016-02-10'),
        ('B','2017',9,null),
        ('B','2018',3,'2016-01-10')

    select t.School, t.Year, t.ReportDate, t.Accts + ISNULL(tNulls.SumAcctsWhenNull,0)
    from @t t
        outer apply (select t2.School, t2.Year, SUM(Accts) AS SumAcctsWhenNull
                    from @t t2
                    where
                        t2.ReportDate IS NULL AND
                        t2.School = t.School AND
                        t2.Year = t.Year
                    group by t2.School, t2.Year) tNulls
    where
        t.ReportDate IS NOT NULL