>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

Yours computation is exact ?

<PRE>
CREATE CURSOR Employee (EmpID I,   EmpName V(20))
INSERT INTO Employee VALUES (1,       'Emp1')
INSERT INTO Employee VALUES (2,       'Emp2')

CREATE CURSOR Work(WrkPK I, Wrk_EmpID I,   dWrkStart D,   dWrkTerminated D NULL)

INSERT INTO Work VALUES (1,     1,  DATE(2007,01,01),NULL)
INSERT INTO Work VALUES (2,     2,  DATE(2007,01,04),DATE(2007,01,20))

CREATE CURSOR  Rates	(RatPK I,  Ratdaily Y,      dRatStart D,    dRatEnd D NULL)

INSERT INTO Rates	VALUES (1,     11,  DATE(2006,12,31),DATE(2007,01,05))
INSERT INTO Rates	VALUES (2,     12,  DATE(2007,01,06), NULL)

dEnd = DATE()

* LOOK FOR THE MIN AND MAX aggregation within a AGGREGATION SQL FUNCTION !!!!
SELECT Wrk_EmpID EmpID ; 
	,	(SELECT EmpName FROM Employee WHERE Work.Wrk_EmpID = Employee.EmpID) EmpName ;
	,	SUM((	MIN(NVL(dRatEnd,m.dEnd) , NVL(dWrkTerminated,m.dEnd));
		-	MAX(dWrkStart,dRatStart) + 1)* Ratdaily ) Pay ;
		FROM Work JOIN Rates ON dWrkStart <= NVL(dRatEnd,m.dEnd) AND dRatStart <= NVL(dWrkTerminated,m.dEnd);
		GROUP BY Wrk_EmpID