Hi,
I am running db2 v 7.2 under OS 390. I need to perform a sql query which satisfy my bellow requirement.

Have a table with fields appNo, appTimeStamp,appStatus

and has fields like,

appNo appTimestamp appStatus

1 2004-04-04-12.00.00 START
1 2004-04-04-12.04.00 COMPLETE
1 2004-04-04-12.12.00 HIRED

has any app numbers with like the above status..


My query is to fetch the records,

1. Select count of all appNo's (distinct) which the appTimestamp should be less than 4 minutes for the appStatus between "START" and "COMPLETE".

Any thoughts?

SELECT DISTINCT (a.appNo)
FROM table-name a
WHERE a.status = 'START'
AND exists
(SELECT b.appNo
FROM table-name b
WHERE b.appNo = a.appNo
AND b.status = 'COMPLETE'
AND (b.appTimestamp - 4 minutes) < a.appTimestamp )