What if you took a slightly different tack?
CREATE TABLE FinishedWork (
EventTime INTEGER NOT NULL,
FileName TEXT NOT NULL,
ProcessID INTEGER NOT NULL,
BytesProcessed INTEGER NOT NULL,
isDuplicate integer -- tri-state: 0=not duplicate 1=duplicate
null=unknown
);
And then periodically run this:
update FinishedWork
set isDuplicate = case when exists(select 1 from FinishedWork fw2 where
fw2.ProcessId=FinishedWork.ProcessId and fw2.FileName=FinishedWork.Filename
and fw2.rowid < FinsishedWork.rowid) then 1 else 0 end where isDuplicate is
null;
Then your report would be this:
SELECT ProcessID, sum(BytesProcessed)
FROM FinishedWork
WHERE EventTime > {20 minutes ago}
AND isDuplicate=0;
By the way, what's magic about 20 minutes ago?
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
