Aggregate in subquery (1 Viewer)

[Bob]

I have a table of payments in various currencies that needs conversion to local currency.

tblPayments table looks like (PaymentID PK)

PaymentID	TransactionDate	CurrencyCode	PaymentAmount
17	4/3/2023	EUR	512.00

Conversion rates are provided by tblCurrencyHistory where PK is a composite key of CurrencyCode and CurrencyDate. (ie, for each currency, we only have one currency rate per day.)
tblCurrencyHistory

CurrencyCode	CurrencyDate	CurrencyRate
EUR	5/4/2023	1.140821

We are not guaranteed to have currency rates for all transaction dates, so we need the previous rate available.
In order words, we need Max(CurrencyDate) WHERE CurrencyDate<=TransactionDate.

In the SELECT statement I need to provide the payments and selected currency rate and date.

Should be simple but I can't get my head around it. Any help much appreciated.

 

[jdraw]

Bob,
Please provide more info. What currencies are involved? Where, how and how often do you populate your tblCurrencyHistory? It would also be helpful to readers if you showed some table designs and your relationships.
And any sql/vba of what yo have tried.

Here's a sample sql from one of my files..

SELECT Dogs.ID, Dogs.DogType,dogs.wght
FROM Dogs where dogs.wght=(select max(x.wght) from dogs as x);

 

[arnelgp]

you may use this Query:

SELECT
    tblPayments.PaymentID,
    tblPayments.TransactionDate,
    tblPayments.CurrencyCode,
    tblPayments.PaymentAmount,
    (SELECT TOP 1 CurrencyRate
        FROM tblCurrencyHistory
            WHERE
                tblCurrencyHistory.CurrencyCode = tblPayments.[CurrencyCode] And
                tblCurrencyHistory.CurrencyDate <= tblPayments.TransactionDate
                ORDER BY tblCurrencyHistory.CurrencyDate DESC)
    AS CurrencyRate
FROM tblPayments;

 

[Bob]

you may use this Query:

SELECT
    tblPayments.PaymentID,
    tblPayments.TransactionDate,
    tblPayments.CurrencyCode,
    tblPayments.PaymentAmount,
    (SELECT TOP 1 CurrencyRate
        FROM tblCurrencyHistory
            WHERE
                tblCurrencyHistory.CurrencyCode = tblPayments.[CurrencyCode] And
                tblCurrencyHistory.CurrencyDate <= tblPayments.TransactionDate
                ORDER BY tblCurrencyHistory.CurrencyDate DESC)
    AS CurrencyRate
FROM tblPayments;

Thank you arnelgp. This works perfectly.
How can I then add CurrencyDate to the subquery as I'd like to show if there's a gap between TransactionDate and CurrencyDate?

 

[arnelgp]

it is the same as deriving the CurrencyRate:

SELECT
    tblPayments.PaymentID,
    tblPayments.TransactionDate,
    tblPayments.CurrencyCode,
    tblPayments.PaymentAmount,
    (SELECT TOP 1 CurrencyRate
        FROM tblCurrencyHistory
            WHERE
                tblCurrencyHistory.CurrencyCode = tblPayments.[CurrencyCode] And
                tblCurrencyHistory.CurrencyDate <= tblPayments.TransactionDate
                ORDER BY tblCurrencyHistory.CurrencyDate DESC)
    AS CurrencyRate,
    (SELECT TOP 1 CurrencyDate
        FROM tblCurrencyHistory
            WHERE
                tblCurrencyHistory.CurrencyCode = tblPayments.[CurrencyCode] And
                tblCurrencyHistory.CurrencyDate <= tblPayments.TransactionDate
                ORDER BY tblCurrencyHistory.CurrencyDate DESC)
    AS CurrencyDate
FROM tblPayments;

 

[Bob]

it is the same as deriving the CurrencyRate:

SELECT
    tblPayments.PaymentID,
    tblPayments.TransactionDate,
    tblPayments.CurrencyCode,
    tblPayments.PaymentAmount,
    (SELECT TOP 1 CurrencyRate
        FROM tblCurrencyHistory
            WHERE
                tblCurrencyHistory.CurrencyCode = tblPayments.[CurrencyCode] And
                tblCurrencyHistory.CurrencyDate <= tblPayments.TransactionDate
                ORDER BY tblCurrencyHistory.CurrencyDate DESC)
    AS CurrencyRate,
    (SELECT TOP 1 CurrencyDate
        FROM tblCurrencyHistory
            WHERE
                tblCurrencyHistory.CurrencyCode = tblPayments.[CurrencyCode] And
                tblCurrencyHistory.CurrencyDate <= tblPayments.TransactionDate
                ORDER BY tblCurrencyHistory.CurrencyDate DESC)
    AS CurrencyDate
FROM tblPayments;

Genius!
Thanks.