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