locked
How to identify when there is a break in continuity of an integer on a column RRS feed

  • Question

  • Gurus,

    I have two columns, one column has a document ID and a given document can have many pages. The second column has the pages. Now I want to find out when the page number is broken. For example, if doc ID 1 has 3 rows and each of the three has 1,2,3 and then the fourth row has document 1 but the value jumps from 3 to 7 and then goes to 8,9,10 and then jumps again and starts from 17, i want to have the ranges identified.

    DocID Page Number 

    1    1

    1 2

    1 3

    1 7

    1 8

    1 9

    1 10

    1 17

    1 18

    19

    1    20

    The result should look like 

    DocID Page Number 

    1   1-3

    1     7-10

    1   17-20


    ebro

    Thursday, July 16, 2015 8:01 PM

Answers

  • Create table test (DocID int, PageNumber int)

    Insert into test values (1,1),(1,2),(1,3),(1,7),
    (1,8),(1,9),(1,10),(1,17),
    (1,18),(1,19),(1,20) 

    ;with mycte as (
    Select * , PageNumber- row_number() Over(Order by PageNumber) grp from test)

    Select DocID, Min(PageNumber),  Max(PageNumber) from mycte
    Group by DocID,grp


    drop table test
    • Marked as answer by Eric__Zhang Monday, July 20, 2015 5:47 AM
    Thursday, July 16, 2015 8:07 PM
  • This is very common problem and the name of it 'Gaps and Islands', you can google by it.

    Here is a nice and easy to understand article about this problem

    Refactoring Ranges

    I also did a quick search by Itzik Ben-Gan and this problem and found

    http://www.manning.com/nielsen/SampleChapter5.pdf


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by ebrolove Thursday, July 16, 2015 8:59 PM
    Thursday, July 16, 2015 8:09 PM

All replies

  • Create table test (DocID int, PageNumber int)

    Insert into test values (1,1),(1,2),(1,3),(1,7),
    (1,8),(1,9),(1,10),(1,17),
    (1,18),(1,19),(1,20) 

    ;with mycte as (
    Select * , PageNumber- row_number() Over(Order by PageNumber) grp from test)

    Select DocID, Min(PageNumber),  Max(PageNumber) from mycte
    Group by DocID,grp


    drop table test
    • Marked as answer by Eric__Zhang Monday, July 20, 2015 5:47 AM
    Thursday, July 16, 2015 8:07 PM
  • This is very common problem and the name of it 'Gaps and Islands', you can google by it.

    Here is a nice and easy to understand article about this problem

    Refactoring Ranges

    I also did a quick search by Itzik Ben-Gan and this problem and found

    http://www.manning.com/nielsen/SampleChapter5.pdf


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by ebrolove Thursday, July 16, 2015 8:59 PM
    Thursday, July 16, 2015 8:09 PM
  • Thank you very much!!

    I got what I want based on your lead.


    ebro

    Thursday, July 16, 2015 8:59 PM