| How to subtract dates between different rows [message #682630] |
Tue, 03 November 2020 16:57  |
 |
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 #682632 is a reply to message #682630] |
Tue, 03 November 2020 18:12  |
flyboy
Messages: 1901
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 |
 |
Michel Cadot
Messages: 68421
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.
...
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
