Sample query from Microsoft SQL Server 2012 T-SQL fundamental not working

Question

I have recenty began following Microsoft SQL Server 2012 T-SQL Fundamental book to learn T-SQL. The following query I tried executing in SQL Server 2008 r2

SELECT empid, ordermonth, val,
SUM(val) OVER(PARTITION BY empid
              ORDER BY ordermonth
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runval
FROM Sales.EmpOrders;

the query fails to execute with the following error message

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'ROWS'.

this is the fragment of sales.emporders view

empid   ordermonth                  qty     val         numorders
-----------------------------------------------------------------
5       2007-10-01 00:00:00.000     361     7581.33         9
6       2007-06-01 00:00:00.000     173     3464.81         7
9       2007-09-01 00:00:00.000     93      8776.15         5
5       2008-02-01 00:00:00.000     276     5377.06         15
5       2007-07-01 00:00:00.000     213     6475.40         5
4       2006-10-01 00:00:00.000     613     13718.97        27

Need help in figureing out a work around for sql server 2008 r2

This is a new property that can be used with SQL Server 2012, not on previous SQL Server versions. You can apply the rows range clause http://www.kodyaz.com/t-sql/sql-last_value-analytic-function-sample-in-sqlserver2012.aspx on analytical functions as well which are new with SQLServer 2012 — Eralper, Apr 04 '16 at 07:12
http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server — Liesel, Apr 04 '16 at 07:48

score 1 · Answer 1 · answered Apr 04 '16 at 06:57

1

It's no SQL Server 2012 functionality. You can use function OVER but in 2008R2 there is no functionality for: ROWS BETWEEN UNBOUNDED PRECEDING

SEE:

https://msdn.microsoft.com/en-us/library/ms189461(v=sql.105).aspx (2008R2)

AND

https://msdn.microsoft.com/en-us/library/ms189461(v=sql.110).aspx (2012)

answered Apr 04 '16 at 06:57

Jeffrey

412
2
10

Can you please suggest a workaround for 2008 r2 running total of the val field is calculated for each empid for a particular month – Tausif Apr 04 '16 at 07:20
Will have a look at it tonight. – Jeffrey Apr 04 '16 at 07:48

score 0 · Answer 2 · answered Apr 04 '16 at 18:23

0

SELECT empid, 
       ordermonth, 
       val,
       (SELECT SUM(val) 
        FROM Sales.EmpOrders ee 
        WHERE e.empid=ee.empid 
        AND ee.ordermonth<=e.ordermonth) AS runval
FROM Sales.EmpOrders e;

answered Apr 04 '16 at 18:23

Wyatt Shipman

1,669
1
10
22

score 0 · Answer 3 · answered Apr 04 '16 at 18:39

0

If you are using UNBOUNDED PRECEDING I think you can just drop it
Did you try?

SELECT empid, ordermonth, val
     , SUM(val) OVER(PARTITION BY empid ORDER BY ordermonth) AS runval
FROM Sales.EmpOrders;

answered Apr 04 '16 at 18:39

paparazzo

44,497
23
105
176

well it appears sql server 2008 dosent supports only partitioning and not ordering for aggregate functions – Tausif Apr 05 '16 at 05:03

Sample query from Microsoft SQL Server 2012 T-SQL fundamental not working

3 Answers3