Translate MS SQL DENSE_RANK to Oracle

ttist25
ttist25 used Ask the Experts™
Hey there!

I'm using a DENSE_RANK function in MS SQL to group by a datetime +/- 2hrs and it's working really well but I need to adapt it to Oracle and I'm having some trouble making that happen.

Here is the TSQL:
MyRank = DENSE_RANK() 
OVER (ORDER BY 
MyPID,MyAgency,MyCCD,(DATEADD(hour, (DATEDIFF(hour, '', MyDtTime)/2)*2,'')))

Open in new window


I tried a TSQL to Oracle converter thing but that's not getting it.                    

Any help will be greatly appreciated.

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer
I don't have an Oracle instance at hands right now, but DENSE_RANK() works in PL/SQL like in T-SQL. It's more likely that you got your date/time arithmetic's wrong.
When I need to guess it's your - in T-SQL working, but badly chosen start date/time literal '', this should 0 - here you need to make your self familiar with T-SQL: 0 (INT) or '' (VARCHAR) is implicitly convertible to the default DATETIME value '1900-01-01 00:00:00', btw a Monday.

Thus something like this should do it:

SELECT DENSE_RANK() OVER ( ORDER BY MyPID ,
                                    MyAgency ,
                                    MyCCD ,
                                    DATEADD(HOUR, DATEDIFF(HOUR, TO_DATE('1900-01-01 00:00:00'), MyDtTime) / 2 ) * 2, '') AS MyRank
FROM   yourTable;

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018
PL/SQL is Oracle's procedural language.  Oracle calls SQL just SQL.

>>I don't have an Oracle instance at hands right now

Oracle has a site where you can test SQL:
https://livesql.oracle.com/apex/f?p=590:1000

Requires creating a free account.

>>adapt it to Oracle and I'm having some trouble making that happen

If you can provide some sample data and expected results, I'm sure we can come up with something.

Author

Commented:
Ok - thanks for the help guys - here's some DDL:

CREATE TABLE
MyTable (MyPID VARCHAR2(20), MyIDNum VARCHAR2(20), MyAgency VARCHAR2(50), MyCCode VARCHAR2(100), MyDate DATE);

INSERT ALL
    INTO MyTable (MyPID, MyIDNum, MyAgency, MyCCode, MyDate) VALUES ('12345A', 'F12345678', 'RYPX', '23', (TO_DATE('2018-10-24 20:39:00', 'yyyy-mm-dd hh24:mi:ss')))
    INTO MyTable (MyPID, MyIDNum, MyAgency, MyCCode, MyDate) VALUES ('12345A', 'F23456789', 'RYPX', '23', (TO_DATE('2018-10-24 18:39:00', 'yyyy-mm-dd hh24:mi:ss')))
    INTO MyTable (MyPID, MyIDNum, MyAgency, MyCCode, MyDate) VALUES ('23456A', 'G12345678', 'RYPX', '23', (TO_DATE('2018-10-10 07:38:00', 'yyyy-mm-dd hh24:mi:ss')))
    INTO MyTable (MyPID, MyIDNum, MyAgency, MyCCode, MyDate) VALUES ('23456A', 'H12345678', 'RYPX', '30', (TO_DATE('2018-10-10 07:38:00', 'yyyy-mm-dd hh24:mi:ss')))
    INTO MyTable (MyPID, MyIDNum, MyAgency, MyCCode, MyDate) VALUES ('34567C', 'E12345678', 'RYPX', '62', (TO_DATE('2017-03-21 12:43:00', 'yyyy-mm-dd hh24:mi:ss')))
SELECT * FROM Dual;

SELECT * FROM MyTable;

SELECT 
      MyPID
     ,MyIDNum
     ,MyAgency
     ,MyCCode
     ,MyDate
     ,DENSE_RANK() OVER (ORDER BY MyPID
                                  ,MyAgency
                                  ,MyCCode
                                  ,DATEADD(HOUR, DATEDIFF(HOUR, TO_DATE('1900-01-01 00:00:00'), MyDate) / 3 ) * 3, '') AS MyRank
FROM MyTable;

Open in new window


And the expected result (hoping this formatting conveys):
MYPID      MYIDNUM      MYAGENCY      MYCCODE      MYDATE            MYRANK
12345A      F12345678      RYPX              23                      24-OCT-18      1
12345A      F23456789      RYPX              30                      24-OCT-18      1
23456A      G12345678      RYPX              23                      10-OCT-18      2
23456A      H12345678      RYPX              30                      10-OCT-18      3
34567C      E12345678      RYPX              62                      21-MAR-17      4

The SQL online Oracle instance is kicking back "ORA-00904: "DATEADD": invalid identifier" which makes sense but - how do I do INTERVAL or whatever?

Thanks again - really appreciate the help
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Most Valuable Expert 2012
Distinguished Expert 2018
Why wouldn't 23456A have the same rank?

Your original query doesn't provide the results you want in SQL Server:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=385380b1838aef85587adbca348d024b

Author

Commented:
Shoot - moving too quickly update the fiddle with this:

OVER (ORDER BY 
MyPID,MyAgency,MyCCode,(DATEADD(hour, (DATEDIFF(hour, '', MyDate)/3)*3,'')))

Open in new window


That gets what I'm after
Most Valuable Expert 2012
Distinguished Expert 2018
Thanks for the update.

Still don't understand why 23456A doesn't need the same rank since it is within three hours.

Trying to understand the requirements.

Author

Commented:
Because of the different MyCCode values (23 and 30)

I set the values up that way on purpose for testing.
Most Valuable Expert 2012
Distinguished Expert 2018
Your expected results threw me.  It showed 12345A also with a 23 and 30.  Now I see the sample data has them the same.

I think I have it now.  Let me see what I can do if someone else doesn't beat me to the answer.
Most Valuable Expert 2012
Distinguished Expert 2018
See if this works:
select mypid, mydate, myccode,
DENSE_RANK() 
OVER (ORDER BY 
MyPID,MyAgency,MyCCode,floor(((mydate-to_date('01-01-1900','DD-MM-YYYY'))*24)/3))
from mytable;

Open in new window

Author

Commented:
HAH!  WOO HOOOOOO!!!!!!

SELECT MyPID, MyIDNum, MyAgency, MyCCode, MyDate,
DENSE_RANK()
OVER (ORDER BY MyPID, MyAgency, MyCCode, floor(((MyDate - TO_DATE('01-01-1900','DD-MM-YYYY'))*24)/3))
FROM MyTable;

You got it man!  

THANK YOU!!!!!
Most Valuable Expert 2012
Distinguished Expert 2018
Glad to help.

Still not 100% happy with it because I think it is more complex than it needs to be but it mirrored the SQL Server method you posted.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial