I have a situation, where a record which is out of order in business terms should be excluded, means only valid previous records should be compared using analytical query like lag (or a similar implementation, I am trying to use DB2 for z/os 10.0, where lag is not yet available). based on the Inputs from the this forum, I will adapt the solution to DB2. First 3 records are all valid records as they follow correct order of the case. compare with previous record, if not valid flag it and then compare the next with previous VALID record. In the next case (case 2) Record 4 is good, but record 5 should not be compared instead record 6 should be compared with Previous valid (which is record 4).
Hope this makes sense.
Rec.No. case_no, case_date, status
1 1 11/10/2014 Rcvd
2 1 11/11/2014 Pndg
3 1 11/12/2014 Clos
4 2 11/10/2014 Pndg
5 2 11/12/2014 Rcvd --INVALID record as Rcvd should be done first, so flag this
6 2 11/13/2014 Clos --valid record in comparision to record 4