How to Assign Numbers to a Set of Rows Partitioning Based on a Gap in Consecutive Numbers SQL

Question

Hello Development Community,

Is there a way to assign a number to a group of rows partitioning based on a gap in a field of consecutive numbers in SQL? I've been searching/trying various things for an answer to this for a few days and have come up empty. Please consider the example:

CREATE TABLE #example
(ID int, Service_Date date, Item_Num int, Desired_Column int)

INSERT INTO #example VALUES
('1111', GetDate(), 4, 1),
('1111', GetDate(), 5, 1),
('1111', GetDate(), 7, 2),
('1111', GetDate(), 8, 2),
('1111', GetDate(), 9, 2),
('1111', GetDate(), 11, 3),
('1111', GetDate(), 12, 3),
('1111', GetDate(), 13, 3)

I am trying to assign the values in Desired_Column but am failing. A new number should be assigned each time there is a gap in consecutive Item_Num values. I've tried multiple approaches using DENSE_RANK(), PARTITION BY, NTILE(), finding the differenece between the first/next row item number, but I just can't get this working. Is this even possible?

Thanks for taking the time, it is appreciated.

Google "SQL Gaps and Islands" problem and you will find all the examples you could possibly want. — Tab Alleman, Aug 19 '16 at 19:35
Possible duplicate of [How do I find a "gap" in running counter with SQL?](http://stackoverflow.com/questions/1312101/how-do-i-find-a-gap-in-running-counter-with-sql) — Tab Alleman, Aug 19 '16 at 19:36
`Management Studio` is just the client, the DBMS is SQL Server 2012 :-) Then my solution should work as-is. — dnoeth, Aug 19 '16 at 19:43

score 1 · Accepted Answer · answered Aug 19 '16 at 19:41

This is a gaps & islands problem, a common solution applies nested Analytical Functions. First you calculate a flag based on a condition (here: there's a gap > 1 between the current and the previous row) and then you do a Cumulative Sum over that flag:

with cte as
 (

    select ...,
       case when lag(Item_Num) over (partition by ID order by Item_Num) + 1 
              = Item_Num
            then 0 -- gap = 1 -> part of the previous group
            else 1 -- gap > 1 ->new group
       end as flag
    from #example
 )
select ...,
   sum(flag) over (partition by ID order by Item_Num) 
from cte

How to Assign Numbers to a Set of Rows Partitioning Based on a Gap in Consecutive Numbers SQL

1 Answers1