Home » SQL & PL/SQL » SQL & PL/SQL » Calculating Average Days (merged)
icon5.gif  Calculating Average Days (merged) [message #413224] Tue, 14 July 2009 17:46 Go to next message
huesland
Messages: 3
Registered: July 2009
Junior Member
Ok, Here is my problem. I have a query that returns the following information:

AUDIT_ID ANALYST_ID EVENT_DATE
402800 3998 1/5/2009
402800 3998 1/7/2009
402800 3998 1/28/2009
402800 3998 1/28/2009
402800 3998 2/2/2009
402800 3998 3/25/2009
402800 3998 4/1/2009
402800 3998 4/23/2009
405000 5140 6/5/2009
405000 5140 6/9/2009
405000 5140 6/16/2009
405000 5140 6/26/2009
405000 5140 7/14/2009
405000 5140 7/14/2009

and I need to manipulate it so it returns this:

AUDIT_ID ANALYST_ID AVERAGE_DAYS
402800 3998 15.42857143
405000 5140 7.8

The AVERAGE_DAYS column is calculated by getting the number of days between all of the dates and then taking a average on that those numbers for instance:

EVENT_DATE DAYS_BETWEEN
1/5/2009 2
1/7/2009 21
1/28/2009 0
1/28/2009 4
2/2/2009 53
3/25/2009 6
4/1/2009 22
4/23/2009

108/7 = 15.42857143

Any help would be greatly appreciated.

Thanks,

Chris
Re: Calculating Average Days [message #413246 is a reply to message #413224] Tue, 14 July 2009 23:59 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Have a look at the "lead" or "lag" function.

That should give you the days between you need in step 1.

With that you could calculate step 2 with sum() and count() and the appropriate GROUP BY.

Re: Calculating Average Days (merged) [message #413249 is a reply to message #413224] Wed, 15 July 2009 00:03 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at LAG/LEAD functions.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: Exam 1z0-147 and autonomous transactions in triggers
Next Topic: sql
Goto Forum:
  


Current Time: Mon Dec 05 18:54:25 CST 2016

Total time taken to generate the page: 0.15066 seconds