Average TAT with Dates from Oracle

[amillia]

I have one heck of a query that I have tried several different approaches to get it to work and I am just about over it. So I come for help. I have to find the turn around time between several dates in my tables. Then I have to find the average TAT for a specific group of aircraft. Depending on what the user chooses. To make this even more fun I am working with Oracle tables and a coldfusion pages which don't like to play well together when it comes to dates. One thing I have tried is this

SELECT Part_number.MFG_PART_NUMBER
, PART_NUMBER.AC_TYPE
, DOC_NUMBER.FLD_DOC_NUMBER
, DOC_NUMBER.PART_NUMBER
, DOC_NUMBER.RETURNED_DATE
, DOC_NUMBER.BLUEPRINT_DATE
, DOC_NUMBER.WO_REL_DTE
, DOC_NUMBER.CSI_SUBMITTED
, DOC_NUMBER.PLANNING_UPDATE
, DOC_NUMBER.COMPLETION_DATE
, DOC_NUMBER.REQUEST_DATE
, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(DOC_NUMBER.COMPLETION_DATE, DOC_NUMBER.REQUEST_DATE)))) AS COMPTAT
, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(DOC_NUMBER.RETURNED_DATE, DOC_NUMBER.CSI_SUBMITTED)))) AS CSITAT
, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(DOC_NUMBER.BLUEPRINT_DATE, DOC_NUMBER.REQUEST_DATE)))) AS INCBPTAT
, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(DOC_NUMBER.BLUEPRINT_DATE, DOC_NUMBER.RETURNED_DATE)))) AS CSIBPTAT
, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(DOC_NUMBER.WO_REL_DTE, DOC_NUMBER.PLANNING_UPDATE)))) AS PLANTAT
FROM DOC_NUMBER RIGHT JOIN PART_NUMBER ON DOC_NUMBER.PART_NUMBER=PART_NUMBER.MFG_PART_NUMBER
WHERE (DOC_NUMBER.COMPLETION_DATE >= TO_DATE('#dateformat(FORM.start, "MMDDYYYY")#','MMDDYYYY')) and (DOC_NUMBER.COMPLETION_DATE <= TO_DATE('#dateformat(FORM.end, "MMDDYYYY")#','MMDDYYYY'))
GROUP BY PART_NUMBER.AC_TYPE
, Part_number.MFG_PART_NUMBER
, DOC_NUMBER.FLD_DOC_NUMBER
, DOC_NUMBER.PART_NUMBER
, DOC_NUMBER.RETURNED_DATE
, DOC_NUMBER.BLUEPRINT_DATE
, DOC_NUMBER.WO_REL_DTE
, DOC_NUMBER.CSI_SUBMITTED
, DOC_NUMBER.PLANNING_UPDATE
, DOC_NUMBER.COMPLETION_DATE
, DOC_NUMBER.REQUEST_DATE

but it doesn't like the SEC_to_time thing it says it is not a valid IDENTIFIER.

Okay so I tried query of query

<cfquery name="cplt_tat" datasource="xxxx">
SELECT Part_number.MFG_PART_NUMBER, PART_NUMBER.AC_TYPE, DOC_NUMBER.CSI_SUBMITTED, DOC_NUMBER.FLD_DOC_NUMBER, DOC_NUMBER.PART_NUMBER, DOC_NUMBER.RETURNED_DATE, DOC_NUMBER.BLUEPRINT_DATE, DOC_NUMBER.WO_REL_DTE, DOC_NUMBER.PLANNING_UPDATE, DOC_NUMBER.COMPLETION_DATE, DOC_NUMBER.REQUEST_DATE
FROM DOC_NUMBER RIGHT JOIN PART_NUMBER ON DOC_NUMBER.PART_NUMBER=PART_NUMBER.MFG_PART_NUMBER
WHERE (DOC_NUMBER.COMPLETION_DATE >= TO_DATE('#dateformat(FORM.start, "MMDDYYYY")#','MMDDYYYY')) and (DOC_NUMBER.COMPLETION_DATE <= TO_DATE('#dateformat(FORM.end, "MMDDYYYY")#','MMDDYYYY'))
</cfquery>
<cfquery dbtype="query" name="cplt2_tat">
SELECT AVG(cplt_tat.COMPLETION_DATE-cplt_tat.REQUEST_DATE) AS COMPTAT,
AVG(cplt_tat.RETURNED_DATE-cplt_tat.CSI_SUBMITTED) AS CSITAT, AVG(cplt_tat.BLUEPRINT_DATE-cplt_tat.REQUEST_DATE) AS INCBPTAT, AVG(cplt_tat.BLUEPRINT_DATE-cplt_tat.RETURNED_DATE) AS CSIBPTAT, avg(cplt_tat.WO_REL_DTE-cplt_tat.PLANNING_UPDATE) AS PLANTAT,
cplt_tat.AC_type
from cplt_tat
GROUP BY cplt_tat.AC_TYPE
</cfquery>

This errors with
The aggregate function [SUM(expression)] cannot operate on an operand of type TIMESTAMP].

I have also tried variations of each but I can’t get it. Can anyone help. Please.