I'm new to QV, looking for some advice on the following;
I have 2 tables containing a RATE per hour and the HOURS worked, linked by a common ID field.
| TABLE 1 |
| ID | START_DATE
| END_DATE | RATE |
|---|
| 9 | 01-JAN-13 | 04-JAN-13 | $50 |
| 9 | 05-JAN-13 | 31-JAN-13 | $55 |
| TABLE 2 |
| ID | DATE | HOURS
|
|---|
| 9 | 12-JAN-13 | 4 |
How can I select the appropriate RATE in TABLE 1 to match the HOURS worked in TABLE 2 on a certain date to give me a value ?
In the example, employee 9 worked 4 HOURS on the 12-JAN-13 and this equates to a RATE of $55, (as 12-JAN-13 falls between the START_DATE 05-JAN-13 and END_DATE 31-JAN-13) which is $220 (4 HOURS * $%% per hour).
Any advice welcome.
