Database Administrators Asked by Trives on February 5, 2021
I’ve seen a few close examples here but I’m not quite getting something close enough for me to noodle out a fix.
I have events, where trucks come in and out of a gate and I have two tables. The first is all ingate events the second is all outgate events. The problem is conceptually easy: For each ingate, find the nearest outgate. The dilemma I have is that obviously trucks can come back. So I might have 300 ingates for a unit on the ingate table with a corresponding 300 outgates.
Both tables look like this, except the other is session.Outgate
session.Ingate
:
CHAS_INIT| CHAS_NR | Ingate_TIME
---------+------------+---------------------
MAEC | 66862 | 2018-01-29-01.34.00
MAEC | 67218 | 2018-03-24-01.29.00
MAEC | 67557 | 2018-03-30-01.26.00
MAEC | 67557 | 2018-04-06-01.49.00
MAEC | 68773 | 2018-02-22-01.47.00
While this is a breeze to do in a spreadsheeting tool, I’m stumped on if it can be done during a join. Any helps is greatly appreciated.
select CHAS_INIT, CHAS_NR, Ingate_TIME,
-- find the previous IN-time
last_value(case when inout = 'i' then Ingate_TIME end ignore nulls)
over (partition by CHAS_INIT, CHAS_NR -- does this determine a truck?
order by Ingate_TIME)
from
( -- combine both in & out into a single result
select 'i' as inout, CHAS_INIT, CHAS_NR, Ingate_TIME
from session.Ingate
union all
select 'o' as inout, CHAS_INIT, CHAS_NR, Ingate_TIME
from session.Outgate
) as dt
-- only return OUT rows
qualify inout = 'o'
Answered by dnoeth on February 5, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP