CODEWITHSUNDEEP
sqljoin
user2811143
user2811143

Reputation: 531

SQL join query about 2 tables

I have a table A

Trade#    Trade_DT
  1        08/10/2013
  2        08/20/2013

and table B

BaseRate   EffectiveDT
  1.5       08/01/2013
  2.0       08/15/2013
  3.0       08/25/2013

I want to have a join such that i get the EffectiveDT after the TradeDT

Trade# Trade_DT   BaseRate    EffectiveDT
  1    08/10/2013  2.0        08/15/2013
  2    08/20/2013  3.0        08/25/2013

Upvotes: 0

Views: 47

Answers (3)

PM 77-1
PM 77-1

Reputation: 13352

This code uses only standard SQL:

SELECT a.*, b.*
FROM TableA a
JOIN TableB b ON b.EffectiveDT =  
                 (SELECT MIN(EffectiveDT) 
                  FROM TableB b1 
                  WHERE a.TradeDT < b1.EffectiveDT)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271131

I am guessing that you want the earliest effective date after the Trade_Dt. The following will work in any SQL dialect:

select a.*,
       (select min(EffectiveDt)
        from b
        where b.EffectiveDt > a.TradeDt
       ) as EffectiveDt
from a;

EDIT:

To get all the values from the b table requires just joining the table back in:

select t.Trade#, t.Trade_DT, b.BaseRate, b.EffectiveDt
from (select a.*,
             (select min(EffectiveDt)
              from b
              where b.EffectiveDt > a.TradeDt
             ) as EffectiveDt
      from a
     ) t join
     b
     on a.EffectiveDt = b.EffectiveDt;

Upvotes: 1

mucio
mucio

Reputation: 7129

in Oracle you can use this:

  select distinct Trade,
           Trade_DT,
           FIRST_VALUE(BaseRate) Over (partition by Trade order by EffectiveDT desc) BaseRate,
           FIRST_VALUE(EffectiveDT) Over (partition by Trade order by EffectiveDT desc) EffectiveDT
      from tableA
inner join tableB 
        on tableA.Trade_DT >= tableB.EffectiveDT

And here a demo in SQLFiddle.

Upvotes: 0

Related Questions