SQL Equivalent of Oracle OVER (PARTITION BY ORDER BY RANGE UNBOUNDED PRECEDING)

Question

SQL Equivalent of Oracle OVER (PARTITION BY ORDER BY RANGE UNBOUNDED PRECEDING)

rattil

SSC Enthusiast

Points: 129
More actions
August 18, 2009 at 9:27 am

#103802

SQL Equivalent of Oracle OVER (PARTITION BY ORDER BY RANGE UNBOUNDED PRECEDING)
I'm in the process of converting Oracle materialized view to SQL indexed view. This view has following analytical functions in written in Oracle:
……
SUM(Vacation_Hrs) OVER(PARTITION BY Emp_ID ORDER BY Pay_Period RANGE UNBOUNDED PRECEDING) As ‘Running Total of Vac_Hrs’,
SUM(SickLeave_Hrs) OVER(PARTITION BY Emp_ID ORDER BY Pay_Period RANGE UNBOUNDED PRECEDING) As ‘Running Total of SickLeave_Hrs’,
……. Etc.
I have tried to use the T-SQL’s OVER() function but it doesn’t have the feature like oracle does. I see there are couple of other postings in the internet, SQL Server Magazine. But none of them are as clean as the Oracle’s one. I appreciate any idea of solving this problem.
Thanks.
Razzakul Chowdhury

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

For those of us who are not Oracle users (anymore), you'll need to give an example of a data set and what results set you expect the function to create. Follow the example in the thread below for proper posting.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

xizwyck Valued Member Points: 58 More actions · Answer 2

It's 2015. Just had to say, thank you for not even trying to answer the question. You are technically correct; however, very unhelpful to those who might stumble across this page in the future, struggling to find an answer.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

What version of SQL Server are you using? Sounds like you may be using SQL Server 2008 and the features you are looking for were released in SQL Server 2012.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

xizwyck (5/18/2015)
It's 2015. Just had to say, thank you for not even trying to answer the question. You are technically correct; however, very unhelpful to those who might stumble across this page in the future, struggling to find an answer.

Actually...asking for detailed information is often the first step in being able to provide a relevant answer. For the record, the original post is 6 years old. I am sort of surprised you found this old forum in a search. Being that as it may, in sql 2012 the windowing functions have been extended to do the same thing and the syntax is pretty much identical. Prior to 2012 it is more difficult.

Here is the BOL (books online) page about the OVER clause. https://msdn.microsoft.com/en-us/library/ms189461.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

rattil (8/18/2009)
SQL Equivalent of Oracle OVER (PARTITION BY ORDER BY RANGE UNBOUNDED PRECEDING)
I'm in the process of converting Oracle materialized view to SQL indexed view. This view has following analytical functions in written in Oracle:
……
SUM(Vacation_Hrs) OVER(PARTITION BY Emp_ID ORDER BY Pay_Period RANGE UNBOUNDED PRECEDING) As ‘Running Total of Vac_Hrs’,
SUM(SickLeave_Hrs) OVER(PARTITION BY Emp_ID ORDER BY Pay_Period RANGE UNBOUNDED PRECEDING) As ‘Running Total of SickLeave_Hrs’,
……. Etc.
I have tried to use the T-SQL’s OVER() function but it doesn’t have the feature like oracle does. I see there are couple of other postings in the internet, SQL Server Magazine. But none of them are as clean as the Oracle’s one. I appreciate any idea of solving this problem.
Thanks.
Razzakul Chowdhury

Quick question, which version of SQL Server are you using? As Lynn said, the extended analytical functionality was introduced in SQL Server 2012, slightly later than in ORA. IIRC this functionality wasn't even fully defined when SQL Server 2005 was released 😉

😎

xizwyck Valued Member Points: 58 More actions · Answer 6

It's six years old but still appears in a search engine query result. If the information/thread is incomplete or not marked as resolved and it's this old, it should be removed.

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 7

xizwyck (5/18/2015)
It's six years old but still appears in a search engine query result. If the information/thread is incomplete or not marked as resolved and it's this old, it should be removed.

He he, didn't even notice that:-D

Thanks xizwyck!

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

xizwyck (5/18/2015)
It's six years old but still appears in a search engine query result. If the information/thread is incomplete or not marked as resolved and it's this old, it should be removed.

Why should it be removed? It still serves a purpose.