Q

Runs of sequential numbers, part 1

I need to get the maximum and minimum of the serial numbers with available status (A) or sold status (B) from the

following table.

SerialNo  Loc_ID  Status
  01       NY      A
  02       NY      A
  03       NY      A
  04       NY      B
  05       NY      B
  06       NY      A
  07       NY      A
  08       NY      B
  09       NY      A
  10       NY      A

I want the range of available (A) serial numbers for a specific location, like this:

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

I want the same for the sold serial numbers in a different query. Please help me to build these queries.

Because we ultimately want two SerialNo values on the same row of the final result set, we'll need a self-join. That is, we'll join two copies of the table, t1 and t2. The join conditions will be matching Status, matching Loc_ID, and the SerialNo in the t1 row being less than or equal to the SerialNo in the t2 row:

select t1.Loc_ID
     , t1.SerialNo as s1
     , 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'   
order 
    by t1.Loc_ID
     , t1.SerialNo 
     , t2.SerialNo

This produces a lot of results, too many to list. For example, the row with t1.SerialNo 03 will produce the following rows in the self-join:

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

Notice that 03 is matched to every Status 'A' SerialNo that is greater than or equal to 03. This is a typical theta join.

Now let's add a condition. We want there to be no Status 'B' SerialNo in between the t1.SerialNo and the t2.SerialNo in our self-join.

select t1.Loc_ID
     , t1.SerialNo as s1
     , 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'
        )   
order 
    by t1.Loc_ID
     , t1.SerialNo 
     , t2.SerialNo

This produces a much smaller result set, so we can list it all here:

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

Notice that there is no Status 'B' in between each pair of Status 'A' SerialNos. In particular, all the rows for t1.SerialNo 03 were eliminated except one.

Please proceed to Runs of sequential numbers (part 2 of 2).

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