Home » SQL & PL/SQL » SQL & PL/SQL » Average date/time
Average date/time [message #8644] Tue, 16 September 2003 13:04 Go to next message
Jean
Messages: 16
Registered: September 1999
Junior Member
I have 2 tables and need to get the average response time in seconds to a call by week day. Table 1 contains the date/time a call is received. Table 2 contains the responses to the call.

Table 1
Event Call
1 08/01/2003 07:45AM
2 08/01/2003 07:55AM
3 08/01/2003 08:15AM

Table 2
Event Responder Response
1 A 08/01/2003 07:46AM
1 B 08/01/2003 07:47AM
1 C 08/01/2003 07:59AM
2 A 08/01/2003 07:56AM
2 B 08/01/2003 07:56AM
3 A 08/02/2003 08:15AM
3 B 08/02/2003 08:16AM

How do I get the average response time for all events on 8/1/2003 using the least time for each event? (i.e. Event 1 responder A, event 2, responder A or B, event 3 responder A). Note: Response for event 3 is on the next day. I need this average calculated for each day of the week.

Sample Code does not provide correct average:
SELECT count(*) Total,
TRUNC (AVG (ROUND (60*(60*(24*(response - call) - ROUND(24*(response - call))) -
ROUND (60*(24*(response - call) - ROUND(24*(response - call))) - 0.5 ))))) AVGTime,
to_char(call, 'DY') WDay
FROM Table1, Table 2
WHERE response IS NOT NULL
GROUP BY to_char(i.report_dttm, 'DY')

Any help is appreciated

Report message to a moderator

Re: Average date/time [message #8645 is a reply to message #8644] Tue, 16 September 2003 13:37 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Jean,
SQL> CREATE TABLE t1 (event NUMBER, call DATE);
SQL> CREATE TABLE t2 (event NUMBER, responder VARCHAR2(1), response DATE);
SQL> INSERT INTO t1 VALUES (1,TO_DATE('08/01/2003 07:45AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t1 VALUES (2,TO_DATE('08/01/2003 07:55AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t1 VALUES (3,TO_DATE('08/01/2003 08:15AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (1,'A',TO_DATE('08/01/2003 07:46AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (1,'B',TO_DATE('08/01/2003 07:47AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (1,'C',TO_DATE('08/01/2003 07:59AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (2,'A',TO_DATE('08/01/2003 07:56AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (2,'B',TO_DATE('08/01/2003 07:56AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (3,'A',TO_DATE('08/02/2003 08:15AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (3,'B',TO_DATE('08/02/2003 08:16AM','MM/DD/YYYY HH12:MIAM'));
SQL> COMMIT;
  
Commit complete.
This query just shows the number of minutes for each event:
SQL> SELECT DISTINCT t2.event
  2  ,      ((t2.response - t1.call) * 24 * 60) minutes
  3  FROM   t1
  4  ,      t2
  5  WHERE  t1.event = t2.event
  6  AND    TRUNC(t1.call) = TO_DATE('20030801','YYYYMMDD')
  7  AND    t2.response = (SELECT MIN(t2_a.response)
  8                        FROM   t2  t2_a
  9                        WHERE  t2_a.event = t2.event)
 10  /
  
     EVENT    MINUTES
---------- ----------
         1          1
         2          1
         3       1440
And this query, surrounding the previous one, computes the average response time across all events:
SQL> SELECT AVG(minutes)
  2  FROM  (SELECT DISTINCT t2.event
  3         ,      ((t2.response - t1.call) * 24 * 60) minutes
  4         FROM   t1
  5         ,      t2
  6         WHERE  t1.event = t2.event
  7         AND    TRUNC(t1.call) = TO_DATE('20030801','YYYYMMDD')
  8         AND    t2.response = (SELECT MIN(t2_a.response)
  9                               FROM   t2  t2_a
 10                               WHERE  t2_a.event = t2.event))
 11  /
  
AVG(MINUTES)
------------
  480.666667
  
SQL>
HTH, Jean.

A.

Report message to a moderator

Re: Average date/time [message #8678 is a reply to message #8645] Thu, 18 September 2003 05:32 Go to previous messageGo to next message
Jean
Messages: 16
Registered: September 1999
Junior Member
Thanks for the solution Art. This works well for me.

Report message to a moderator

Re: Average date/time [message #8688 is a reply to message #8645] Thu, 18 September 2003 12:55 Go to previous messageGo to next message
Jean
Messages: 16
Registered: September 1999
Junior Member
After adding TYPE to t1, need to compute average per type (i.e. avg for type FE and avg for type FF.

I'm trying to get:

count(FE) avg(FE) count(FF) avg(FF) count(both) avg(both)
-----------------------------------------------------
2 ?? 2 ?? 4 ??

Thanks
=======================================================
SQL> CREATE TABLE t1 (event NUMBER, call DATE, type VARCHAR2);
SQL> CREATE TABLE t2 (event NUMBER, responder VARCHAR2(1), response DATE);
SQL> INSERT INTO t1 VALUES (1,TO_DATE('08/01/2003 07:45AM','MM/DD/YYYY HH12:MIAM'),FE1);
SQL> INSERT INTO t1 VALUES (2,TO_DATE('08/01/2003 07:55AM','MM/DD/YYYY HH12:MIAM'),FE2);
SQL> INSERT INTO t1 VALUES (3,TO_DATE('08/01/2003 08:15AM','MM/DD/YYYY HH12:MIAM'),FF1);
SQL> INSERT INTO t1 VALUES (4,TO_DATE('08/01/2003 03:00PM','MM/DD/YYYY HH12:MIAM'),FF2);
SQL> INSERT INTO t2 VALUES (1,'A',TO_DATE('08/01/2003 07:46AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (1,'B',TO_DATE('08/01/2003 07:47AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (1,'C',TO_DATE('08/01/2003 07:59AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (2,'A',TO_DATE('08/01/2003 07:56AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (2,'B',TO_DATE('08/01/2003 07:56AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (3,'A',TO_DATE('08/02/2003 08:15AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (3,'B',TO_DATE('08/02/2003 08:16AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (4,'A',TO_DATE('08/01/2003 03:16PM','MM/DD/YYYY HH12:MIAM'));

SQL> COMMIT; Commit complete.

SQL> SELECT AVG(minutes)
2 FROM (SELECT DISTINCT t2.event
3 , ((t2.response - t1.call) * 24 * 60) minutes
4 FROM t1
5 , t2
6 WHERE t1.event = t2.event
7 AND TRUNC(t1.call) = TO_DATE('20030801','YYYYMMDD')
8 AND t2.response = (SELECT MIN(t2_a.response)
9 FROM t2 t2_a
10 WHERE t2_a.event = t2.event))
11 /

AVG(MINUTES)
------------
480.666667

Report message to a moderator

Re: Average date/time [message #8690 is a reply to message #8688] Thu, 18 September 2003 14:54 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
 
SQL> CREATE TABLE t1 (event NUMBER, call DATE, type VARCHAR2(4));
SQL> CREATE TABLE t2 (event NUMBER, responder VARCHAR2(1), response DATE);
SQL> INSERT INTO t1 VALUES (1,TO_DATE('08/01/2003 07:45AM','MM/DD/YYYY HH12:MIAM'),'FE');
SQL> INSERT INTO t1 VALUES (2,TO_DATE('08/01/2003 07:55AM','MM/DD/YYYY HH12:MIAM'),'FE');
SQL> INSERT INTO t1 VALUES (3,TO_DATE('08/01/2003 08:15AM','MM/DD/YYYY HH12:MIAM'),'FF');
SQL> INSERT INTO t1 VALUES (4,TO_DATE('08/01/2003 03:00PM','MM/DD/YYYY HH12:MIAM'),'FF');
SQL> INSERT INTO t2 VALUES (1,'A',TO_DATE('08/01/2003 07:46AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (1,'B',TO_DATE('08/01/2003 07:47AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (1,'C',TO_DATE('08/01/2003 07:59AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (2,'A',TO_DATE('08/01/2003 07:56AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (2,'B',TO_DATE('08/01/2003 07:56AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (3,'A',TO_DATE('08/02/2003 08:15AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (3,'B',TO_DATE('08/02/2003 08:16AM','MM/DD/YYYY HH12:MIAM'));
SQL> INSERT INTO t2 VALUES (4,'A',TO_DATE('08/01/2003 03:16PM','MM/DD/YYYY HH12:MIAM'));
SQL> COMMIT;
  
Commit complete.
  
SQL> SELECT SUM(DECODE(agg.type,'FE',1,0))               "count(FE)"
  2  ,      AVG(DECODE(agg.type,'FE',agg.diff_in_mins))  "avg(FE)"
  3  ,      SUM(DECODE(agg.type,'FF',1,0))               "count(FF)"
  4  ,      AVG(DECODE(agg.type,'FF',agg.diff_in_mins))  "avg(FF)"
  5  ,      COUNT(*)                                     "count(both)"
  6  ,      AVG(agg.diff_in_mins)                        "avg(both)"
  7  FROM  (SELECT 
  8         DISTINCT t1.event
  9         ,        t1.type
 10         ,       (FIRST_VALUE(t2.response)
 11                  OVER (PARTITION BY t2.event
 12                        ORDER BY t2.response)
 13                  -
 14                  t1.call) * 24 * 60 diff_in_mins
 15         FROM     t1
 16         ,        t2
 17         WHERE    t2.event = t1.event) agg
 18  /
  
 count(FE)    avg(FE)  count(FF)    avg(FF) count(both)  avg(both)
---------- ---------- ---------- ---------- ----------- ----------
         2          1          2        728           4      364.5
  
SQL>
HTH,

A.

Report message to a moderator

Re: Average date/time [message #8714 is a reply to message #8690] Fri, 19 September 2003 14:23 Go to previous message
Jean
Messages: 16
Registered: September 1999
Junior Member
Thanks for your help! I sure have a lot to learn.

Report message to a moderator

Previous Topic: DB_Link inside Trigger Breaking
Next Topic: Convert Rows to a List
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 18:38:11 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.