I am trying to calculate the average of the difference between two dates for the returned results. I have already managed to return the timestamp number to a number of days, and this works perfectly fine, but I now need to calculate the average difference in days for those returned results. This is my code:

select 
    SUPPORTCALL.CALL_ID "Call ID",
    PROBLEMTYPE.PROBLEMCATEGORY "Problem Category",
    SUPPORTCALL.CALLDATETIME "Opened Date/Time",
    SUPPORTCALL.RESOLVEDDATETIME "Resolved Date/Time",
    TRUNC(SUPPORTCALL.RESOLVEDDATETIME) - TRUNC(SUPPORTCALL.CALLDATETIME) DTR
    
from
    SUPPORTCALL,
    PROBLEMTYPE
where
    SUPPORTCALL.PROBLEM_ID=PROBLEMTYPE.PROBLEM_ID and
    PROBLEMTYPE.PROBLEMCATEGORY='VMware' and
    RESOLVEDDATETIME is not null

The above returns the following result:

Call ID Problem Category                                   Opened Date/Time             Resolved Date/Time                  DTR
    19 VMware                                             05-MAR-18 15.14.23.100000000 06-APR-18 11.14.23.100000000         32
    18 VMware                                             01-APR-18 11.14.23.100000000 02-APR-18 11.14.23.100000000          1
     8 VMware                                             23-MAR-18 15.14.23.100000000 25-MAR-18 11.14.23.100000000          2
     7 VMware                                             21-MAR-18 11.14.23.100000000 22-MAR-18 11.14.23.100000000          1

Output from code

I am a bit lost on getting the average number of the returned results.

TIA Simon

CodePudding user response：

You can subtract two timestamps to get an INTERVAL DAY TO SECOND data type and then use EXTRACT to get the component parts of the interval to give the time difference. Then to get the average you can use the AVG function. If you want to get all the other data as well then you can use AVG as an analytic function:

select s.CALL_ID "Call ID",
       p.PROBLEMCATEGORY "Problem Category",
       s.CALLDATETIME "Opened Date/Time",
       s.RESOLVEDDATETIME "Resolved Date/Time",
       EXTRACT(DAY FROM s.RESOLVEDDATETIME - s.CALLDATETIME)
         EXTRACT(HOUR FROM s.RESOLVEDDATETIME - s.CALLDATETIME)/24
         EXTRACT(MINUTE FROM s.RESOLVEDDATETIME - s.CALLDATETIME)/24/60
         EXTRACT(SECOND FROM s.RESOLVEDDATETIME - s.CALLDATETIME)/24/60/60 AS diff,
       AVG(
         EXTRACT(DAY FROM s.RESOLVEDDATETIME - s.CALLDATETIME)
           EXTRACT(HOUR FROM s.RESOLVEDDATETIME - s.CALLDATETIME)/24
           EXTRACT(MINUTE FROM s.RESOLVEDDATETIME - s.CALLDATETIME)/24/60
           EXTRACT(SECOND FROM s.RESOLVEDDATETIME - s.CALLDATETIME)/24/60/60  
       ) OVER () AS avg_diff
from   SUPPORTCALL s
       INNER JOIN PROBLEMTYPE p
       ON (s.PROBLEM_ID = p.PROBLEM_ID)
where  p.PROBLEMCATEGORY='VMware'
and    RESOLVEDDATETIME is not null

Which, for the sample data:

CREATE TABLE supportcall (Call_ID, Problem_ID, calldatetime, resolveddatetime ) AS
  SELECT 19, 1, TIMESTAMP '2018-03-05 15:14:23.1', TIMESTAMP '2018-04-06 11:14:23.1' FROM DUAL UNION ALL
  SELECT 18, 1, TIMESTAMP '2018-04-01 11:14:23.1', TIMESTAMP '2018-04-02 11:14:23.1' FROM DUAL UNION ALL
  SELECT  8, 1, TIMESTAMP '2018-03-23 15:14:23.1', TIMESTAMP '2018-03-25 11:14:23.1' FROM DUAL UNION ALL
  SELECT  7, 1, TIMESTAMP '2018-03-21 11:14:23.1', TIMESTAMP '2018-03-22 11:14:23.1' FROM DUAL;

CREATE TABLE problemtype (problem_id, ProblemCategory) AS
SELECT 1, 'VMware' FROM DUAL;

Outputs:

fiddle

CodePudding user response：

Thanks everyone for the comments / replies

After much thought and further research I decided that the assignment question is so ambiguous whether you are to return a specific problem category or show all problem categories and their averages. So I decided to show all categories that have resolved cases and their Average "Days to Resolve" by using GROUP BY, my code ended up like this:

SELECT
    AVG(TRUNC(SUPPORTCALL.RESOLVEDDATETIME) - TRUNC(SUPPORTCALL.CALLDATETIME)) "Avg DTR",
    PROBLEMTYPE.PROBLEMCATEGORY "Problem Category"
FROM
    SUPPORTCALL,
    PROBLEMTYPE
WHERE
    SUPPORTCALL.PROBLEM_ID=PROBLEMTYPE.PROBLEM_ID AND
    SUPPORTCALL.RESOLVEDDATETIME IS NOT NULL
    
GROUP BY PROBLEMTYPE.PROBLEMCATEGORY

Which Resulted in the following output:

    Avg DTR Problem Category                                  
         31 Servers                                           
          3 Office                                            
          9 VMware