9

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
Pondlife
  • 15,992
  • 6
  • 37
  • 51
Wilest
  • 1,820
  • 7
  • 36
  • 62
  • 10
    That is 2012 syntax. You are probably using an earlier version. (Although you do have an unmatched `,1)` in there as well. – Martin Smith Oct 31 '12 at 13:22
  • Yes I'm using 2008, thanks will investigate – Wilest Oct 31 '12 at 13:26
  • For 2008 see http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver – Martin Smith Oct 31 '12 at 13:27
  • 5
    Additionally, you've got a strange `,1)` sitting in there for no apparent reason. Did you copy this from BoL? If so, the `,1)` is part of the `CONVERT(` at the start of that line... – MatBailie Oct 31 '12 at 13:27
  • Thank you @MartinSmith, you just saved me hours of time figuring out why my SQL Server instance didn't like the ROWS key word :) – Jeff.Clark Sep 21 '16 at 22:24

2 Answers2

2

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
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
Wilest
  • 1,820
  • 7
  • 36
  • 62