Newbie needs help with averaging time

I'm working on a shell script that pulls out data from informix with a few sql lines.. Trying to get a report put togeather for our help_desk. One of the things I have to do with the data is to take a start date and time, and an end date and end time for all priority 1 calls. Then take the total time for all the priority 1 calls and get an average time that it's taking us to take care of them. I'm not sure if Perl is the way to go, or doing it all via sql would work. I'm by no means a guru at this stuff! I have it retrieving the start and end date and time, just need help on getting the total time for each priority 1 call and then the average for them all. the date and time fields they used are report_dt report_tm resolv_dt resolv_tm Oh ya... The time is in 24hr format. Can I get the output I need with pure SQL? If so, How? If more info is needed, or a copy of the script I wrote so far, I can post it.. Thank you ahead of time!! Tony!

Tony wrote: > I'm working on a shell script that pulls out data from informix with a > few sql lines.. Trying to get a report put togeather for our > help_desk. > > One of the things I have to do with the data is to take a start date > and time, and an end date and end time for all priority 1 calls. > > Then take the total time for all the priority 1 calls and get an > average time that it's taking us to take care of them. > > I'm not sure if Perl is the way to go, or doing it all via sql would > work. I'm by no means a guru at this stuff! > > I have it retrieving the start and end date and time, just need help > on getting the total time for each priority 1 call and then the > average for them all. > > the date and time fields they used are > report_dt > report_tm > resolv_dt > resolv_tm > > Oh ya... The time is in 24hr format. > > Can I get the output I need with pure SQL? Yes. > If so, How? Painfully. Since your report and resolve times are split into date and time components, the first step required is assembling a single datetime value representing the actual start and end date/time. That's not trivial, especially if the time portions are stored in a DATETIME HOUR TO MINUTE (or SECOND; it doesn't really matter), rather than as an INTERVAL HOUR TO MINUTE. Convert DATE to DATETIME YEAR TO DAY: EXTEND(report_dt, YEAR TO DAY) Convert DATETIME HOUR TO MINUTE to INTERVAL HOUR TO MINUTE: report_tm - DATETIME(00:00) HOUR TO MINUTE Combine DATETIME YEAR TO DAY with INTERVAL HOUR TO MINUTE: EXTEND(dt_ytod, YEAR TO MINUTE) + i_htom Hence, your total first step for report_tm, report_dt is: EXTEND(EXTEND(report_dt, YEAR TO DAY), YEAR TO MINUTE) + (report_tm - DATETIME(00:00) HOUR TO MINUTE Now you want the actual difference between the two datetimes in minutes: INTERVAL(0) MINUTE(9) TO MINUTE + (dt_yr_sec1 - dt_yr_sec2) That gives you the individual call closure times in minutes. Obviously, you should be able to average these quite easily :-) No, I did not say it was easy. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>

I'm seeing where you're going, but the implimentation of it confuses me. What I have so far is... database help_desk; select report_dt, report_tm, resolv_dt, resolv_tm, ticket_no, facility, operator, status, assn_to, priority from help where report_dt between "01202000" and "02022000" AND ticket_no >= "00010100" (omitted some "Group By" statements that seems not to be important for the question) the resulting text looks like... report_dt 01/21/2000 report_tm 15:43 resolv_dt 01/24/2000 resolv_tm 12:39 ticket_no 00010100 facility ADMIN operator JFE status C assn_to systems priority 2 It's rather humbling to get what seems to be a helpful reply with pretty much exactly what to enter to get it to do what I need, and still not be able to get it.. Pfft! How would I tell if the start and end date/time portions are stored in a "DATETIME HOUR TO MINUTE (or SECOND; it doesn't really matter), rather than as an INTERVAL HOUR TO MINUTE." After payday I'll be picking up a book or two from Borders or wherever... Can you suggest any that are simple enough for an sql newbie, but no so simple that one can finish off the book and not know more than a simple select statement like I have above?) btw: Thanks for the help! Tony! On Mon, 07 Feb 2000 06:56:35 GMT, Jonathan Leffler <jleffler@earthlink.net> wrote: > > >Tony wrote: > >> I'm working on a shell script that pulls out data from informix with a >> few sql lines.. Trying to get a report put togeather for our >> help_desk. >> >> One of the things I have to do with the data is to take a start date >> and time, and an end date and end time for all priority 1 calls. >> >> Then take the total time for all the priority 1 calls and get an >> average time that it's taking us to take care of them. >> >> I'm not sure if Perl is the way to go, or doing it all via sql would >> work. I'm by no means a guru at this stuff! >> >> I have it retrieving the start and end date and time, just need help >> on getting the total time for each priority 1 call and then the >> average for them all. >> >> the date and time fields they used are >> report_dt >> report_tm >> resolv_dt >> resolv_tm >> >> Oh ya... The time is in 24hr format. >> >> Can I get the output I need with pure SQL? > >Yes. > >> If so, How? > >Painfully. > >Since your report and resolve times are split into date and time >components, the >first step required is assembling a single datetime value representing the >actual >start and end date/time. That's not trivial, especially if the time >portions are stored >in a DATETIME HOUR TO MINUTE (or SECOND; it doesn't really matter), >rather than as an INTERVAL HOUR TO MINUTE. > >Convert DATE to DATETIME YEAR TO DAY: > > EXTEND(report_dt, YEAR TO DAY) > >Convert DATETIME HOUR TO MINUTE to INTERVAL HOUR TO MINUTE: > > report_tm - DATETIME(00:00) HOUR TO MINUTE > >Combine DATETIME YEAR TO DAY with INTERVAL HOUR TO MINUTE: > > EXTEND(dt_ytod, YEAR TO MINUTE) + i_htom > >Hence, your total first step for report_tm, report_dt is: > > EXTEND(EXTEND(report_dt, YEAR TO DAY), YEAR TO MINUTE) + > (report_tm - DATETIME(00:00) HOUR TO MINUTE > >Now you want the actual difference between the two datetimes in minutes: > > INTERVAL(0) MINUTE(9) TO MINUTE + (dt_yr_sec1 - dt_yr_sec2) > >That gives you the individual call closure times in minutes. Obviously, >you should be able to average these quite easily :-) > >No, I did not say it was easy.

Well,if it's any consolation, I felt compelled to check that what I'd said worked. It did, but I needed to check. "Tony!" wrote: > I'm seeing where you're going, but the implimentation of it confuses > me. > > What I have so far is... > > database help_desk; > select report_dt, report_tm, resolv_dt, resolv_tm, ticket_no, > facility, > operator, status, assn_to, priority > from help where report_dt between "01202000" > and "02022000" AND ticket_no >= "00010100"> > (omitted some "Group By" statements that seems not to be important for > the question) > > the resulting text looks like... > > report_dt 01/21/2000 > report_tm 15:43 > resolv_dt 01/24/2000 > resolv_tm 12:39 > ticket_no 00010100 > facility ADMIN > operator JFE > status C > assn_to systems > priority 2 > > It's rather humbling to get what seems to be a helpful reply with > pretty much exactly what to enter to get it to do what I need, and > still not be able to get it.. Pfft! > > How would I tell if the start and end date/time portions are stored in > a "DATETIME HOUR TO MINUTE (or SECOND; it doesn't really matter), > rather than as an INTERVAL HOUR TO MINUTE." > > After payday I'll be picking up a book or two from Borders or > wherever... Can you suggest any that are simple enough for an sql > newbie, but no so simple that one can finish off the book and not know > more than a simple select statement like I have above?) > > btw: Thanks for the help! > > Tony! > > On Mon, 07 Feb 2000 06:56:35 GMT, Jonathan Leffler > <jleffler@earthlink.net> wrote: > > > >Tony wrote: > >> One of the things I have to do with the data is to take a start date > >> and time, and an end date and end time for all priority 1 calls. > >> Then take the total time for all the priority 1 calls and get an > >> average time that it's taking us to take care of them. > >> > >> [...] just need help on getting the total time for each priority 1 call > and then the > >> average for them all. > >> > >> the date and time fields they used are > >> report_dt > >> report_tm > >> resolv_dt > >> resolv_tm > >> > >> Oh ya... The time is in 24hr format. > >> > >> Can I get the output I need with pure SQL? > > > >Yes. > > > >> If so, How? > > > >Painfully. > > > >Since your report and resolve times are split into date and time > >components, the > >first step required is assembling a single datetime value representing the > >actual > >start and end date/time. That's not trivial, especially if the time > >portions are stored > >in a DATETIME HOUR TO MINUTE (or SECOND; it doesn't really matter), > >rather than as an INTERVAL HOUR TO MINUTE. > > > >Convert DATE to DATETIME YEAR TO DAY: > > > > EXTEND(report_dt, YEAR TO DAY) > > > >Convert DATETIME HOUR TO MINUTE to INTERVAL HOUR TO MINUTE: > > > > report_tm - DATETIME(00:00) HOUR TO MINUTE > > > >Combine DATETIME YEAR TO DAY with INTERVAL HOUR TO MINUTE: > > > > EXTEND(dt_ytod, YEAR TO MINUTE) + i_htom > > > >Hence, your total first step for report_tm, report_dt is: > > > > EXTEND(EXTEND(report_dt, YEAR TO DAY), YEAR TO MINUTE) + > > (report_tm - DATETIME(00:00) HOUR TO MINUTE > > > >Now you want the actual difference between the two datetimes in minutes: > > > > INTERVAL(0) MINUTE(9) TO MINUTE + (dt_yr_sec1 - dt_yr_sec2) > > > >That gives you the individual call closure times in minutes. Obviously, > >you should be able to average these quite easily :-) > > > >No, I did not say it was easy. select report_dt, report_tm, resolv_dt, resolv_tm, ticket_no, facility, operator, status, assn_to, priority, INTERVAL(0) MINUTE(9) TO MINUTE + ( (EXTEND(EXTEND(resolv_dt, YEAR TO DAY), YEAR TO MINUTE) + (resolv_tm - DATETIME(00:00) HOUR TO MINUTE) - (EXTEND(EXTEND(report_dt, YEAR TO DAY), YEAR TO MINUTE) + (report_tm - DATETIME(00:00) HOUR TO MINUTE)) elapsed_mins from help where report_dt between "01202000" and "02022000" AND ticket_no >= "00010100" You find out your data types by looking at the schema (DB-Access or DB-Schema). You might be able to short circuit the EXTEND of an EXTEND value by going direct from DATE to DATETIME YEAR TO MINUTE. That gives you the elapsed time in minutes per call; the aggregation of those values can be done as you choose. Save the raw data into a temp table and then process that. Or do the aggregation direct on that goddamawful (if it doesn't get censored) expression. I'd probably use the temp tables -- it would be easier to verify intermediate results. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Thank you again Jon!!! You've been a big help to me! Thanks for your time! Tony! On Wed, 09 Feb 2000 05:13:41 GMT, Jonathan Leffler <jleffler@earthlink.net> wrote: >Well,if it's any consolation, I felt compelled to check that what I'd said >worked. >It did, but I needed to check. >> >select report_dt, report_tm, resolv_dt, resolv_tm, ticket_no, >facility, >operator, status, assn_to, priority,> > INTERVAL(0) MINUTE(9) TO MINUTE + ( >(EXTEND(EXTEND(resolv_dt, YEAR TO DAY), YEAR TO MINUTE) + > (resolv_tm - DATETIME(00:00) HOUR TO MINUTE) - >(EXTEND(EXTEND(report_dt, YEAR TO DAY), YEAR TO MINUTE) + > (report_tm - DATETIME(00:00) HOUR TO MINUTE)) elapsed_mins ^^ I got a syntax error when originally copying this in to an filename.sql. Added 2 ")"'s and it seems to be working now! Very Coooool! >from help where report_dt between "01202000" >and "02022000" AND ticket_no >= "00010100" > >You find out your data types by looking at the schema (DB-Access or >DB-Schema). You might be able to short circuit the EXTEND of an >EXTEND value by going direct from DATE to DATETIME YEAR TO MINUTE. > >That gives you the elapsed time in minutes per call; the aggregation >of those values can be done as you choose. Save the raw data into >a temp table and then process that. Or do the aggregation direct on >that goddamawful (if it doesn't get censored) expression. > >I'd probably use the temp tables -- it would be easier to verify >intermediate results.