Query dependant on dates

[Softop]

I have two tables as shown below. They are not joined. I wish to create a Query that will select the correct rate from Table 1 at the appropriate date in Table 2. Can anyone help please (supply the SQL for the query)?

Table1-Rates
EffDate Rate
22/01/2004 2.5
27/02/2004 2.3
15/03/2004 2.1
29/03/2004 2.8

Table2-Tasks
TaskRef TaskDate
A001 24/01/2004
A002 31/01/2004
B003 27/02/2004
A015 02/03/2004
C005 16/03/2004
A014 19/03/2004
A003 31/03/2004
B001 14/04/2004

Thanks in advance,
Softop

 

[Softop]

Here are tables with US date format...

Table1-Rates
EffDate   Rate
01/22/2004 2.5
02/27/2004 2.3
03/15/2004 2.1
03/29/2004 2.8


Table2-Tasks
TaskRef TaskDate
A001 01/24/2004
A002 01/31/2004
B003 02/27/2004
A015 03/02/2004
C005 03/16/2004
A014 03/19/2004
A003 03/31/2004
B001 04/14/2004

Softop

 

[nicsin]

select 
    rates.rate
from 
    rates inner join tasks on rates.EffDate = tasks.TaskDate 
where 
    tasks.TaskDate = #<some date>#

replace

<some date>

with the desired date

 

[cjowsey]

You need to define your problem better. Only one set of dates matches between the two tables. What do you want to do for the other task records? You could:
[ol][li]Return no row[/li][li]Return a Null rate[/li][li]Return the nearest rate in the future[/li][li]Return the nearest rate in the past[/li][/ol]
or maybe something totally different altogether.

 

[Softop]

Thanks nicsin for this but my problem is a little more complex.

Problem definition...

In Table1 are a list of rates. Only one rate can be in effect at a given time. On examination of the table a rate of 2.5 is to be used from 01/22/2004 until the next rate kicks in on 02/27/2004. This means that if there is a task starting between these dates (including the Effdate as the from date) then the corresponding rate of 2.5 is to be used.

By examining Table 2 and applying the rule the result I want to achieve is as follows...

TaskRef Rate (Reason)
A001 2.5 (TaskDate >= StartDate for rate 2.5 but < Startdate for rate 2.3)
A002 2.5 (TaskDate >= StartDate for rate 2.5 but < Startdate for rate 2.3)
B003 2.3 (TaskDate >= StartDate for rate 2.3 but < Startdate for rate 2.1)
A015 2.3 (TaskDate >= StartDate for rate 2.3 but < Startdate for rate 2.1)
C005 2.1 (TaskDate >= StartDate for rate 2.1 but < Startdate for rate 2.8)
A014 2.1 (TaskDate >= StartDate for rate 2.1 but < Startdate for rate 2.8)
A003 2.8 (TaskDate >= StartDate for rate 2.8)
B001 2.8 (TaskDate >= StartDate for rate 2.8)

Hope this is a little clearer. I appreciate your help.

Softop

 

[Softop]

Thanks for your reply too cjowsey. From the list you supplied I need the nearest rate in the past.

Softop.