963578 wrote:Computing closing_aht, that assumes that last_updated_date is on the same calendar day as end_date. If last_updated_date is 23:59 on Oct. 5, 2012, and end_date is 00:01 on October 6, 2012 (120 seconds later), that will return -86280, not +120. In fact, it will still return -86280 if last_updated_date is 23:59 on any day, and end_date is 00:01 on any day, before, same as or after last_updated_date, even if they are years apart.
Ok. Maybe this will help us further.....
In my select SQL statement, I use the following:
to_char(create_date,'sssss') - to_char(start_date,'sssss') "GREETING_AHT",
to_char(to_char(end_date,'sssss') - to_char(last_updated_date,'sssss')) "CLOSING_AHT",
The results from the SQL produce the following:What's wrong with the solutions already given? Post your code, and CREATE TABLE and INSERT statements for a little sample data. Point out where your query gets the wrong results, and say what the right results are in those places.
VALUE_NAME TOTAL_TALK_TIME GREETING_AH CLOSING_AHT CREATE_DATE
POI Search 69 40 4 10/1/2012 6:04:25 AM
Transmit Route 69 59 4 10/1/2012 6:04:44 AM
All I need now is to write a quick SQL statement which takes the "CREATE_DATE" from "Value_Name" = 'POI_SEARCH' and subtracts this timestamp from "Value_Name" = 'TRANSMIT Route' - using the to_char(create_date,'sssss') definition.
sorry for any confusion.
Can someone assist me?