stclr - 1 year ago 101
SQL Question

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

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.

Answer Source

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download