find gaps in multiple sequences

Question

Post reply

find gaps in multiple sequences

stuartmarshall

SSC Journeyman

Points: 84
More actions
April 27, 2012 at 1:11 am

#273298

I have seen multiple articles about finding gaps in sequences but they treat all the numbers in the column as part of one sequence. I need a statement to find the gaps in multiple sequences in one column. the sequences can be grouped by another column in the same table. please see below:
Doc_NbrTicket_Nbr
12907
12908
12909
12912
12914
12915
141481
141484
141485
141486
141487
141489
141490
The results should be displayed:
Doctor_Number Missing_Ticket_Number
12 910
12 911
12 913
14 1482
14 1483
14 1488
Any help is greatly appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Thinky Night SSCommitted Points: 1663 More actions · Answer 1

I think, If you would use SQL Server 2012, here are some new functions lag and lead which will help you.

Thinky Night SSCommitted Points: 1663 More actions · Answer 2

I think, This gives you some idea :)))

declare @Table table (Doc_Nbr int,Ticket_Nbr int)

insert into @Table values

(12,907),

(12,908),

(12,909),

(12,913),

(12,915),

(12,919),

(14,1481),

(14,1484),

(14,1485),

(14,1486),

(14,1487),

(14,1489),

(14,1490);

WITH C AS(

select Doc_Nbr,Ticket_Nbr,DENSE_RANK() OVER (PARTITION By Doc_Nbr ORDER BY Ticket_Nbr) as rownum

from @Table

)

select * from (

select Cur.Doc_Nbr, Cur.Ticket_Nbr + 1 start_Range,Nxt.Ticket_Nbr - 1 as end_range

from c as Cur

Join C as Nxt

on Nxt.Rownum = Cur.rownum + 1 and Cur.Doc_Nbr = Nxt.Doc_Nbr

where nxt.Ticket_Nbr - Cur.Ticket_Nbr > 1

) a

But it is not the correct solution

Thinky Night SSCommitted Points: 1663 More actions · Answer 3

Here is the final solution:

declare @Table table (Doc_Nbr int,Ticket_Nbr int)

insert into @Table values

(12,907),

(12,908),

(12,909),

(12,913),

(12,915),

(12,919),

(14,1481),

(14,1484),

(14,1485),

(14,1486),

(14,1487),

(14,1489),

(14,1490);

WITH C AS(

select Doc_Nbr,Ticket_Nbr,DENSE_RANK() OVER (PARTITION By Doc_Nbr ORDER BY Ticket_Nbr) as rownum

from @Table

)

select b.Doc_Nbr,a.number

from MASTER.dbo.SPT_VALUES a ,(

select Cur.Doc_Nbr, Cur.Ticket_Nbr + 1 start_Range,Nxt.Ticket_Nbr - 1 as end_range

from c as Cur

Join C as Nxt

on Nxt.Rownum = Cur.rownum + 1 and Cur.Doc_Nbr = Nxt.Doc_Nbr

where nxt.Ticket_Nbr - Cur.Ticket_Nbr > 1

) b

where a.number>=b.start_Range and a.number <= b.end_range

stuartmarshall SSC Journeyman Points: 84 More actions · Answer 4

thank you very much! I will implement it tomorrow and post back the outcome.

Thinky Night SSCommitted Points: 1663 More actions · Answer 5

Also You can use this recursion instead of spt_values:

declare @Table table (Doc_Nbr int,Ticket_Nbr int)

insert into @Table values

(12,907),

(12,908),

(12,909),

(12,913),

(12,915),

(14,1481),

(14,1484),

(14,1485),

(14,1486),

(14,1487),

(14,1489),

(14,1490);

WITH cteSequence ( number) as

(

SELECT 800

UNION ALL

SELECT number+ 1

FROM cteSequence

WHERE number < 1500

) ,

C AS(

select Doc_Nbr,Ticket_Nbr,DENSE_RANK() OVER (PARTITION By Doc_Nbr ORDER BY Ticket_Nbr) as rownum

from @Table

)

select b.Doc_Nbr,a.number

from cteSequence a,(

select Cur.Doc_Nbr, Cur.Ticket_Nbr + 1 start_Range,Nxt.Ticket_Nbr - 1 as end_range

from c as Cur

Join C as Nxt

on Nxt.Rownum = Cur.rownum + 1 and Cur.Doc_Nbr = Nxt.Doc_Nbr

where nxt.Ticket_Nbr - Cur.Ticket_Nbr > 1

) b

where a.number>=b.start_Range and a.number <= b.end_range

OPTION ( MAXRECURSION 0);

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Sample data:

CREATE TABLE #Sample (Doc_Nbr int,Ticket_Nbr int)

INSERT INTO #Sample

SELECT 12,907 UNION ALL

SELECT 12,908 UNION ALL

SELECT 12,909 UNION ALL

SELECT 12,913 UNION ALL

SELECT 12,915 UNION ALL

SELECT 12,919 UNION ALL

SELECT 14,1481 UNION ALL

SELECT 14,1484 UNION ALL

SELECT 14,1485 UNION ALL

SELECT 14,1486 UNION ALL

SELECT 14,1487 UNION ALL

SELECT 14,1489 UNION ALL

SELECT 14,1490

Script:

SELECT

Ranges.Doc_Nbr,

MissingTicket = n.n

FROM (

SELECT Doc_Nbr, MINTicket_Nbr = MIN(Ticket_Nbr), MAXTicket_Nbr = MAX(Ticket_Nbr)

FROM #Sample

GROUP BY Doc_Nbr

) Ranges

CROSS APPLY (

SELECT TOP (1+MAXTicket_Nbr-MINTicket_Nbr) n = MINTicket_Nbr+ROW_NUMBER() OVER(ORDER BY a.[Name])-1

FROM sys.columns a, sys.columns b

) n

LEFT JOIN #Sample s ON s.Ticket_Nbr = n.n

WHERE s.Ticket_Nbr IS NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 7

Thinky Night (4/27/2012)
Also You can use this recursion instead of spt_values:
declare @Table table (Doc_Nbr int,Ticket_Nbr int)
insert into @Table values
(12,907),
(12,908),
(12,909),
(12,913),
(12,915),
(14,1481),
(14,1484),
(14,1485),
(14,1486),
(14,1487),
(14,1489),
(14,1490);
WITH cteSequence ( number) as
(
SELECT 800
UNION ALL
SELECT number+ 1
FROM cteSequence
WHERE number < 1500
) ,
C AS(
select Doc_Nbr,Ticket_Nbr,DENSE_RANK() OVER (PARTITION By Doc_Nbr ORDER BY Ticket_Nbr) as rownum
from @Table
)
select b.Doc_Nbr,a.number
from cteSequence a,(
select Cur.Doc_Nbr, Cur.Ticket_Nbr + 1 start_Range,Nxt.Ticket_Nbr - 1 as end_range
from c as Cur
Join C as Nxt
on Nxt.Rownum = Cur.rownum + 1 and Cur.Doc_Nbr = Nxt.Doc_Nbr
where nxt.Ticket_Nbr - Cur.Ticket_Nbr > 1
) b
where a.number>=b.start_Range and a.number <= b.end_range
OPTION ( MAXRECURSION 0);

Heh.. GOD NO! Don't use recursive CTEs for counting... ever. See the following article for why not.

http://www.sqlservercentral.com/articles/T-SQL/74118/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)