Joining tables on sequential values 1

[azzazzello]

Hi,

I have 2 tables. One table is called "data". It's defined as follows

date DATE NOT NULL
total int NOT NULL

It is indexed by date and has, say, 300K records per date, and a total of 45M records with a range of dates from, say ,20090101 to 20110401

second table is called "rate" and is defined as follows

date DATE NOT NULL
rate float (10,4) NOT NULL

It has 3 entries:
19800101,5.0
20100525,4.0
20110225,3.0

This basically defines a range of dates for which a rate is valid

I want to return every row from "data" such I get

data.date,total,total*rate where rate is from a rate.date that is CLOSEST AND LESS THAN that of the data.date

I hope this can be done fairly efficiently despite the fact that the "data" table is huge

 

[r937]

SELECT data.date
     , data.total
     , data.total * rate.rate   as balance
  FROM data
INNER
  JOIN rate
    ON rate.date =
       ( SELECT MAX(date)
           FROM rate
          WHERE date < data.date )

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[azzazzello]

Wow, this is hot! thanks, Rudy!