SQL Server 2008 using SUM() OVER(ORDER BY…)

Question

I am trying to use a CTE and CROSS JOIN the result set. I want to sum up the 4 rows leading up to the current row. The example online I found does not use a CTE, only a newly created table (http://sqlandme.com/2011/08/17/sql-server-denali-over-rows-range/). The syntax should work, but I get an error saying "Incorrect syntax near 'ROWS'".

An Example output would be this using the following statement: SUM(y) OVER(ORDER BY x ROWS 4 PRECEDING) sum

X Y SUM

     1          7     0     No prev rows, so sum is 0
     2          1     7     Sum   = 7 
     3          2     8           = 1 + 7
     4          5     10          = 2 + 1 + 7
     5          7     15          = 5 + 2 + 1 + 7
     6         34     15          = 7 + 5 + 2 + 1
     7         32     48          = 34 + 7 + 5 + 2

Does anyone have any suggestion on what is incorrect with the query? Thanks in advance.

with quarterResults as (
      <subquery in here>
)

--COLUMN1: String
--COLUMN2: Date
--COLUMN3: Date
--COLUMN4: Double
select a.TIC, a.DATADATE, a.EFFDATE, SUM(b.valuei) OVER (ORDER BY a.TIC, a.DATADATE, a.EFFDATE ROWS 4 PRECEDING) AS [SUM]
from quarterResults a
cross join quarterResults b
where a.datadate > b.datadate
group by a.tic, a.datadate, a.EFFDATE, a.valuei
order by a.TIC, a.datadate

Your first problem is that PRECEDING is for SQL Server Denali and not SQL Server 2008. — Wil

Michael Petito Michael Petito · Accepted Answer · 2011-10-04T18:50:35

The documentation you found for ROWS/RANGE is not for SQL Server 2008 - it's for a future version of SQL Server.

To accomplish your query in SQL 2008, one approach would be similar to:

SELECT a.TIC, a.datadate, a.effdate, x.s
FROM quarterResults a
    CROSS APPLY (   SELECT ISNULL(SUM(v), 0)
                    FROM (  SELECT TOP(4) b.valuei
                            FROM quarterResults b
                            WHERE b.datadate < a.datadate
                            ORDER BY b.datadate DESC ) x(v)
                ) x(s)
ORDER BY a.TIC, a.datadate

Note that this is potentially an expensive query. The use of the OVER expression with ROWS would probably be more efficient but, again, it is not available in SQL Server 2008.

SQL Server 2008 using SUM() OVER(ORDER BY…)

2 Answers