Problem with date based query

[dave]

I have two tables - Transactions and Rates.

Table: Txns
Refce TxnDate Curr Amount
10 21/03/2006 USD 10.87
11 10/04/2006 USD 20.00

Table: Rates
Curr RateDate Rate
EUR 01/01/2006 1.41
USD 01/01/2006 1.80
USD 01/03/2006 1.81
USD 01/04/2006 1.82
USD 01/05/2006 1.83

I want to link these tables in a Query so that I can value the
transactions using the currency rate in force at the transaction date.
In this example, for Refce=10, the rate should be 1.81; for Refce=11,
it should be 1.82.

If only a forward dated rate is found, it should be ignored.

All dates are ddmmyyyy

Can this be done in a *single* Query?
Can anyone help with the SQL please?

Thanks
Dave

[w_a_n_n_a_l_l_ -]

This:

SELECT txns.Curr, txns.Refce, Format([txns].[txndate],"d mmm yyyy") AS
TxnDate, Format((Select Max(rM.ratedate) From rates as rM Where rM.ratedate
< txns.TxnDate And rM.curr=txns.Curr),"d mmm yyyy") AS ApplicableRateDate,
(Select rR.Rate From rates as rR Where rR.Curr = txns.Curr and rR.RateDate =
(Select Max(rM.ratedate) From rates as rM Where rM.ratedate < txns.TxnDate
And rM.curr=txns.Curr)) AS ApplicableRate
FROM txns;
returned this:

Curr Refce TxnDate ApplicableRateDate ApplicableRate
USD 10 21 Mar 2006 1 Mar 2006 1.81
USD 11 10 Apr 2006 1 Apr 2006 1.82

the sql could should be trimmed down to this:

SELECT txns.Curr, txns.Refce, txns.Amount, txns.TxnDate AS TxnDate, (Select
rR.Rate From rates as rR Where rR.Curr = txns.Curr and rR.RateDate = (Select
Max(rM.ratedate) From rates as rM Where rM.ratedate < txns.TxnDate And
rM.curr=txns.Curr)) AS ApplicableRate
FROM txns;

I had some formatting in the first version to show dates unequivocally as d
mmm yyyy so the month would be spelled out.

In case you're interested, there are two correlated subqueries used. The
lowest level is the (Select max...) that finds the latest date earlier than
the transaction date for the currency type. That one is fed up one level to
the (Select rR.Rate...) that finds the rate that matches the date returned
by the lower level one.

In the first version I accidentally omitted the Amount column. I just
noticed.

The other thing of note perhaps is the use of aliases. In both subqueries I
used aliases because it saves on typing when specifying, for instance, which
Curr field is being referenced. use of aliases also helps in that you can
use the alias to distinguish for yourself the use of the table where, as in
this example, the same table is used in two contexts. In the lower level
subquery I select from rates in order only to find the maximum date less
than my transaction date. Hence rM, M for Max. In the upper subquery I
refer to the rates table in order to get the rate, hence rR. R for rate.

Fun stuff.

[dave]

Excellent, thanks very much for your time and effort.

Dave

w_a_n_n_a_l_l_-@-_s_b_c_g_l_o_b_a_l._n_e_t wrote:

This:

SELECT txns.Curr, txns.Refce, Format([txns].[txndate],"d mmm yyyy") AS
TxnDate, Format((Select Max(rM.ratedate) From rates as rM Where rM.ratedate
< txns.TxnDate And rM.curr=txns.Curr),"d mmm yyyy") AS ApplicableRateDate,
(Select rR.Rate From rates as rR Where rR.Curr = txns.Curr and rR.RateDate =
(Select Max(rM.ratedate) From rates as rM Where rM.ratedate < txns.TxnDate
And rM.curr=txns.Curr)) AS ApplicableRate
FROM txns;
returned this:

Curr Refce TxnDate ApplicableRateDate ApplicableRate
USD 10 21 Mar 2006 1 Mar 2006 1.81
USD 11 10 Apr 2006 1 Apr 2006 1.82

the sql could should be trimmed down to this:

SELECT txns.Curr, txns.Refce, txns.Amount, txns.TxnDate AS TxnDate, (Select
rR.Rate From rates as rR Where rR.Curr = txns.Curr and rR.RateDate = (Select
Max(rM.ratedate) From rates as rM Where rM.ratedate < txns.TxnDate And
rM.curr=txns.Curr)) AS ApplicableRate
FROM txns;

I had some formatting in the first version to show dates unequivocally as d
mmm yyyy so the month would be spelled out.

In case you're interested, there are two correlated subqueries used. The
lowest level is the (Select max...) that finds the latest date earlier than
the transaction date for the currency type. That one is fed up one level to
the (Select rR.Rate...) that finds the rate that matches the date returned
by the lower level one.

In the first version I accidentally omitted the Amount column. I just
noticed.

The other thing of note perhaps is the use of aliases. In both subqueries I
used aliases because it saves on typing when specifying, for instance, which
Curr field is being referenced. use of aliases also helps in that you can
use the alias to distinguish for yourself the use of the table where, as in
this example, the same table is used in two contexts. In the lower level
subquery I select from rates in order only to find the maximum date less
than my transaction date. Hence rM, M for Max. In the upper subquery I
refer to the rates table in order to get the rate, hence rR. R for rate.

Fun stuff.

[dave]

Excellent, thanks very much for your time and effort.

Dave

w_a_n_n_a_l_l_-@-_s_b_c_g_l_o_b_a_l._n_e_t wrote:

This:

SELECT txns.Curr, txns.Refce, Format([txns].[txndate],"d mmm yyyy") AS
TxnDate, Format((Select Max(rM.ratedate) From rates as rM Where rM.ratedate
< txns.TxnDate And rM.curr=txns.Curr),"d mmm yyyy") AS ApplicableRateDate,
(Select rR.Rate From rates as rR Where rR.Curr = txns.Curr and rR.RateDate =
(Select Max(rM.ratedate) From rates as rM Where rM.ratedate < txns.TxnDate
And rM.curr=txns.Curr)) AS ApplicableRate
FROM txns;
returned this:

Curr Refce TxnDate ApplicableRateDate ApplicableRate
USD 10 21 Mar 2006 1 Mar 2006 1.81
USD 11 10 Apr 2006 1 Apr 2006 1.82

the sql could should be trimmed down to this:

SELECT txns.Curr, txns.Refce, txns.Amount, txns.TxnDate AS TxnDate, (Select
rR.Rate From rates as rR Where rR.Curr = txns.Curr and rR.RateDate = (Select
Max(rM.ratedate) From rates as rM Where rM.ratedate < txns.TxnDate And
rM.curr=txns.Curr)) AS ApplicableRate
FROM txns;

I had some formatting in the first version to show dates unequivocally as d
mmm yyyy so the month would be spelled out.

In case you're interested, there are two correlated subqueries used. The
lowest level is the (Select max...) that finds the latest date earlier than
the transaction date for the currency type. That one is fed up one level to
the (Select rR.Rate...) that finds the rate that matches the date returned
by the lower level one.

In the first version I accidentally omitted the Amount column. I just
noticed.

The other thing of note perhaps is the use of aliases. In both subqueries I
used aliases because it saves on typing when specifying, for instance, which
Curr field is being referenced. use of aliases also helps in that you can
use the alias to distinguish for yourself the use of the table where, as in
this example, the same table is used in two contexts. In the lower level
subquery I select from rates in order only to find the maximum date less
than my transaction date. Hence rM, M for Max. In the upper subquery I
refer to the rates table in order to get the rate, hence rR. R for rate.

Fun stuff.

[dave]

Excellent, thanks very much for your time and effort.

Dave

w_a_n_n_a_l_l_-@-_s_b_c_g_l_o_b_a_l._n_e_t wrote:

This:

SELECT txns.Curr, txns.Refce, Format([txns].[txndate],"d mmm yyyy") AS
TxnDate, Format((Select Max(rM.ratedate) From rates as rM Where rM.ratedate
< txns.TxnDate And rM.curr=txns.Curr),"d mmm yyyy") AS ApplicableRateDate,
(Select rR.Rate From rates as rR Where rR.Curr = txns.Curr and rR.RateDate =
(Select Max(rM.ratedate) From rates as rM Where rM.ratedate < txns.TxnDate
And rM.curr=txns.Curr)) AS ApplicableRate
FROM txns;
returned this:

Curr Refce TxnDate ApplicableRateDate ApplicableRate
USD 10 21 Mar 2006 1 Mar 2006 1.81
USD 11 10 Apr 2006 1 Apr 2006 1.82

the sql could should be trimmed down to this:

SELECT txns.Curr, txns.Refce, txns.Amount, txns.TxnDate AS TxnDate, (Select
rR.Rate From rates as rR Where rR.Curr = txns.Curr and rR.RateDate = (Select
Max(rM.ratedate) From rates as rM Where rM.ratedate < txns.TxnDate And
rM.curr=txns.Curr)) AS ApplicableRate
FROM txns;

I had some formatting in the first version to show dates unequivocally as d
mmm yyyy so the month would be spelled out.

In case you're interested, there are two correlated subqueries used. The
lowest level is the (Select max...) that finds the latest date earlier than
the transaction date for the currency type. That one is fed up one level to
the (Select rR.Rate...) that finds the rate that matches the date returned
by the lower level one.

In the first version I accidentally omitted the Amount column. I just
noticed.

The other thing of note perhaps is the use of aliases. In both subqueries I
used aliases because it saves on typing when specifying, for instance, which
Curr field is being referenced. use of aliases also helps in that you can
use the alias to distinguish for yourself the use of the table where, as in
this example, the same table is used in two contexts. In the lower level
subquery I select from rates in order only to find the maximum date less
than my transaction date. Hence rM, M for Max. In the upper subquery I
refer to the rates table in order to get the rate, hence rR. R for rate.

Fun stuff.