troubleshooting Question

Troublesome retrieve to find a list of records and the records immediatly before...

Avatar of splanton
splantonFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
4 Comments1 Solution302 ViewsLast Modified:
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:

ID [int]
JobRef [varchar](10)
Status [varchar](10)

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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros