1 2 Previous Next 15 Replies Latest reply: Oct 6, 2012 12:20 PM by 966581 Go to original post RSS
      • 15. Re: Some quick assistance - PLSQL Query Question
        Frank Kulash
        963578 wrote:
        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",
        create_date "CREATE_DATE",
        last_updated_date "UPDATED_DATE"
        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.
        It also risks a run-time error, because you can't subtract strings.
        The same problem exists for greeting_aht.
        The results from the SQL produce the following:

        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?
        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.
        1 2 Previous Next