How to determine sequence

I'm working on a QC App that is used to check contents of an order. There is a particular sequence in which the items are supposed to be in the box. Each time the user scans one of the items it's record is updated with the sequence in which it was scanned.  I need to find the best way to determine what is in sequence. I have a function in place that loops through looking for each sequence number and flags the records out of sequence if it doesn't match the expected. The problem I've found with this it that if say expected sequence 1 is 100 packages in then it would count 100 packages out of sequence.  I just thought I'd post here to see if anyone has experience dealing with this scenario or if there are some suggestions that I could try. I've attached an example order.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
It seems to me that the records you are would would to flag are those where their sequence is greater than the sequence of the proceeding record (i.e. the 1st, 2nd, and 5th rows) when the rows are ordered by expected sequence as your example is.  If this is the case then you can just use the LEAD funtion assuming you are using SQL 2012 or later.

If you can verify that this is the logic you want to apply we can help with a specific query.
coperations07Author Commented:
I think that can be part of it. Adding to your example, I would consider record 3 to be in sequence, but it is less than the previous record 2 sequence. Maybe I shouldn't consider record 3 to be in, but it seems like the start of what is in sequence.
coperations07Author Commented:
Didn't mean to post that yet....
I reattached the same data with the ones I think would be out of sequence highlighted.

Also I am on SQL 2008 r2.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Brian CroweDatabase AdministratorCommented:
The first step is to define the logic that we are going to use to flag a record as "out of sequence".  It's not enough to just say "i know it when I see it".  We need quantifiable logical rules.

If we use the logic...

A record is out of sequence when:
scan_seq is less than the scan_seq of the following record

This actually catches most of your highlighted rows.

WITH cteQCseq AS
	SELECT order_id, agin, seq, scan_seq, exp_seq,
		scan_seq - exp_seq AS Diff,
		LAG(scan_seq) OVER(PARTITION BY order_id ORDER BY exp_seq) AS PrevScan_Seq,
		LEAD(scan_seq) OVER(PARTITION BY order_id ORDER BY exp_seq) AS NextScan_Seq
	FROM Practice.dbo.QCseq
SELECT order_id, agin, seq, scan_seq, exp_seq
FROM cteQCseq
WHERE NextScan_Seq < scan_seq
ORDER BY order_id, exp_seq

Open in new window

Brian CroweDatabase AdministratorCommented:
I forgot you're running 2008R2...give me a few minutes to write one without LAG/LEAD
Brian CroweDatabase AdministratorCommented:
SELECT QCseq_Curr.order_id, QCseq_Curr.agin, QCseq_Curr.seq,
	QCseq_Curr.scan_seq, QCseq_Curr.exp_seq
FROM dbo.QCseq AS QCseq_Curr
	ON QCseq_Curr.order_id = QCseq_Next.order_id
	AND QCseq_Curr.exp_seq = QCseq_Next.exp_seq - 1
WHERE QCseq_Curr.scan_seq > QCseq_Next.scan_seq

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
coperations07Author Commented:
Thanks Brian. I'm trying to work with this some to see what it gets me.
coperations07Author Commented:
I think this is going to get me going. Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.