 Calculating Average Days (merged) [message #413224] |
Tue, 14 July 2009 17:46  |
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  |
ThomasG
Messages: 3212
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 |
 |
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
