CODEWITHSUNDEEP
sqlamazon-redshift
Sujith Rao
Sujith Rao

Reputation: 17

Join Two Tables based on a range of dates

I'm struggling to get the below logic. I have 2 tables.

Table A

Request#       ReqDate          Amount    Currency
-----------------------------------------------
REQ1234        2017-01-01       100000    INR

Table B

ExDate           Conversion      FromCur          ToCur
-----------------------------------------------------
2017-01-03     0.018           INR              USD  
2016-12-20     0.019           INR              USD  
2016-12-21     0.011           INR              USD  
2016-12-22     0.012           INR              USD  
2016-12-23     0.015           INR              USD  
2016-12-24     0.020           INR              USD  
2016-12-25     0.021           INR              USD

Here, I need to join on FromCur and Date to get corresponding USD conversionfactor.

However, if the factor for the ReqDate is not available check the previous date, if not available check before that date.

Like wise I need to check 7 days before which is 2016-12-25 and get that factor.

How to do this? So the result should be

Req#     Date        Conversion     Amount   Currency    ToCur    USDAmt ExDate
-------------------------------------------------------------------------------  
REQ1234  2017-01-01  0.021          100000   INR         CAD      2100   2016-12-25

Upvotes: 0

Views: 267

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35623

For SQL Server I suggest an apply operator, and if as you may need NULL returned if one isn't found then use outer apply

select a.*,rate.conversion 
from a
outer apply (
        select top(1) b.conversion
        from b
        where b.fromcur = a.currency and b.date <= a.date
        -- and b.date > dateadd(day,-7,a.date)
        order by b.date desc
       ) as rate

Note if you need to limit how far back you seek a rate use an additional condition in the where clause as suggested above.

You can use a correlated subquery in the select clause, but I recommend using the approach above. 

select a.*
     , (select top(1) b.conversion
        from b
        where b.fromcur = a.currency and b.date <= a.date
        -- and b.date > dateadd(day,-7,a.date)
        order by b.date desc
       ) as conversion
from a

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

One method uses a correlated subquery:

select a.*,
       (select b.conversion
        from b
        where b.fromcur = a.currency and b.date <= a.date
        order by b.date desc
        fetch first 1 row only
       ) as conversion
from a

Upvotes: 1

Related Questions