Ask the Expert

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.

    Requires Free Membership to View

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

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: