Hi steven,

I've been in your situation many times. The best solution I found is to include another field in your wages table which indicated the enddate on which the wage is valid. You'll have to write some code when saving a wages record but that should not be too difficult.

Then you could use a simple BETWEEN function to join the Employee and Wages table.


If you don't want to do this you'll have to something like this

SELECT Employee.* , MAX(date) as wagedate, wage ;
   FROM Employee INNER JOIN Wages ON Emp_pk = Wage.emp_fk ;
   WHERE Date =< DATE() ;
   GROUP BY Emp_id

However, this is tricky because strategy this does not work in SQL server and I think it does not in VFP 8 (with the SET ENGINEBEHAVIOUR 80) also.

Another strategy is to have subquery:

SELECT Employee.*, Date as wagedate, wage ;
   FROM Employee INNER JOIN Wages ON Emp_pk = Wage.emp_fk ;
   WHERE STR(Emp_pk)+DTOS(date) IN ;
      (SELECT STR(Emp_fk)+DTOS(MAX(date)) FROM Wages WHERE date =<DATE() GROUP BY Emp_fk)

Or split them up into two Queries

SELECT Emp_fk, MAX(date) FROM Wages GROUP BY Emp_fk WHERE date <=DATE() INTO CURSOR CurrentWages
SELECT * FROM Employee INNER JOIN CurrentWages ON emp_pk = emp_fk 

Good luck,

Walter,

>Hi all,
>
>I've been struggling for many days trying to create a query for the following situation:
>
>Employees are stored in a wages table with a field for name, date, wage, etc . Wages change periodically.The employee name, along with the date of change and the new wage are appended periodically.
>
>Daily work is stored in a seperate table which has a field for the employee name, man-hours etc.
>
>Now, I require a query, so that over a period of time, as the date changes, it keeps pace with the date of wage change, and picks up the relevant wage of the day.
>
>Not been possible !!
>
>This is the last thing I have done :
>
>In wage table, indexing on date in descending order. When a particular date is encountered in the dailywork table, look for which wage-period it belongs to in the wage table.
>For example, skip on the wage table in a do while loop, until the date of the dailywork table is less than the current date in the wage table. The previous date, along with the attached wage is the wage for that day/period.
>
>Had no success at all ! Help !
>
>Steve.