By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TRACKER (RECORDID, BILLETNUM, TIMESTAMP) AS
SELECT 1, 100, TIMESTAMP '2022-06-01 09:00:00.0' FROM DUAL UNION ALL
SELECT 2, 200, TIMESTAMP '2022-06-01 09:01:00.0' FROM DUAL UNION ALL
SELECT 3, 300, TIMESTAMP '2022-06-01 09:01:30.0' FROM DUAL UNION ALL
SELECT 4, 100, TIMESTAMP '2022-06-01 09:05:00.0' FROM DUAL UNION ALL
SELECT 5, 200, TIMESTAMP '2022-06-01 09:06:30.0' FROM DUAL UNION ALL
SELECT 6, 300, TIMESTAMP '2022-06-01 09:08:00.0' FROM DUAL UNION ALL
SELECT 7, 100, TIMESTAMP '2022-06-01 09:10:00.0' FROM DUAL UNION ALL
SELECT 8, 200, TIMESTAMP '2022-06-01 09:11:00.0' FROM DUAL UNION ALL
SELECT 9, 100, TIMESTAMP '2022-06-01 09:14:53.0' FROM DUAL UNION ALL
SELECT 10, 100, TIMESTAMP '2022-06-02 09:27:16.0' FROM DUAL
10 rows affected
SELECT *
FROM TRACKER
| RECORDID | BILLETNUM | TIMESTAMP |
|---|---|---|
| 1 | 100 | 01-JUN-22 09.00.00.000000000 |
| 2 | 200 | 01-JUN-22 09.01.00.000000000 |
| 3 | 300 | 01-JUN-22 09.01.30.000000000 |
| 4 | 100 | 01-JUN-22 09.05.00.000000000 |
| 5 | 200 | 01-JUN-22 09.06.30.000000000 |
| 6 | 300 | 01-JUN-22 09.08.00.000000000 |
| 7 | 100 | 01-JUN-22 09.10.00.000000000 |
| 8 | 200 | 01-JUN-22 09.11.00.000000000 |
| 9 | 100 | 01-JUN-22 09.14.53.000000000 |
| 10 | 100 | 02-JUN-22 09.27.16.000000000 |
SELECT RecordID,
lag(TIMESTAMP) OVER (PARTITION BY Billetnum ORDER BY TIMESTAMP)
AS PriorStamp
FROM Tracker
| RECORDID | PRIORSTAMP |
|---|---|
| 1 | null |
| 4 | 01-JUN-22 09.00.00.000000000 |
| 7 | 01-JUN-22 09.05.00.000000000 |
| 9 | 01-JUN-22 09.10.00.000000000 |
| 10 | 01-JUN-22 09.14.53.000000000 |
| 2 | null |
| 5 | 01-JUN-22 09.01.00.000000000 |
| 8 | 01-JUN-22 09.06.30.000000000 |
| 3 | null |
| 6 | 01-JUN-22 09.01.30.000000000 |
SELECT RecordID,
BILLETNUM,
TIMESTAMP,
lag(TIMESTAMP) OVER (PARTITION BY Billetnum ORDER BY TIMESTAMP)
AS PriorStamp,
TIMESTAMP - lag(TIMESTAMP) OVER (PARTITION BY Billetnum ORDER BY TIMESTAMP)
AS TIMESTAMP_DIFF,
EXTRACT(DAY FROM TIMESTAMP - lag(TIMESTAMP) OVER (PARTITION BY Billetnum ORDER BY TIMESTAMP))
AS DAYS_DIFF
FROM Tracker
| RECORDID | BILLETNUM | TIMESTAMP | PRIORSTAMP | TIMESTAMP_DIFF | DAYS_DIFF |
|---|---|---|---|---|---|
| 1 | 100 | 01-JUN-22 09.00.00.000000000 | null | null | null |
| 4 | 100 | 01-JUN-22 09.05.00.000000000 | 01-JUN-22 09.00.00.000000000 | +000000000 00:05:00.000000000 | 0 |
| 7 | 100 | 01-JUN-22 09.10.00.000000000 | 01-JUN-22 09.05.00.000000000 | +000000000 00:05:00.000000000 | 0 |
| 9 | 100 | 01-JUN-22 09.14.53.000000000 | 01-JUN-22 09.10.00.000000000 | +000000000 00:04:53.000000000 | 0 |
| 10 | 100 | 02-JUN-22 09.27.16.000000000 | 01-JUN-22 09.14.53.000000000 | +000000001 00:12:23.000000000 | 1 |
| 2 | 200 | 01-JUN-22 09.01.00.000000000 | null | null | null |
| 5 | 200 | 01-JUN-22 09.06.30.000000000 | 01-JUN-22 09.01.00.000000000 | +000000000 00:05:30.000000000 | 0 |
| 8 | 200 | 01-JUN-22 09.11.00.000000000 | 01-JUN-22 09.06.30.000000000 | +000000000 00:04:30.000000000 | 0 |
| 3 | 300 | 01-JUN-22 09.01.30.000000000 | null | null | null |
| 6 | 300 | 01-JUN-22 09.08.00.000000000 | 01-JUN-22 09.01.30.000000000 | +000000000 00:06:30.000000000 | 0 |