>>>Hi all
>>>
>>>I've inherited a system and have a tricky query.
>>>
>>>There is a table of pay rates with effective date ranges. There is also an employment history. An employee can be employed across multiple rate periods or within one or beginning within one and ending within another, etc.
>>>
>>>Initially I thought to do a union for each combination. I also thought of one join for each combination. Is it possible to avoid a cross join?
>>>
>>>A rate Foreign key in work.dbf wouldn't work - pun intended - because every new rate would have to generate new work records.
>>>
>>>
Employee.dbf
>>>EmpID EmpName
>>>1 Emp1
>>>2 Emp2
>>>
>>>Work.DBF
>>>WrkPK Wrk_EmpID dWrkStart dWrkTerminated
>>>1 1 2007-01-01
>>>2 2 2007-01-04 2007-01-20
>>>
>>>Rates.dbf
>>>RatPK Ratdaily dRatStart dRatEnd
>>>1 11 2006-12-31 2007-01-05
>>>2 12 2007-01-06
>>>
>>>So what I'd like to see is:
>>>
>>>
Query from 2007-01-01 until 2007-10-29
>>>EmpID EmpName Pay
>>>1 Emp1 55 + 3552 = 3607
>>>2 Emp2 22 + 0168 = 0190
>>>
>>>Thanks
>>
>>What is 55 and 3552 for the Emp1?
>
>It represents pay.
>
>$11 (ratdaily) * number of days from 2007-01-01 - 2007-01-05 (5) = 55
>$12 * (2007-10-29 - 2007-01-06) = 12 * 296 = 3552
>
>HTH
I think it should be easier to use SCAN...ENDSCAN command to do that.
I'm thinking for SQL approach, but not sure if I could find one :-)
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.