TransWikia.com

For each row, find the next closest value in another table (Teradata)

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.

One Answer

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP