Home » SQL & PL/SQL » SQL & PL/SQL » Calculating Average Days (merged)
Calculating Average Days (merged) Tue, 14 July 2009 17:46
 huesland Messages: 3Registered: 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
 ThomasG Messages: 3202Registered: 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
 Michel Cadot Messages: 65088Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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: Tue Jul 25 16:12:10 CDT 2017

Total time taken to generate the page: 0.12735 seconds