Re-use a variable from a sub-query on a whole row of data

November 7, 2023

I need to calculate the dollar Conversion Rate more than once in a row of data since multiple columns will rely on it. To make it more efficient, Is there a way to set a variable (@ConversionRate) just once and re-use the it for each column that uses it (since the value doesn’t change for the whole row). Of course for the next row could it have a different rate and it would need to be re-calculated again.

declare @CurrencyType varchar(10) = 'USD/GBP'
declare @ConversionRate decimal (18,6)


CREATE TABLE CurrentyRate (
    CURCODE varchar(8),
    CURDATE varchar(8),
    CURRATE decimal (18,6)
)

INSERT INTO CurrentyRate
   (CURCODE, CURDATE, CURRATE)
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)


select
    @ConversionRate = (Select Top 1 c.CURRATE From CurrentyRate c with (nolock)
                     Where c.CURCODE = @CurrencyType and c.CURDATE <= CONVERT(varchar, pf.CREATEDATE, 112)
                     Order By c.CURDATE DESC),
    pf.PName,
    pf.CreateDate,
    pf.FareTotal * @ConversionRate,
    pf.FareDiscount * @ConversionRate
from PassengerFare pf

DROP TABLE IF EXISTS PassengerFare
DROP TABLE IF EXISTS CurrentyRate

>Solution :

You can use CROSS APPLY combined with function. Here is the sample code:

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)
GO

-- 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
)

GO

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

Here is sample output:

Here is the sample fiddle link

NB : In this sample, Mark P data is not displayed because no rate suitable to his data. If you want to add some logic for that, please add that information on your question,