OK, here is the problem. I have to 'fix' some data that has got corrupted (a bug in some software has led to data corruption - it has gone unnoticed for some time and has left a right mess). Anyway...
I have order records that are made up of two parts:
An order pending
An order actioned
A simplified version of the order record would look like this:
The real record has many other fields but they are immaterial for the purposes of this question.
Data might look like this:
ID JobRef Status
1001 n/a Pending
1002 5001234 Actioned
1003 n/a Pending
1004 1000005 Actioned
1005 n/a Pending
1006 5001236 Actioned
1007 n/a Pending
1008 5001236 Actioned
I need to find all the records with a JobRef LIKE '500%' AND I also need to find the record with an ID that immediatly preceeds it. Otherwise I have no way of finding all those 'Pending' records that belong to the corresponding Actioned records!
If you have a clever answer to this I'm all ears because I'm fresh out of ideas.