I have a table that looks like this
| event_d | event_lcl_ts | location_id | event_code | container_id |
|---|---|---|---|---|
| 4/6/24 | 2024-04-06T10:19:32.133+00:00 | 1 | PUTAWAY | asdjhdf-323 |
| 4/6/24 | 2024-04-06T21:52:35.019+00:00 | 1 | RECEIVE | asdjhdf-323 |
| 4/7/24 | 2024-04-07T00:13:17.496+00:00 | 1 | PUTAWAY | asdjhdf-323 |
| 4/7/24 | 2024-04-07T12:35:54.766+00:00 | 1 | RECEIVE | asdjhdf-323 |
| 4/7/24 | 2024-04-07T16:27:13.245+00:00 | 1 | PUTAWAY | asdjhdf-323 |
| 4/8/24 | 2024-04-08T22:56:19.038+00:00 | 1 | RECEIVE | asdjhdf-323 |
| 4/9/24 | 2024-04-09T00:19:47.575+00:00 | 1 | PUTAWAY | asdjhdf-323 |
| 4/10/24 | 2024-04-10T20:44:12.190+00:00 | 1 | RECEIVE | asdjhdf-323 |
| 4/11/24 | 2024-04-11T01:14:45.466+00:00 | 1 | PUTAWAY | asdjhdf-323 |
| 4/11/24 | 2024-04-11T10:14:12.709+00:00 | 1 | RECEIVE | asdjhdf-323 |
| 4/11/24 | 2024-04-11T12:57:11.640+00:00 | 1 | PUTAWAY | asdjhdf-323 |
I am trying to create a row where it shows the timestamp when the container was received and then put away which is based on the event_code. One caveat of this data is that there will be instances where a PUTAWAY event will be populated without its prior RECEIVE event based on the timeframe of the data pulled. There can also be cases where the PUTAWAY event has not occurred yet over the most recent RECEIVE event.
In this scenario by ideal output would be this:
| container_id | receive_timestamp | putaway_timestamp | location_id |
|---|---|---|---|
| asdjhdf-323 | 2024-04-06T21:52:35.019+00:00 | 2024-04-07T00:13:17.496+00:00 | 1 |
| asdjhdf-323 | 2024-04-07T12:35:54.766+00:00 | 2024-04-07T16:27:13.245+00:00 | 1 |
| asdjhdf-323 | 2024-04-08T22:56:19.038+00:00 | 2024-04-09T00:19:47.575+00:00 | 1 |
| asdjhdf-323 | 2024-04-10T20:44:12.190+00:00 | 2024-04-11T01:14:45.466+00:00 | 1 |
| asdjhdf-323 | 2024-04-11T10:14:12.709+00:00 | 2024-04-11T12:57:11.640+00:00 | 1 |
How can I do this while ignoring or filtering cases where there might be PUTAWAY events before RECEIVE? In this case - the first row. Thanks!
Looks like you can just use LAG or LEAD if you are sure that the row are always interleaved.
SELECT
t.container_id,
t.event_lcl_ts AS receive_timestamp,
t.putaway_timestamp,
t.location_id
FROM (
SELECT t.*,
LEAD(CASE WHEN t.event_code = 'PUTAWAY' THEN t.event_lcl_ts END)
OVER (PARTITION BY t.location_id, t.container_id ORDER BY t.event_lcl_ts) AS putaway_timestamp
FROM YourTable AS t
) AS t
WHERE t.event_code = 'RECEIVE';