1 2 Previous Next 15 Replies Latest reply: Oct 6, 2012 12:20 PM by 966581 RSS

    Some quick assistance - PLSQL Query Question

    966581
      I am trying to write a SQL statement that takes a timestamp value from one field of code and subtracts it from another timestamp. I need to find a way to identify the one field from the other in the equation.

      TASK AHT START_TIMESTAMP
      POI 40 10/1/2012 6:04:25
      TRANS 59 10/1/2012 6:04:44

      So, in the example above, I want to write a SQL which suggests subtracting "TRANS" timestamp from "POI" timestamp to get a value in seconds.

      Can someone suggest how to write this SQL to perform this task?

      Thanks.
        • 1. Re: Some quick assistance - PLSQL Query Question
          812975
          with xx as (
          SELECT 'POI' TASK, 40 AHT, TO_DATE('10/1/2012 6:04:25', 'MM/DD/YYYY HH24:MI:SS') START_TIMESTAMP FROM DUAL UNION ALL
          SELECT 'TRANS' TASK, 59 AHT, TO_DATE('10/1/2012 6:04:44', 'MM/DD/YYYY HH24:MI:SS') START_TIMESTAMP FROM DUAL
          )
          SELECT ROUND((MAX(CASE WHEN TASK = 'TRANS' THEN START_TIMESTAMP END) - MAX(CASE WHEN TASK = 'POI' THEN START_TIMESTAMP END))*24*60*60) AS SEC
          FROM XX;
          • 2. Re: Some quick assistance - PLSQL Query Question
            ranit B
            try this...
            with xx as(
              select 'POI' as TASK,40 as AHT,'10/1/2012 6:04:25' as start_ts from dual union all
              select 'TRANS' as TASK,59 as AHT,'10/1/2012 6:04:44' as start_ts from dual
            )  
            SELECT extract(second FROM 
              ((select to_timestamp(a.start_ts,'dd/mm/yyyy hh:mi:ss') from xx a where a.task = 'TRANS')-
              ((select to_timestamp(a.start_ts,'dd/mm/yyyy hh:mi:ss') from xx a where a.task = 'POI'))) 
            ) as secos FROM dual;
            gives
            19
            HTH.
            Ranit B.
            • 3. Re: Some quick assistance - PLSQL Query Question
              Frank Kulash
              Hi,

              Welcome to the forum!

              Oracle DATEs are much easier to manipulate than TIMESTAMPs. If you can ignore the fractions of a second, then you can do something like:
              SELECT     ( MIN (CASE WHEN task = 'POI'   THEN CAST (start_timestamp AS date) END)
                         - MIN (CASE WHEN task = 'TRANS' THEN CAST (start_timestamp AS date) END)
                   ) * 24 * 60 * 60     AS trans_poi_seconds
              FROM     table_x
              ;
              When you subtract one DATE from another, the result is a NUMBER, the difference in days. To get the difference in seconds, multiply the number of days by the number of seconds in a day.

              Instead of MIN (CASE ...), you can do a self-join.
              • 4. Re: Some quick assistance - PLSQL Query Question
                ranit B
                SaadL wrote:
                with xx as (
                SELECT 'POI' TASK, 40 AHT, TO_DATE('10/1/2012 6:04:25', 'MM/DD/YYYY HH24:MI:SS') START_TIMESTAMP FROM DUAL UNION ALL
                SELECT 'TRANS' TASK, 59 AHT, TO_DATE('10/1/2012 6:04:44', 'MM/DD/YYYY HH24:MI:SS') START_TIMESTAMP FROM DUAL
                )
                SELECT ROUND((MAX(CASE WHEN TASK = 'TRANS' THEN START_TIMESTAMP END) - MAX(CASE WHEN TASK = 'POI' THEN START_TIMESTAMP END))*24*60*60) AS SEC
                FROM XX;
                I guess CASE statement twice along with ROUND and MAX will be tooooo costly.
                • 5. Re: Some quick assistance - PLSQL Query Question
                  ranit B
                  Frank Kulash wrote:

                  Oracle DATEs are much easier to manipulate than TIMESTAMPs. If you can ignore the fractions of a second, then you can do something like:
                  SELECT     ( MIN (CASE WHEN task = 'POI'   THEN CAST (start_timestamp AS date) END)
                       - MIN (CASE WHEN task = 'TRANS' THEN CAST (start_timestamp AS date) END)
                       ) * 24 * 60 * 60     AS trans_poi_seconds
                  FROM     table_x
                  ;
                  Don't you think cost priority is more than a minor manipulation effort?
                  • 6. Re: Some quick assistance - PLSQL Query Question
                    Frank Kulash
                    Hi,
                    ranit B wrote:
                    Don't you think cost priority is more than a minor manipulation effort?
                    Sure, sometimes there's a tradeoff between speed and simplicity. Regular expressions are a common example.

                    In this case, is there such a tradeoff? I don't think so, though, of course, what someone considers simple and easy to maintaIn is rather subjective. I find CAST easier to uderstand than TO_TIMESTAMP and TO_CHAR, and I think other people do, too. My guess is that CAST is more efficient than either TO_TIMESTAMP or TO_CHAR, let alone both of them together. But don't rely on my guess; try it and see.

                    Edited by: Frank Kulash on Oct 6, 2012 10:19 AM
                    • 7. Re: Some quick assistance - PLSQL Query Question
                      ranit B
                      Frank Kulash wrote:
                      Hi,
                      ranit B wrote:
                      Don't you think cost priority is more than a minor manipulation effort?
                      No. My guess is that CAST is more efficient than either TO_TIMESTAMP or TO_CHAR, let alone both of them together. But don't rely on my guess; try it and see.
                      1. Efficient?? In terms of what?
                      2. What about the CASE WHEN clause? This is somewhat close to a Pl/Sql's If-Else... don't you think now it's getting way to costly?

                      Please rectify me if i'm wrong.

                      Ranit B.
                      • 8. Re: Some quick assistance - PLSQL Query Question
                        Frank Kulash
                        Hi,
                        ranit B wrote:
                        1. Efficient?? In terms of what?
                        Execution time.
                        2. What about the CASE WHEN clause? This is somewhat close to a Pl/Sql's If-Else... don't you think now it's getting way to costly?
                        No. Using CASE, you can get both values with a single table scan. Otherwise, you can need two separate sub-queries to get the two different values. If the problem is really as simple as OP posted it, then those 2 separate queries can be really fast, but if the problem is that simple, it will be difficult to notice a difference in speed whatever you do.
                        • 9. Re: Some quick assistance - PLSQL Query Question
                          ranit B
                          ok. Then cool... But i still prefer my query. ;-)

                          Frank,
                          I'm your big fan and love to learn new stuff whatever you share in this forum.

                          Thanks for nourishing our grey cells.
                          Ranit B.
                          • 10. Re: Some quick assistance - PLSQL Query Question
                            966581
                            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"

                            The results from the SQL produce the following:

                            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?
                            • 11. Re: Some quick assistance - PLSQL Query Question
                              ranit B
                              why don't you try the query which i gave above... i guess it'll meet your requirement.

                              If not, please let me know why. I'll fix.
                              Ranit B.
                              • 12. Re: Some quick assistance - PLSQL Query Question
                                966581
                                Not sure how to enter it within the select statement
                                • 13. Re: Some quick assistance - PLSQL Query Question
                                  ranit B
                                  with xx as(
                                    select 'POI' as value_name,40 as AHT,'10/1/2012 6:04:25' as create_date from dual union all
                                    select 'TRANS' as value_name,59 as AHT,'10/1/2012 6:04:44' as create_date from dual
                                  )  
                                  this is nothing, just assume it like an inline view(or your dummy table data).
                                  Google it - WITH clause (also called Subquery Factoring )
                                  SELECT extract(second FROM 
                                    ((select to_timestamp(a.create_date,'dd/mm/yyyy hh:mi:ss') from xx a where a.value_name= 'TRANS')-
                                    ((select to_timestamp(a.create_date,'dd/mm/yyyy hh:mi:ss') from xx a where a.value_name = 'POI'))) 
                                  ) as secos FROM dual;
                                  XX - is your actual table.

                                  Hope you understand now.

                                  Ranit B.
                                  • 14. Re: Some quick assistance - PLSQL Query Question
                                    966581
                                    In your explanation below, why couldn't I use;

                                    (case when task = 'POI' then CAST (start_timestamp as date1)
                                    when task = 'TRANS' then CAST (start_timestamp as date2)
                                    then 'date1' - 'date2' as 'POI_AHT' else 'NULL_AHT' end,


                                    Is this possible?

                                    I just want the value of the POI start_timestamp less TRANS start-timestamp

                                    Thanks.
                                    1 2 Previous Next