How to add a column based on certain date range

I have two tables which I need to join.

First Table:

EFF_DATE     RATE   CURRENCY
20110101     1.286  USD
20110201     1.275  USD
20110301     1.275  USD
20110401     1.260  USD
20110501     1.225  USD

2nd Table:

PO_NO   TRANS_DATE  ACCT_DATE   SUPP_NO CURRENCY     LOCAL_AMT 
1000068 20110114    20110115    S016    USD          16,500.00 
1000070 20110214    20110215    S016    USD           7,660.00 
1000072 20110317    20110322    S025    USD           1,080.00 
1000132 20110314    20110315    S037    USD           3,500.00 
1000133 20110414    20110415    S038    USD          14,500.00 

Based on ACCT_DATE, I wish to add RATE column & the result should look like this:

PO_NO   TRANS_DATE  ACCT_DATE   SUPP_NO CURRENCY     LOCAL_AMT  RATE
1000068 20110114    20110115    S016    USD          16,500.00  1.286
1000070 20110214    20110215    S016    USD           7,660.00  1.275
1000072 20110317    20110322    S025    USD           1,080.开发者_如何学C00  1.275
1000132 20110314    20110315    S037    USD           3,500.00  1.275
1000133 20110414    20110415    S038    USD          14,500.00  1.26
1000170 20110531    20110531    S016    USD          15,400.00  1.225

How can I achieve this by using SQL? Thanks!

In MySQL you would do something like:

SELECT st.*, ft.rate FROM second_table st LEFT JOIN first_table ft ON (MONTH(acct_date) = MONTH(eff_date) AND YEAR(acct_date) = YEAR(eff_date) )

Solution below assumes that trans_date, acct_date and eff_date are int. If they are date, you need to use proper date function depending on your SQL server

SELECT 
    [trans].[po_no]
    ,[trans].[trans_date]
    ,[trans].[acct_date]
    ,[trans].[supp_no]
    ,[trans].[currency]
    ,[trans].[local_amt]
    ,[month_rate].[rate]
FROM [trans]
    JOIN [month_rate] on [eff_date] / 100 = [trans].[acct_date] / 100
    AND [month_rate].[currency] = [trans].[currency]

select st.*,ft.rate from second_table st,first_table ft 
where to_char(st.acct_date,'YYYYMM')=to_char(ft.eff_date,'YYYYMM')

as per your given example you have EFF_DATE and ACCT_DATE in formated form only so i think you can directly join basis of these two columns

like -

select st.*,ft.rate from first_table ft, second_table st
where st.acct_date=ft.eff_date

but this will not give your desired output what you have mentioned in you question...

do you really want to join with acct_date???

and if you want to just join without this type of condition then better mention that condition and update your question

This will use indexes of firstTable.t.EFF_DATE and secondTable.ACCT_DATE (if you have indexes!).

Any solution with calls to functions like MONTH(), YEAR() cannot effectively use such indexes.

SELECT st.*
     , ( SELECT ft.RATE
         FROM firstTable ft 
         WHERE ft.EFF_DATE <= st.ACCT_DATE            
         ORDER BY ft.EFF_DATE DESC
         LIMIT 1
       ) AS RATE
FROM secondTable st 

LIMIT 1 is the MySQL syntax. For SQL-Server use SELECT TOP 1.