>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.

Steve,
I don't know of a way to do that in one SQL in VFP. Second it's arguable if SQL would be faster than xbase approach.

*Create and check test data
Create Cursor persons (personID i, pname c(10))
Create Cursor wagehistory (personID i, wagedate d, rate i)
Create Cursor workhrs (personID i, workdate d, hrs i)
Local ix
For ix = 1 To 10
    Insert Into persons (personID, pname) Values (ix, Sys(2015))
    Insert Into wagehistory (personID, wagedate, rate) ;
        VALUES (ix, Date()-1001, 100)
    For jx=1 To Ceiling(Rand()*5)
        Insert Into wagehistory (personID, wagedate, rate) ;
            VALUES (ix, Date()-Int(Rand()*1000), Ceiling(Rand()*1000))
    Endfor
    For px=1 To Ceiling(Rand()*10)
        Insert Into workhrs (personID, workdate, hrs) ;
            VALUES (ix, Date()-Int(Rand()*1000), Ceiling(Rand()*8))
    Endfor
Endfor
Select persons
Index On personID Tag personID
Select workhrs
Index On personID Tag personID
Index On workdate Tag workdate
Index On BinToC(personID)+Dtoc(workdate,1) Tag workmain
Select wagehistory
Index On personID Tag personID
Index On wagedate Tag wagedate
Index On BinToC(personID)+Dtoc(wagedate,1) Tag wagemain Descending

SELECT * from persons
SELECT * from wagehistory ORDER BY personID, wagedate
SELECT * from workhrs ORDER BY personID, workdate

With 2 SQLs approach :

Select wk.personID, wk.workdate, Max(wg.wagedate) As wagedate ;
    FROM workhrs wk ;
    INNER Join wagehistory wg On wk.personID = wg.personID ;
    where (wg.wagedate <= wk.workdate) ;
    GROUP By  wk.personID, wk.workdate ;
    INTO Cursor crsPass1 ;
    nofilter

Select pr.personID, pr.pname, ;
    wg.wagedate, wg.rate, ;
    wk.workdate, wk.hrs, wk.hrs * wg.rate As Calced ;
    FROM persons pr ;
    INNER Join crsPass1 p1 On p1.personID = pr.personID ;
    INNER Join workhrs wk On wk.personID = pr.personID ;
    AND wk.workdate = p1.workdate ;
    INNER Join wagehistory wg On pr.personID = wg.personID ;
    AND p1.wagedate = wg.wagedate ;
    ORDER By pr.personID, wk.workdate ;
    INTO Cursor crsPays ;
    nofilter
browse

xBase approach :

Set Near On
Create Cursor pays ;
  (personID i, pname c(10), wagedate d, rate i, workdate d, hrs i, pay i)
Select persons
Scan
    If Seek(BinToC(persons.personID),'workhrs','workmain')
        Select workhrs
        Scan While personID = persons.personID
          =Seek(BinToC(personID)+Dtoc(workdate,1), 'wagehistory', 'wagemain')
          Insert Into pays ;
              (personID, pname, wagedate, rate, workdate, hrs, pay) ;
              VALUES ;
              (persons.personID, persons.pname, ;
              wagehistory.wagedate, wagehistory.rate, ;
              workhrs.workdate, workhrs.hrs, ;
              workhrs.hrs * wagehistory.rate)
        Endscan
        Select persons
    Endif
Endscan
Select pays
browse

Cetin