Razzak and all,
The more I played around I realized that there not be a value for the
previous week
which really messed with my head and the math.
So I just brute forced it with a cursor. Takes less than a second.
SET ERROR MESSAGE 2038 OFF
DROP TABLE tLastValue
SET ERROR MESSAGE 2038 ON
CREATE TEMPORARY TABLE `tLastValue` +
(`EmpID` INTEGER, +
`LastValue` REAL , +
`CurrentValue` REAL, +
`DiffValue`=(CurrentValue-LastValue) REAL)
INSERT INTO tLastValue EmpID SELECT EmpID FROM Employee
SET VAR vAmount REAL = NULL
SET ERROR MESSAGE 705 OFF
DROP CURSOR c1
SET ERROR MESSAGE 705 ON
DECLARE c1 CURSOR FOR SELECT EmpID +
FROM tLastValue
OPEN c1
FETCH c1 INTO vVar1 INDIC ivVar1
WHILE SQLCODE <> 100 THEN
SELECT Amount INTO vAmount INDIC ivAmount+
FROM Action WHERE EmpID = .vVar1 AND Amount IS NOT NULL AND COUNT = LAST
UPDATE tLastValue SET LastValue = .vAmount WHERE CURRENT OF c1
SET VAR vAmount = NULL
FETCH c1 INTO vVar1 INDIC ivVar1
ENDWHILE
DROP CURSOR c1
RETURN
Have a great weekend!
Jan
