MS Access query - use the correct rate depending on date?

Question

Hi,

I have two tables in Access, one for Rates, and another for peoples Time every week. They look like this for example:

(Note all dates are in dd/mm/yy format)

Rate Table:

-----------------

PERSON RATE EFFECTIVE_FROM

James 50.00 01/01/2007

James 60.00 04/05/2008

James 70.00 10/10/2008

Time table

-----------------

PERSON WEEK_ENDING HOURS

James 11/01/2007 10

James 22/05/2008 5

I'd like a query which will show me the correct rate to use for the persons time, so 2 records for James, one showing 10 hours with a rate of 50, and another showing 5 hours at a rate of 60.

How can I do this in access/sql ? Please help.

Answer 1

Sounds like a job for a correlated subquery. Try this:

SELECT T.person AS 'Employee', T.week_ending AS 'Week Ending', T.hours AS 'Hours', R1.rate AS 'Effective Rate'

FROM rate R1, time T

WHERE R1.person = T.person

AND R1.effective_from =

(SELECT MAX(R2.effective_from)

FROM rate R2

WHERE R2.person = T.person

AND R2.effective_from < T.week_ending)

(Note: this assumes that an effective_from is always computed for a full week going forward. If an effective_from covers just part of aparticular week, it'd be a far messier problem. However, since you only have hours broken out by week, I assumed this was not the case.)

Edit: A correlated subquery always tends to be something of a CPU/time hog, since it has to evaluate the inner query for each row of the outer. However, I can't think of any other purely-SQL solution for the problem. Not sure that a solution involving a multi-step script would gain you much timewise either - the base algorithim still would have to do some sort of lookup of the Rate table for each row of the Time table.

I see only two other possibilities:

1) Denormalize by adding a rate column to the Time table which is populated from Rate via a trigger when you insert/modify the row. If the Time table rows are inserted as a manual process, the extra time for the trigger to execute would be unnoticable. Of course, this would expose you to the usual danger of denormalizing - if a rate is changed in Rate, you have to remember to update Time as well or your data will be inconsistent.

2) Modify your Rate table by adding an effective_thru column. You then could eliminate the subquery and use a BETWEEN instead. (This is the sort of structure I tend to encounter and use myself.) If you go this way, I'd add a trigger to execute a stored procedure whenever you add/modify Rate to ensure that you don't have overlapping effective periods between rows for the same person.