mardi 28 mars 2017

SQL query - To update a column with if condition and join

Request your help in writing update query with joining 2 tables and an if condition.

Converting the amount to USD here. There are 2 tables. A - with amount and original currency, B- with Exchange rate, currency, and type. Exch_rate value should be fetched with below conditions and where b.[Rate Type]='Actual'. In case if a particular currency does not have corresponding row with b.[Rate Type]='Actual', then it should consider exch_rate of row with value b.[Rate Type]='NA'

This is what i have got so far. This query is able to only update the items which has corresponding row in table B where [Rate Type]='Actual'. I am not sure how can I check if corresponding rows are there or not and update accordingly

update a
    set [USD_AMT]=a.[Amt] *  b.[Exch Rate]
    from [dbo].a
    inner join [dbo].b
on
    a.[Currency]=b.[From Cur]
    and month(a.month)=month(b.[Eff Date])

where
    b.[To Cur]='USD' and
    b.[Rate Type]='Actual' 

Table A:

Amt | Currency | Month | USD_AMT

100 | GBP | Jan

200 | ISD | Feb

Table B:

From cur | To cur| Rate Type | Month | Exch_Rate

GBP USD Actual Jan 0.16

GBP USD NA Jan 0.18

ISD USD NA Feb 65

Please help.

Aucun commentaire:

Enregistrer un commentaire