Hi, I have a couple of tables, Cash and FXRates. The Cash table gets populated daily via SSIS package import from CSV but column BaseCurrencyValue is always NULL.
I am looking for help on how to write an update query that will update the BaseCurrencyValue column for all records based on a lookup where Cash.Currency = FXRates.ToCurrency and returns the FXRate value which I then need to multiply against the Cash.Value field before updating that value into the BaseCurrencyValue field.
It's a simple currency conversion.
Here are the basic tables and data:
CREATE TABLE Cash(
[SaleDate] varchar NULL,
[SaleTime] varchar NULL,
[Receipt No] [int] NULL,
[Currency] varchar NULL,
[Value] [decimal](18, 2) NULL,
[BaseCurrencyValue] [decimal](18, 2) NULL
)
GO
CREATE TABLE FXRates(
[BaseCurrency] varchar NULL,
[ToCurrency] varchar NULL,
[FXRate] [decimal](18, 8) NULL
)
GO
INSERT INTO Cash
([SaleDate]
,[SaleTime]
,[Receipt No]
,[Currency]
,[Value]
,[BaseCurrencyValue])
VALUES
('20/08/2019'
,'09:10:00'
,1
,'GBP'
,20
,NULL),
('19/08/2019'
,'09:10:00'
,1
,'EUR'
,20
,NULL)
GO
INSERT INTO FXRates
([BaseCurrency]
,[ToCurrency]
,[FXRate])
VALUES
('EUR'
,'GBP'
,1.11110000),
('EUR'
,'EUR'
,1)
GO
Any help would be much appreciated as I have tried triggers and update queries to no avail.
Thank you.