Getting the avarage time pl/sql [message #302920] |
Wed, 27 February 2008 06:15  |
berlina
Messages: 2 Registered: February 2008 Location: South Africa
|
Junior Member |
|
|
Hi
i'm trying to create a query to retrieve the avarage time. We have 5 states and now need to know the duration each record spent in each state.I've managed to get the duration per record per state but now i need to get the avarage duration per state not per record as well in pl/sql.
Berlina
|
|
|
|
Re: Getting the avarage time pl/sql [message #302924 is a reply to message #302920] |
Wed, 27 February 2008 06:23   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
Welcome to the forum. Take some time to read the guidelines. Also if you could post some test case and explain what you have tried so far, where you are stuck and what's your expected output and why ? without these information it will be very difficult for anybody to help you.
In the mean time from your explanation what I think you want is
avg function.
HTH
Regards
Raj
|
|
|
Re: Getting the avarage time pl/sql [message #302940 is a reply to message #302920] |
Wed, 27 February 2008 07:11   |
berlina
Messages: 2 Registered: February 2008 Location: South Africa
|
Junior Member |
|
|
this is the code i'm using to get the duration per ticket per state,but now i'm trying to get the duration per state and not per record. the time was in Julian time so i changed it to normal time . Here's my quesry
select a.*,
case when record_id = next_record
then
case
when state_abbr = 'shr'
then
to_working_duration(to_date('01/01/1970 00:00:00','dd/mm/yyyy hh24:mi:ss')+((event_time*(1/24/60/60))+(2/24)),
to_date('01/01/1970 00:00:00','dd/mm/yyyy hh24:mi:ss')+((next_time*(1/24/60/60))+(2/24)))
when state_abbr = 'ar'
then to_working_duration(to_date('01/01/1970 00:00:00','dd/mm/yyyy hh24:mi:ss')+((event_time*(1/24/60/60))+(2/24)),
to_date('01/01/1970 00:00:00','dd/mm/yyyy hh24:mi:ss')+((next_time*(1/24/60/60))+(2/24)))
when state_abbr = 'rr'
then to_working_duration(to_date('01/01/1970 00:00:00','dd/mm/yyyy hh24:mi:ss')+((event_time*(1/24/60/60))+(2/24)),
to_date('01/01/1970 00:00:00','dd/mm/yyyy hh24:mi:ss')+((next_time*(1/24/60/60))+(2/24)))
when state_abbr = 'rep'
then to_working_duration(to_date('01/01/1970 00:00:00','dd/mm/yyyy hh24:mi:ss')+((event_time*(1/24/60/60))+(2/24)),
to_date('01/01/1970 00:00:00','dd/mm/yyyy hh24:mi:ss')+((next_time*(1/24/60/60))+(2/24)))
when state_abbr = 'cls'
then to_working_duration(to_date('01/01/1970 00:00:00','dd/mm/yyyy hh24:mi:ss')+((event_time*(1/24/60/60))+(2/24)),
to_date('01/01/1970 00:00:00','dd/mm/yyyy hh24:mi:ss')+((next_time*(1/24/60/60))+(2/24)))
end
end difference
from (select ev.record_id,
ev.event_id,
ev.event_time event_time,
lead(ev.event_time) OVER(order by ev.record_id, ev.event_time) as next_time,
lead(record_id) OVER(order by ev.record_id, ev.event_time) as next_record,
st.state_abbr
from tbl_events ev, tbl_states st
where st.state_id = ev.event_param
order by ev.record_id, ev.event_time)a
-
Attachment: duration.xls
(Size: 19.00KB, Downloaded 456 times)
|
|
|
Re: Getting the avarage time pl/sql [message #302948 is a reply to message #302940] |
Wed, 27 February 2008 07:22   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Hi berlina.
You'll be uch more likely to get an answer if you provide a test case. i.e. provide create table and insert scripts to generate a representative set of data. Also, most folk won't/can't open up xls files, just paste it into your post. Finally, use [code]...[/code] tags around any code, and properly format that code.
Cheers
Jim
|
|
|
Re: Getting the avarage time pl/sql [message #302950 is a reply to message #302940] |
Wed, 27 February 2008 07:23  |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
I am sorry to tell you that you have not read the forum guidelines. There is no create table script, insert script, sample output, and what this function is doing to_working_duration. You might have included the above in the .xls file but few people in the forum (including myself) won't download .xls file. Try to convert the file into a .csv and upload it.
Without these information it is difficult to advise you. Try to come up with a very simple test case need not be a very elaborative.
Regards
Raj
P.S : @pablolee i didn't see your post as I was very busy in lecturing.
[Updated on: Wed, 27 February 2008 07:24] Report message to a moderator
|
|
|