SQL compare previous record lag offset conditional

199 Views Asked by At

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
0

There are 0 best solutions below