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';