Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rwaite
Starting Member
2 Posts |
 Posted - 2002-10-22 : 16:08:29
|
| I need to select the appropriate rate to apply to hours worked on a given date and calculate an amount. I've got the following tables:DateWork Employee #Hours DateWorked Jane Doe 8 11/1/02 Jane Doe 8 12/1/02 Jane Doe 8 2/1/03EmpRate Employee HourlyRate EffectiveDate Jane Doe $9 10/1/01 Jane Doe $10 10/1/02 Jane Doe $11 1/1/03My result should look like this: Employee DateWorked Amount Jane Doe 11/1/02 $72 Jane Doe 12/1/02 $80 Jane Doe 2/1/03 $88I can't figure out how to put together a join to perform this task.TIA. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-22 : 16:21:55
|
| SELECT DW.Employee, DW.DateWorked, DW.[#Hours]*ER.HourlyRate AS AmountFROM DateWork DW INNER JOIN EmpRate ER ON DW.Employee=ER.EmployeeINNER JOIN(SELECT D.Employee, D.DateWorked, Max(E.EffectiveDate) AS MaxDateFROM DateWork D INNER JOIN EmpRate E ON D.Employee=E.EmployeeWHERE D.DateWorked<=E.EffectiveDateGROUP BY D.Employee, D.DateWorked) A ON DW.Employee=A.Employee AND DW.DateWorked=A.DateWorked AND ER.EffectiveDate=A.MaxDateI'm pretty sure that'll do it, and I'm also pretty sure there's a more efficient way to do it, at least one that's a little shorter than this. |
 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
|
|
|
|
|
Rate Lookup Join