Home » SQL & PL/SQL » SQL & PL/SQL » Getting the avarage time pl/sql
Getting the avarage time pl/sql [message #302920] Wed, 27 February 2008 06:15 Go to next message
berlina
Messages: 2
Registered: February 2008
Location: South Africa
Junior Member
Hi Embarassed

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 #302922 is a reply to message #302920] Wed, 27 February 2008 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You have to better explain your case.
For instance, what is a record state?

Maybe just add timestamp field, maybe for each state, maybe other thing...

Regards
Michel
Re: Getting the avarage time pl/sql [message #302924 is a reply to message #302920] Wed, 27 February 2008 06:23 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: Predefined Exception
Next Topic: WITH Clause
Goto Forum:
  


Current Time: Wed Feb 19 16:14:16 CST 2025