*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, workdateWith 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 browsexBase 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 browseCetin