如何获得按周排序的每份工资的总和

Question

我有一个类似的表

Input Table -
Date          Salary
2020-01-01 00:00:00.000 2321
2020-01-02 00:00:00.000 2414
2020-01-03 00:00:00.000 2323
2020-01-04 00:00:00.000 2324
2020-01-05 00:00:00.000 2325.....so on 

我已经写了一个查询，但这个查询只给出了我使用LAG函数的前两周的总和

SELECT  DATENAME(MONTH,Date) Months,
DATEPART(WEEK,Date) as WeekNo,
SUM(Salary) Salary,
WeekSalary= LAG(SUM(salary)) OVER (PARTITION BY  Datepart(Month,Date) ORDER BY DATEPART(MONTH,Date))+SUM(salary)  
FROM SheetTable 
GROUP BY DATEPART(WEEK,Date),DATENAME(MONTH,Date)

输出：

Months  Week    Salary  WeekSalary
January    1    9382    NULL
January    2    11681   21063
January    3    55245   66926
January    4    90939   146184
January    5    14091   105030
February    5   2352    NULL
February    6   16492   18844
February    7   16541   33033
February    8   16590   33131
February    9   16639   33229
March      10   16685   NULL
March      11   16730   33415
March      12   16779   33509
March      13   16828   33607
March      14   7227    24055
April      14   9650    NULL
April      15   7248    16898

但是，如果我需要一个截止到本周的工资总额栏呢-我尝试了这个查询

SELECT  DATENAME(MONTH,Date) Months,
DATEPART(WEEK,Date) as WeekNo,
SUM(Salary) Salary,
TotalSalary=SUM(salary) OVER (ORDER BY Datepart(week,Date) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM SheetTable 
GROUP BY DATEPART(WEEK,Date),DATENAME(MONTH,Date)

但是这个获取错误：

选择列表中的列"SheetTable.ssalary"无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。

预期：

Months  Week    Salary   TotalSalary
January    1    9382          9382  --Week(1)
January    2    11681         21063 --week(1+2)
January    3    55245         76308 --week(1+2+3)
January    4    90939         167247--week(1+2+3+4)...so on 

Answer 1

您应该能够通过使用这样的框架来实现您所需要的：

SUM(SUM(salary)) OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)