SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE CurrencyRate (
    CURCODE varchar(8),
    CURRATE decimal (18,6),
    CURDATE varchar(8)
)

INSERT INTO CurrencyRate
   (CURCODE, CURRATE, CURDATE)
VALUES
    ('USD/AED', 3.672775, '20231001'),
    ('USD/GBP', 0.81671, '20231001'),
    ('USD/AED', 3.672435, '20231101'),
    ('USD/GBP', 0.80671, '20231101')

CREATE TABLE PassengerFare (
   ID int,
   CreateDate Date,
   PName nvarchar(50),
   [Desc] nvarchar(50),
   FareTotal decimal (18,2),
   FareDiscount decimal (18,2)
)

INSERT INTO PassengerFare
   (ID, CreateDate, PName, [Desc], FareTotal, FareDiscount)
VALUES
    (1, '09-14-2023', 'Mark P.', 'OneWay', 178.75, 15.00),
    (2, '10-11-2023', 'Jeff H.', 'TwoWay', 319.50, 30.00)
6 rows affected

-- Define a table-valued function to get the conversion rate
CREATE FUNCTION GetConversionRate(@code varchar(8), @date varchar(8))
RETURNS TABLE AS RETURN
(
    SELECT TOP 1 CURRATE AS ConversionRate
    FROM CurrencyRate
    WHERE CURCODE = @code
    AND CURDATE <= @date
    ORDER BY CURDATE DESC
)
declare @CurrencyType varchar(10) = 'USD/GBP'

-- Query using CROSS APPLY to get the conversion rate for each row
SELECT
    c.ConversionRate,
    pf.PName,
    pf.CreateDate,
    pf.FareTotal * c.ConversionRate AS ConvertedFareTotal,
    pf.FareDiscount * c.ConversionRate AS ConvertedFareDiscount
FROM PassengerFare pf
CROSS APPLY GetConversionRate(@CurrencyType, CONVERT(varchar, pf.CreateDate, 112)) AS c

DROP FUNCTION IF EXISTS GetConversionRate
DROP TABLE IF EXISTS PassengerFare
DROP TABLE IF EXISTS CurrencyRate


ConversionRate
PName
CreateDate
ConvertedFareTotal
ConvertedFareDiscount


0.816710
Jeff H.
2023-10-11
260.93884500
24.50130000