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

2 Answers 2

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')
           )
      );
4
  • 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, 2014 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? Jun 30, 2014 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, 2014 at 16:16
  • Calculating business elapsed time is an entire problem unto itself. See this solution: stackoverflow.com/a/12288804/1680777 Jul 2, 2014 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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