join - SQL, joining tables -

here problem. have 2 tables , b. both have field of date type. denote them date_a , date_b, correspondingly. problem join these 2 tables each other following method - each row x table need join row y b such among rows z b row y provides minimum value of expression abs(date_a(x)-date_b(z)), i.e. it's value in date_b closest value of date_a in x. assumingly, minimum unique, if it's not it's choose 1 of them randomly (but one). example. table a:

"a", "b", "2015-10-01"

table b: "c", "2015-10-07" "d", "2015-12-02"

expected result:

"a", "b", "2015-10-01", "c", "2015-10-07"

p.s. platform teradata, if matters

of course write join-condition based on logic, worst_case in parallel dbms teradata. result in product join (probably followed step return 1 matching row).

for closest-match-join try find actual value using lag/lead logic:

select   dt date_a,   -- find previous date_b // lag   max(case when x = 2 dt end) on (order  dt, x rows unbounded preceding ) prev_date,   -- find next date_b // lead   min(case when x = 2 dt end) on (order  dt, x rows between current row , unbounded following ) next_date,   -- find nearest date   case when prev_date null next_date        when next_date null prev_date        when dt - prev_date < next_date - dt prev_date         else next_date    end date_b  ( -- dates, maybe union instead of union    select date_a dt, 1 x    table_a    union    select date_b, 2    table_b  ) dt qualify x = 1 -- rows table_a 

this need 2 stat-steps, got correct date_b equi-join. put in derived table , join both table_a , tabke_b, should faster unless both tables small.

of course you're joining on other columns well, add them union , partiton by.