I have a table of transactions in an Oracle database. I am attempting to pull a report together for a delivery system involving a number of transaction types. The "request" type can actually be one of four sub-types ('A', 'B', 'C', and 'D' for this example), and the "delivery" type can be one of four different sub-types ('PULL', 'PICKUP', 'MAIL'). There can be anywhere from 1 to 5 transactions to get an item from "request" to "delivery, and a number of the "delivery" types are also intermediary transactions. Example:
Item | Transaction | Timestamp
001 | REQ-A | 2014-07-31T09:51:32Z
002 | REQ-B | 2014-07-31T09:55:53Z
003 | REQ-C | 2014-07-31T10:01:15Z
004 | REQ-D | 2014-07-31T10:02:29Z
005 | REQ-A | 2014-07-31T10:05:47Z
002 | PULL | 2014-07-31T10:20:04Z
002 | MAIL | 2014-07-31T10:20:06Z
001 | PULL | 2014-07-31T10:22:21Z
001 | TRANSFER | 2014-07-31T10:22:23Z
003 | PULL | 2014-07-31T10:24:10Z
003 | TRANSFER | 2014-07-31T10:24:12Z
004 | PULL | 2014-07-31T10:26:28Z
005 | PULL | 2014-07-31T10:28:42Z
005 | TRANSFER | 2014-07-31T10:28:44Z
001 | ARRIVE | 2014-07-31T11:45:01Z
001 | PICKUP | 2014-07-31T11:45:02Z
003 | ARRIVE | 2014-07-31T11:47:44Z
003 | PICKUP | 2014-07-31T11:47:45Z
005 | ARRIVE | 2014-07-31T11:49:45Z
005 | PICKUP | 2014-07-31T11:49:46Z
What I need is a report like:
Item | Start Tx | End Tx | Time
001 | REQ-A | PICKUP | 1:53:30
002 | REQ-B | MAIL | 0:24:13
003 | REQ-C | PICKUP | 1:46:30
004 | REQ-D | PULL | 0:23:59
005 | REQ-A | PICKUP | 1:43:59
What I have:
Item | Start Tx | End Tx | Time
001 | REQ-A | PULL | 0:30:49
001 | REQ-A | TRANSFER | 0:30:51
001 | REQ-A | ARRIVE | 1:53:29
001 | REQ-A | PICKUP | 1:53:30
002 | REQ-B | PULL | 0:24:11
002 | REQ-B | MAIL | 0:24:13
003 | REQ-C | PULL | 0:22:55
003 | REQ-C | TRANSFER | 0:22:57
003 | REQ-C | ARRIVE | 1:46:29
003 | REQ-C | PICKUP | 1:46:30
004 | REQ-D | PULL | 0:23:59
005 | REQ-A | PULL | 0:22:55
005 | REQ-A | TRANSFER | 0:22:57
005 | REQ-A | ARRIVE | 1:43:58
005 | REQ-A | PICKUP | 1:43:59
What I'm doing to get that data:
SELECT Item, Transaction, nextTransaction, nextTimestamp - Timestamp
FROM (
SELECT Item, Transaction, Timestamp,
LEAD(Transaction, 5) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTransaction"
LEAD(Timestamp, 5) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTimestamp"
FROM Transactions
UNION ALL
SELECT Item, Transaction, Timestamp,
LEAD(Transaction, 4) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTransaction"
LEAD(Timestamp, 4) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTimestamp"
FROM Transactions
UNION ALL
SELECT Item, Transaction, Timestamp,
LEAD(Transaction, 3) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTransaction"
LEAD(Timestamp, 3) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTimestamp"
FROM Transactions
UNION ALL
SELECT Item, Transaction, Timestamp,
LEAD(Transaction, 2) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTransaction"
LEAD(Timestamp, 2) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTimestamp"
FROM Transactions
UNION ALL
SELECT Item, Transaction, Timestamp,
LEAD(Transaction, 1) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTransaction"
LEAD(Timestamp, 1) OVER (PARTITION BY Item ORDER BY Timestamp) AS "nextTimestamp"
FROM Transactions
)
WHERE nextTransaction IS NOT NULL
AND Transaction IN ('REQ-A', 'REQ-B', 'REQ-C', 'REQ-D')
I could manually parse this in a script (and perhaps that's actually the best course of action), but for the sake of learning, I'd like to know if it's possible to actually do this with SQL alone.
It sounds like you want the first and last transactions based on time. I think the following does what you want:
select item,
min(transaction) keep (dense_rank first order by timestamp) as StartTx,
min(transaction) keep (dense_rank last order by timestamp) as EndTx,
max(timestamp) - min(timestamp)
from transactions t
group by item;