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