Determine the difference in days between two dates ? 1

[gtb12314]

Hi Everyone,

How to determine the date difference between the current record and the next record?

For example, for every employee in DEPTNO 10, I want to determine the number of days between the day they were hired and the day the next employee was hired.

Below is a sample of employees in dept no 10:


DEPTNO ENAME HIREDATE
------ ------ -----------
10 CLARK 09-JUN-1981
10 KING 17-NOV-1981
10 MILLER 23-JAN-1982

I am using oracle9i, Please anybody suggest how to accomplish it with a brief explanation.

Any help is greatly appreciated.

Thanks in advance

 

[SantaMufasa]

GTB,

If I didn't know that you had been a Tek-Tips member for well over a year, and an on-going participant in this forum, I'd say this was a classic classroom assignment. <grin>

But since I know otherwise, here is a solution using Oracle's Analytic Functions (using my copy of Oracle University's Summit Sporting Goods data set rather than Oracle's EMP/DEPT tables):

col x heading "Days Since|Previous|Hire"
select dept_id, last_name, start_date,
       start_date-(lag(start_date,1)
                   over (partition by dept_id order by start_date)) x
  from s_emp
 where dept_id = 41
/


                                               Days Since
                                                 Previous
   DEPT_ID LAST_NAME                 START_DAT       Hire
---------- ------------------------- --------- ----------
        41 Ngao                      08-MAR-90
        41 Smith                     08-MAR-90          0
        41 Urguhart                  18-JAN-91        316
        41 Maduro                    07-FEB-92        385

If you need more mentoring with Analytics, we'll be glad to help, but first please read and try out the excellent explanations of Oracle Analytics by Shouvik Basu.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Sorry, GTB,

When I re-read your original specifications, I realize now tht I went the wrong direction. Here is revised code to go to direction your wanted:

col x heading "Days Until|Next|Hire"
select dept_id, last_name, start_date,
       (lead(start_date,1)
             over (partition by dept_id order by start_date))
       -start_date x
  from s_emp
 where dept_id = 41
/

                                               Days Until
                                                     Next
   DEPT_ID LAST_NAME                 START_DAT       Hire
---------- ------------------------- --------- ----------
        41 Ngao                      08-MAR-90          0
        41 Smith                     08-MAR-90        316
        41 Urguhart                  18-JAN-91        385
        41 Maduro                    07-FEB-92

Let us know if this is more like what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Also, GTB, remind me to carefully re-read posts before I hit the [Submit] button so that I don't sound as though English is a second language for me.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

GTB, Testing...1...2...3...Are you there?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[MikeDevenney]

GTB has left the building.