Q

Runs of sequential numbers, part 2

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

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

Dig deeper on SQL Server Database Modeling and Design

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close