Home » SQL & PL/SQL » SQL & PL/SQL » How to subtract dates between different rows (Oracle 12)
How to subtract dates between different rows [message #682630] Tue, 03 November 2020 16:57 Go to next message
talhaparvaiz@yahoo.com
Messages: 14
Registered: October 2020
Junior Member
Hi,

I have a table in Oracle that reads something like this

Employee ID Group Date
ABC123 Job start Jan 1 2020
ABC123 Promotion 1 Mar 1 2020
ABC123 Promotion 2 May 1 2020
ABC123 Promotion 3 Dec 1 2020


I want to find out how long this employee stayed within the single role... so the output that I am looking for is something like this


Employee ID Group days in the role
ABC123 Promotion 1 90
ABC123 Promotion 2 60
ABC123 Promotion 3 210

Please let me know how to accomplish this

Thanks in advance
Re: How to subtract dates between different rows [message #682631 is a reply to message #682630] Tue, 03 November 2020 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Re: How to subtract dates between different rows [message #682632 is a reply to message #682630] Tue, 03 November 2020 18:12 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Hi,

sure, as you require query output, use SELECT statement.
For getting "days in the role" column, use something like analytic LAG function.
Study its description including example in documentation (as you did not state exact version, link is for 12.2): https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/LAG.html#GUID-68081CD0-72BE-4C0A-AA6B-AD39FFA7BCF2

Use appropriate formula to get those "like" figures (hopefully "Date" column has DATE data type), as there are in fact
60 days between March 1st, 2020 and January 1st, 2020 (due to leap year),
61 days between May 1st, 2020 and March 1st, 2020 and
214 days between December 1st, 2020 and May 1st, 2020.

[Edit: corrected wrong day count]

[Updated on: Tue, 03 November 2020 18:40]

Report message to a moderator

Re: How to subtract dates between different rows [message #682633 is a reply to message #682630] Tue, 03 November 2020 23:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Sun, 11 October 2020 21:56

Michel Cadot wrote on Sun, 11 October 2020 10:22

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
The test case must be representative of your data and different cases you have to handle.

...
Re: How to subtract dates between different rows [message #682634 is a reply to message #682632] Tue, 03 November 2020 23:43 Go to previous message
talhaparvaiz@yahoo.com
Messages: 14
Registered: October 2020
Junior Member
Perfect... It worked

I am one happy person on planet earth.

Thank you so much
Previous Topic: ORA-02315: incorrect number of arguments for default constructor
Next Topic: Nested JSON_ARRAYAGG --> ORA-00937 - how to create json structure
Goto Forum:
  


Current Time: Thu Mar 28 04:50:29 CDT 2024