2 Replies Latest reply on May 14, 2015 1:59 AM by rp0428

    Case statement to calculate average age of work order

    2949867

      This is what I have so far for SQL Query.  I have attached Excel spreadsheet.  I need to come up with AGE (column M) results.  I have not sure how I can write this query in SQL to come up with same answer as excel.

       

      SELECT WO.WONUM, WO.ACTFINISH, WO.REPORTDATE,

      CASE

      WHEN WO.ACTFINISH IS NULL THEN 0

      ELSE TRUNC(TO_DATE(WO.ACTFINISH,'DD-MON-YY HH24:MI:SS') -  TO_DATE(WO.REPORTDATE,'DD-MON-YY HH24:MI:SS'))

      END AS "DATE MATH"

      FROM WORKORDER WO

      WHERE WO.STATUS !='COMP'

      AND WO.ACTFINISH IS NOT NULL;

       

      Thank you

      R