I have a sales budget for every trading day of the month. So for day 1 the budget is 300, for day 2 the budget is 400, and then month to date budget is 700. I get this error in my query:
Incorrect syntax near 'ROWS'.
,sum(Budget) over (PARTITION BY TradingDate
order by TradingDate asc
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),1) AS BudgetMTD
Your query is taking the sum of each day's amount, with the next amount within the same day (your partition and order by are on the same field). This is allowed, but it meanns one of three things:
I would speculate that you want one of two things. The first would be the sum of one day's Budget with the next day's. The other would be a cumulative sum.
There is the possibility that you have multiple rows per day and you want the sum of the budget on that day. If that's the case, then you can use the simpler formulation:
select TradingDate, Budget, sum(Budget) over (PARTITION BY TradingDate) AS BudgetMTD from #4
Ok I came up with a subquery solution:
select TradingDate, Budget, RunningTotal = (select sum(Budget) from #4 s2 where s2.TradingDate<= s1.TradingDate) from #4 s1 order by s1.TradingDate