Performing general use on a proposal

Question

I am running SQL Server 2008, now I am trying to understand the over clause more, I used it before, but only with row_number() , and I found out that you can use aggregate functions with it and create things like the total amount.

I created the following test database.

 CREATE TABLE sales ( employee nvarchar(50), sales decimal(18, 2), datesale datetime ); Insert into sales Values('John','54.23','2017-01-30 08:00:00.000') Insert into sales Values('John','123.96','2017-01-30 09:00:00.000') Insert into sales Values('John','534.20','2017-01-30 10:00:00.000') Insert into sales Values('Ben','98.34','2017-01-29 04:00:00.000') Insert into sales Values('Ben','126.32','2017-01-29 05:00:00.000') 

And used the following query to create the current amount for each employee

 select employee, sales, datesale, SUM(sales) over (partition by employee order by datesale) as mvgsum from sales 

In SQL Fiddle using SQL Server 2008, I get the following results

  | employee | sales | datesale | mvgsum | |----------|--------|---------------------------|--------| | Ben | 98.34 | January, 29 2017 04:00:00 | 98.34 | | Ben | 126.32 | January, 29 2017 05:00:00 | 224.66 | | John | 54.23 | January, 30 2017 08:00:00 | 54.23 | | John | 123.96 | January, 30 2017 09:00:00 | 178.19 | | John | 534.2 | January, 30 2017 10:00:00 | 712.39 | 

However, in SQL Server Management Studio in a SQL Server 2008 database, I get the following error using the same query with the same table structure and data.

Invalid syntax near order.

What could be the reason for this?

Answer 1

SUM() OVER() not supported in SQL Server 2008.

You can use the correlated subquery:

 select employee, sales, datesale, ( select sum(sales) from sales s2 where s1.employee = s2.employee and s2.datesale <= s1.datesale ) mvgsum from sales s1 

Or CROSS APPLY :

 select employee, sales, datesale, x.mvgsum from sales s1 cross apply ( select sum(sales) mvgsum from sales s2 where s1.employee = s2.employee and s2.datesale <= s1.datesale ) x