I have a table like this
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
I have written a query but this query is only giving the sum of previous two weeks for which I have used LAG function
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)
Output:
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
but what if I need a column of total salary till week- I tried this query
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)
but this getting error:
Column 'SheetTable.salary' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Expected:
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
You should be able to achieve what you need by using a frame like so:
SUM(SUM(salary)) OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)