I would like to write query to add column which calculate values using exchange rate conversion rate from the other table

First table:

Second table

I would like to write query to get column with "Value in USD" which convert lo

cal CCY by the rate based on the Date in the first column

Thank you so much for the support

CodePudding user response：

This is a simple INNER JOIN and application of exchange rate, for either platform.

SELECT data.Date,
       data.localCCY,
       data.ValueinlocalCCY / rates.USDFactor AS ValueInUSD
FROM Table1 AS data
INNER JOIN Table2 AS rates ON rates.Date = data.Date
                          AND rates.CCY = data.localCCY;

Example for SQL Server:

DROP TABLE IF EXISTS #data;
CREATE TABLE #data (
  [Date] DATE,
  [localCCY] VARCHAR(100),
  [ValueinlocalCCY] DECIMAL(18, 6)
);

DROP TABLE IF EXISTS #rates;
CREATE TABLE #rates (
  [Date] DATE,
  [CCY] VARCHAR(100),
  [USDFactor] DECIMAL(18, 6)
);

INSERT INTO #data ([Date],[localCCY],[ValueinlocalCCY])
SELECT '02.06.2022', 'Eur', 5000 UNION ALL
SELECT '02.06.2022', 'Gbp', 6000 UNION ALL
SELECT '01.06.2022', 'CHF', 40000;

INSERT INTO #rates ([Date],[CCY],[USDFactor])
SELECT '02.06.2022', 'Eur', 0.99 UNION ALL
SELECT '02.06.2022', 'Gbp', 0.9 UNION ALL
SELECT '02.06.2022', 'CHF', 0.95 UNION ALL
SELECT '01.06.2022', 'Eur', 0.98 UNION ALL
SELECT '01.06.2022', 'Gbp', 0.91 UNION ALL
SELECT '01.06.2022', 'CHF', 0.92;

SELECT d.date, d.localCCY, d.ValueinlocalCCY / r.USDFactor AS [Value In USD]
FROM #data d
INNER JOIN #rates r ON r.Date = d.Date
                   AND r.CCY = d.localCCY;

CodePudding user response：

Thank you so much it works great ! Right now I would like to create similar column with value in CHF

So first I will need to calculate "CHFfactor" :

Select Rates.USDFactor/CHF.USDFactor as CHFFactor
From Table2 as Rates
Inner join Table2 CHF
on CHF.Date = Rates.Date and CHF.CCY = USD

And then use the table to get value in CHF However above solution requires separate query and I'm wondering if there is any other solutions how to get this done?

Thank you so much !