Ask the Expert

Runs of sequential numbers, part 2

Please see Runs of sequential numbers (part 1 of 2) for the complete problem statement.

    Requires Free Membership to View

So far, we've developed a self-join query with a NOT EXISTS subquery that produces the following results:

 Loc_ID s1 s2 NY 01 01 NY 01 02 NY 01 03 NY 02 02 NY 02 03 NY 03 03 NY 06 06 NY 06 07 NY 07 07 NY 09 09 NY 09 10 NY 10 10

Each row represents a pair of Status 'A' SerialNos which have no intervening Status 'B' SerialNo. However, there are multiple rows for each t1, so let's just take the pair with the highest t2.SerialNo for each t1.SerialNo.

 select t1.Loc_ID , t1.SerialNo as s1 , max(t2.SerialNo) as s2 from yourtable as t1 inner join yourtable as t2 on t1.Status = t2.Status and t1.Loc_ID = t2.Loc_ID and t1.SerialNo <= t2.SerialNo where t1.Status = 'A' and not exists ( select 1 from yourtable where Loc_ID = t1.Loc_ID and SerialNo between t1.SerialNo and t2.SerialNo and Status = 'B' ) group by t1.Loc_ID , t1.SerialNo

This query gets us part way to the final goal:

 Loc_ID s1 s2 NY 01 03 NY 02 03 NY 03 03 NY 06 07 NY 07 07 NY 09 10 NY 10 10

Now all we have to do is select only those rows which have the lowest s1 for each s2.

 select Loc_ID as Location , min(s1) as SerNo_From , s2 as SerNo_To from ( select t1.Loc_ID , t1.SerialNo as s1 , max(t2.SerialNo) as s2 from yourtable as t1 inner join yourtable as t2 on t1.Status = t2.Status and t1.Loc_ID = t2.Loc_ID and t1.SerialNo <= t2.SerialNo where t1.Status = 'A' and not exists ( select 1 from yourtable where Loc_ID = t1.Loc_ID and SerialNo between t1.SerialNo and t2.SerialNo and Status = 'B' ) group by t1.Loc_ID , t1.SerialNo ) as dt group by Loc_ID , s2

This gives us the desired final result:

 Location SerNo_From SerNo_To NY 01 03 NY 06 07 NY 09 10

To obtain a list of Status 'B' numbers, just switch 'A' and 'B' in the above query.

This was first published in December 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: