Date difference between rows

Question

I have the following SQL query:

SELECT t.trans_id, t.business_process_id, tsp.status, tsp.timestamp
  FROM tran_stat_p tsp, tran t
 WHERE t.trans_id = tsp.trans_id
       AND tsp.timestamp BETWEEN '1-jan-2008' AND SYSDATE
       AND t.business_process_id = 'ABC01'

It outputs data like this:

trans_ID business_process_id status timestamp
14444400 ABC01 F 6/5/2008 12:37:36 PM
14444400 ABC01 W 6/6/2008 1:37:36 PM
14444400 ABC01 S 6/7/2008 2:37:36 PM
14444400 ABC01 P 6/8/2008 3:37:36 PM
14444401 ABC01 F 6/5/2008 12:37:36 PM
14444401 ABC01 W 6/6/2008 1:37:36 PM
14444401 ABC01 S 6/7/2008 2:37:36 PM
14444401 ABC01 P 6/8/2008 3:37:36 PM

In addition to the above, I'd like to add a column which calculates the time difference (in days) between statuses W&F, S&W, P&S for every unique trans_id.

The idea is to figure out how long transactions are sitting in the various statuses before they are finally processed to status "P". The life cycle of a transaction is in the following order -> F -> W -> S -> P. Where F is the first status, and P is the final status.

Can anyone help? Thanks in advance.

Answer 1

You can use LEAD to retrieve the next timestamp value and calculated the time left in every status (F, W and S) and TRUNC to calculated days between as an integer :

SELECT t."trans_ID", t."business_process_id", tsp."status", tsp."timestamp", 
       LEAD("timestamp", 1) OVER (
                  PARTITION BY tsp."trans_ID" 
                  ORDER BY "timestamp") AS "next_timestamp",
       trunc(LEAD("timestamp", 1) OVER (
                        PARTITION BY tsp."trans_ID" 
                        ORDER BY "timestamp")) - trunc(tsp."timestamp") as "Days"
  FROM tran t
INNER JOIN tran_stat_p tsp ON t."trans_ID" = tsp."trans_ID" 
       AND tsp."timestamp" BETWEEN '01-jan-2008 12:00:00 AM' AND SYSDATE
WHERE t."business_process_id" = 'ABC01'

See SQLFIDDLE : http://www.sqlfiddle.com/#!4/04633/49/0

Answer 2

Look into oracle window analytics. http://www.orafaq.com/node/55

You'll want to do a diff of your current row date and the lag of that date. Hope that makes sense.

Answer 3

The actual query would use LAG, which will give you a value from a prior row.

Your status codes won't sort as F -> W -> S -> P, which is why the query below has the big CASE statement for the LAG function's ORDER BY - it translates the status codes into a value that follows your transaction life cycle.

SELECT
  t.trans_id,
  t.business_process_id,
  tsp.status,
  tsp.timestamp,
  tsp.timestamp - LAG(timestamp) OVER (
    PARTITION BY tsp.trans_id
    ORDER BY
      CASE tsp.Status
        WHEN 'F' THEN 1
        WHEN 'W' THEN 2
        WHEN 'S' THEN 3
        WHEN 'P' THEN 4
        END) AS DaysBetween
FROM tran t
INNER JOIN tran_stat_p tsp ON t.trans_id = tsp.trans_id
WHERE tsp.timestamp BETWEEN DATE '2008-01-01' AND SYSDATE
  AND t.business_process_id = 'ABC01';

A couple more notes:

• The query is untested. If you have trouble please post some sample data and I'll test it.
• I used DATE '2008-01-08' to define Jnauary 1, 2008 because that's how Oracle (and ANSI) likes a date constant to look. When you use 1-jan-2008 you're relying on Oracle's default date format, and that's a session value which can be changed. If it's changed your query will stop working.