1

I need to calculated time elapsed for each user to perform his part in a transaction.

The sequence is: 0 -1 - B2 or 0-B3 The table looks like the following:

+--------+--------+-------------------+------------------+-----------+
| id     | Trtype |Internal Status    | Timestamp        | User Name |
+--------+--------+-------------------+------------------+-----------+
|2072499 | BSE100 | 0                 | 03/06/2014 13:33 | User1     |
|2072499 | BSE100 | 1                 | 04/06/2014 08:53 | User2     |
|2072499 | BSE100 |B2                 | 03/06/2014 13:33 | User3     |
|2072605 | BSE100 | 0                 | 03/06/2014 13:33 | User1     |
|2072605 | BSE100 |B3                 | 03/06/2014 13:33 | User3     |
.....................

I am using the following sql query to generate the table above:

select h.id, h.trtype, T.DESCRIPTION, hist.internal_status,
 ts.description status_Desc, h.val_dte, h.account_dte, 
 hist.signature_timestamp, usr.login_name as user_name
  from k$transaction_header h, k$transtypes t, k$transaction_history hist, k$usr usr, K$TRANSACTION_STATUS ts
 where h.trtype = t.trtype
 and hist.trans_id = h.id
 and usr.id=hist.signature_usr
 and ts.id=hist.internal_status
 AND H.ACCOUNT_DTE = TO_DATE(&DATE_YYYYMMDD, 'yyyymmdd')
 order by h.id, hist.internal_status
0

Try this. Logically it's a self-join.

WITH transaction_user_parts AS
(
   select h.id, h.trtype, T.DESCRIPTION, hist.internal_status,
   ts.description status_Desc, h.val_dte, h.account_dte, 
   hist.signature_timestamp, usr.login_name as user_name
    from k$transaction_header h, k$transtypes t, k$transaction_history hist, k$usr usr, K$TRANSACTION_STATUS ts
   where h.trtype = t.trtype
   and hist.trans_id = h.id
   and usr.id=hist.signature_usr
   and ts.id=hist.internal_status
   AND H.ACCOUNT_DTE = TO_DATE(&DATE_YYYYMMDD, 'yyyymmdd')
)
SELECT  t1.id
      , t1.internal_status || ' -> ' || t2.internal_status internal_status_change
      , t2.user_name
      , (t2.signature_timestamp - t1.signature_timestamp) time_elapsed
FROM transaction_user_parts t1
JOIN transaction_user_parts t2
  ON (t1.id = t2.id
       AND t1.signature_timestamp <= t2.signature_timestamp
       AND (
             (t1.internal_status='0' AND t2.internal_status='1')
            OR
             (t1.internal_status='1' AND t2.internal_status='B2')
            OR
             (t1.internal_status='0' AND t2.internal_status='B3')
           )
      );
| improve this answer | |
  • I need one more help.... I need to take into consideration the weekend when calculating the time elapsed. I did small modification to the script that you sent before as the following: to_char(to_date('00.00.00','HH24.MI.SS') + (t2.signature_timestamp - t1.signature_timestamp), 'HH24.MI.SS') time_elapsed – Yousef Jun 30 '14 at 6:00
  • Can you elaborate more? Is it that you want it to ignore the weekend? So that task started Friday and completed Monday counts as 1 (business) day? Also, your to_char...to_date code seems problematic; not sure why you're casting and re-casting just to find a time interval. Is this because simply because you want the output formatted as h:m:s? – Joshua Huber Jun 30 '14 at 16:06
  • You are right, I want the output formatted as h:m:s, and the calculation needed for business hours so the weekend should not be counted – Yousef Jul 1 '14 at 16:16
  • Calculating business elapsed time is an entire problem unto itself. See this solution: stackoverflow.com/a/12288804/1680777 – Joshua Huber Jul 2 '14 at 0:52
0

Do you want to calculate it by hand, or is it enough to let the oracle db to do the calculation?

Question: I want to see the response time for my SQL statement and see the run time. What tools does Oracle provide to see how long a query runs?

Answer: For seeing the elapsed time for an individual query, you can see individual query response time in SQL*Plus with the “set timing on” command.

SQL> set timing on;
SQL> select stuff from mytab;

Elapsed: 00:00:02.82

(directly from http://www.dba-oracle.com/t_measure_sql_response_time.htm) may be of some interest in that case.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.