0

I have the following query which works great on SQL Server 2012:

   SELECT Name, 
     WeekNumber,
     SUM(NumberOfSecondsWorked) AS totalDaily,
     StartTime,
     EndTime,
     SUM(SUM(NumberOfSecondsWorked)) OVER (PARTITION BY WeekNumber ORDER BY EndTime) AS totalWeekly 
   FROM #temp AS T1

But unfortunately I get the following error when running this query on a SQL Server 2008 DB:

Incorrect syntax near 'order'.

My desired outcome of the above query is to add the NumberOfSecondsWorked by Day for each week. Here is my desired output:

enter image description here

But without the ORDER BY, I just get a total for each week without the increment by day:

enter image description here

Anybody know how to run the above query in SQL Server 2008? Or a mechanism to get the same result? Thanks!

JNevill
  • 46,980
  • 4
  • 38
  • 63
user1501171
  • 210
  • 1
  • 3
  • 17
  • 3
    (1) SQL Server 2008 doesn't support cumulative sums. (2) Your query doesn't make sense, because you are selecting columns that are not in the `GROUP BY`. Sample data and desired results would help. – Gordon Linoff Jul 08 '19 at 13:10
  • Apologies, the Group BY was a typo! – user1501171 Jul 08 '19 at 13:11
  • From Simone Spagna: `in order to reproduce your problem I'm in need of create statement for the table and some sample data. Can you provide me SQL SCRIPT?` – Panagiotis Kanavos Jul 08 '19 at 13:18
  • Updated to include sample data :) – user1501171 Jul 08 '19 at 13:18
  • @user1501171 images aren't sample data. They are just images that can't be used for testing unless people actually type them value by value – Panagiotis Kanavos Jul 08 '19 at 13:18
  • Possible duplicate of [Calculate running total / running balance](https://stackoverflow.com/questions/11310877/calculate-running-total-running-balance) – Eric Brandt Jul 08 '19 at 13:20
  • @user1501171 in any case SQL Server 2008 is out of support. Even SQL Server 2014 SP3 goes out of mainstream support *tomorrow*. You should upgrade to a supported SQL Server version. – Panagiotis Kanavos Jul 08 '19 at 13:20

2 Answers2

3

SQL Server 2008 doesn't support cumulative sums. My recommendation is to upgrade to a supported version of SQL Server.

That said, you can implement this using a correlated subquery or lateral join (i.e. apply):

SELECT t.*,
       (SELECT SUM(t2.totalDaily)
        FROM #temp t2
        WHERE t2.WeekNumber = t.WeekNumber AND
              t2.EndTime <= t.EndTime
       ) as running_weekly
FROM #temp t2
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I suggest you to read the following article : https://dba.stackexchange.com/questions/47116/in-microsoft-sql-server-2008-syntax-generates-the-error-the-parallel-data-ware

It explains that what you want to do is not supported until SQL2012. You can use the workaround suggested by the collegue but the best solution is to migrate to sql server version that supports what you want to do. Bye, Simone

Simone Spagna
  • 626
  • 7
  • 27
  • Welcome to Stack Overflow! This is really a comment, not an answer. With a bit more rep, [you will be able to post comments](//stackoverflow.com/privileges/comment). For the moment I've added the comment for you, and I'm flagging this post for deletion. – Panagiotis Kanavos Jul 08 '19 at 13:17