How to find Missing Numbers in a table using sqlserver?

Question

I want to find Missing Numbers in a table..Table Like this.

   Sno                Branch 

    1                   ABC
    2                   ABC
    3                   ABC
    5                   ABC   // 4th sno is missing
    6                   ABC
    8                   ABC   // 7th sno is missing
   10                   ABC   // 9th sno is missing

I found the missing SNo using this Query

 ALTER proc [dbo].[K_RT_DCNoMissing]--1,50 
 as
 begin

 declare @id int
  set @id = 0
 declare @maxid int

 --set @id = @fromvalue
 select @maxid = (select count(*) dcno  from K_RT_Dailyentryretail nolock)   


create table #IDSeq
(
id int 
)

 while 0<@maxid--whatever you max is
 begin
insert into #IDSeq values(@id)

set @id = @id + 1
set @maxid = @maxid - 1
-- print @maxid
end

select 
s.id  
from 
#idseq s 
left join K_RT_Dailyentryretail t on 
    s.id = t.dcno 

  where t.dcno is  null  order by s.id asc

  drop table #IDSeq

  end

I am getting out put like this..

  MissingNo's
    4
    7
    9

Now I want to Display Sno with Branch Name like.

    MissingNo's           Branch
      4                   ABC
      7                   ABC
      9                   ABC

How can i get the branch name...

Am getting output as
4    abc
4    cde
4    fgh
7    abc
7    cde
7    fgh

but what actually am expecting is
4    abc
7    cde
.     ..
.     ..

etc.

how are you supposed to get the branch from a row that does not exists? btw the stor proc you post cannot work because of a inner select broken by a comment line. — Paolo
– Paolo, Commented Sep 24, 2014 at 10:11
If number is missing between, then alwasy GAP is only for 1 digit? Will this happen? like 1,2,3,6 ? after 3 GAP is there but next number is not 5, it is bigger than that. Will this happen? — AK47
– AK47, Commented Sep 24, 2014 at 10:14

Rhumborl · Accepted Answer · 2014-09-24 12:29:54Z

2

You can use a CTE to build a table of all the branches and the full range of numbers for each. Then left join that to the main table to find what is missing. This will allow you to get consecutive missing numbers, e.g. 3, 4 and 5 missing. You can adjust @minid and @maxid to whatever you want your range to be. If @maxid can be greater than 32767, then you will need to do something with batches of ranges.

declare @minid int, @maxid int

set @minid = 1
set @maxid = (select count(*) dcno from K_RT_Dailyentryretail with (nolock))

; with Branches as (
    select distinct Branch from K_RT_Dailyentryretail
)
, CTE as
(
    select @minid as Sno, Branch from Branches
    union all
    select Sno + 1, Branch from CTE where Sno < @maxid
)

select  CTE.Sno, CTE.Branch from CTE
    left outer join K_RT_Dailyentryretail k on k.Branch = CTE.Branch and CTE.Sno = k.Sno
where k.Sno is null
option (MAXRECURSION 32767);

SQLFiddle here: http://sqlfiddle.com/#!3/13653/13

edited Sep 24, 2014 at 12:29

answered Sep 24, 2014 at 10:39

Rhumborl

16.7k4 gold badges41 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ramarao Over a year ago

; with Branches as ( select distinct Branch from K_RT_Dailyentryretail ...Here i get an error message like ..The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

santhosha Over a year ago

am getting output as 1 abc,1 cde,1 cde. that means for one sno repeating the branch names.

santhosha Over a year ago

k like any missing numbers. Those are coming for two branches

AK47 · Accepted Answer · 2014-09-24 10:15:36Z

0

I am considering that GAP between you number will be always of only 1 digit. In this case following will work for you,

;With CTE as
(
select Sno,Branch,ROW_NUMBER() over (order by Sno) R
 from C
)
select a.Sno+  1,a.Branch from CTE as a
left join CTE as b on a.R + 1= b.R 
where a.Sno + 1 <> b.Sno

One more thing is that, for missing row you want branch, I have thought that for missing row you want to have branch of previous row.

answered Sep 24, 2014 at 10:15

AK47

3,8073 gold badges21 silver badges37 bronze badges

Comments

Turophile · Accepted Answer · 2014-09-24 10:27:27Z

0

Assuming that each branch has it's own set of sno numbers, this works:

select sno-1 as sno, branch
from t1
where sno-1 NOT in (select sno from t1 as t2 where t1.branch = t2.branch)
and sno > 1
;

Which you can check here: http://sqlfiddle.com/#!3/daa81d/3

If sno is a unique key, let me know and I will revise the answer.

EDIT: Like AK47's answer, I'm assuming that the gap is only one row.

answered Sep 24, 2014 at 10:27

Turophile

3,4151 gold badge15 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to find Missing Numbers in a table using sqlserver?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related