asked on

How to determine sequence

Hi,
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.

Thanks!
QCseq.xls

Brian Crowe

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.

coperations07

ASKER

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.
QCseq.xls

coperations07

ASKER

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.

Brian Crowe

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 Crowe

I forgot you're running 2008R2...give me a few minutes to write one without LAG/LEAD

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

coperations07

ASKER

Thanks Brian. I'm trying to work with this some to see what it gets me.

coperations07

ASKER

I think this is going to get me going. Thanks