"Incorrect syntax" error using OVER()

Question

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'.

select 
TradingDate
,Budget
,sum(Budget) over (PARTITION BY TradingDate
order by TradingDate asc
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING),1) AS BudgetMTD
from #4

Answer 1

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:

1. The TradingDate is a date, with multiple values on each date. If so, the sum is indeterminate, since you do not know which one would follow.
2. TradingDate is poorly named, and it is really a date time. In this case, you are getting the next date time value, on the same date.
3. Your query is misformed and you do not really mean "partition by TradingDate order by TradingDate".

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

Answer 2

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