Help with Contiguous Sequences in SQl

  • I have data that contains a sequence.  Such as:

    store_numberaislesidesectionshelfseq
    30175112463
    30175112464
    30175112465
    309411021116
    309411021117
    311271031277
    311271031278
    311271031279
    3112710312712

    I am trying to write code that will tell me when a store has Contiguous Sequences on the same shelf.  The top 2 stores should be returned.  Store number 31127 should not.  Any help would be greatly appreciated.  I assume there is some way to do this with math.

  • Assuming that the seq will not be reused for a given shelf, I believe what you are after can be done with three subselects in the WHERE clause. It won't perform the greatest, but should function. Add a condition such that the MAX(seq) minus the MIN(seq) equals the COUNT(*).

    SELECT * FROM MyTable A

    WHERE ( SELECT MAX(seq) FROM MyTable B WHERE B.store_number = A.store_number AND B.aisle = A.aisle AND B.side = A.side AND B.section = A.section AND B.shelf = A.shelf)

              - ( SELECT MIN(seq) FROM MyTable B WHERE B.store_number = A.store_number AND B.aisle = A.aisle AND B.side = A.side AND B.section = A.section AND B.shelf = A.shelf)

              = ( SELECT COUNT(*) FROM MyTable B WHERE B.store_number = A.store_number AND B.aisle = A.aisle AND B.side = A.side AND B.section = A.section AND B.shelf = A.shelf)

     

  • Assuming that the entire shelf must be made up of consecutive seq numbers then the following query should give you what you want:

     

      SELECT store_number, aisle, side, [section], shelf,

             MIN(seq) AS 'MIN_seq', MAX(seq) AS 'MAX_seq', COUNT(DISTINCT seq) AS 'seq_COUNT'

        FROM YOUR_TABLE_NAME

       GROUP BY store_number, aisle, side, [section], shelf

      HAVING (MAX(seq) - MIN(seq)) + 1 = COUNT(DISTINCT seq)

  • The Shelf does not always contain a pefect sequence (1-5).  No shelf will have a duplicate sewquence.  This gets me some information. 

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply