Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to find Missing Numbers in a table..Table Like this.

C#
        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

SQL
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
    --,(select wd.branch from K_RT_Dailyentryretail d inner join K_RT_WarehouseDetails wd on d.branchdate=wd.sno where d.dcno is not null) as branch
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..

C#
MissingNo's
      4
      7
      9



Now I want to Display Sno with Branch Name like.

C#
    MissingNo's           Branch
         4                   ABC
         7                   ABC
         9                   ABC


How can i get the branch name...
Posted
Updated 23-Sep-14 19:29pm
v4
Comments
George Jonsson 24-Sep-14 0:55am    
Do you want to do it in c# or SQL?
[no name] 24-Sep-14 1:02am    
I updated my Question..I want to use SQL..
Gihan Liyanage 24-Sep-14 1:35am    
Are you sure, the missing ID's has same Branch Name 'ABC' ?
[no name] 24-Sep-14 1:54am    
Yes..
DiponRoy 24-Sep-14 3:00am    
what is the abstract relation between the Sno and Branch column? Like is there any other tables to explain the relation

Hi,
Do you want to find the Missing No from a Table .If so check my below query i have created a sample for you.

SQL
-- I have created a Sample Table for you
create table K_RT_Dailyentryretail  (
    SNO int not null primary key,
    Branch  varchar(10)
);
-- here insert sample data to the table with missing no.
insert into K_RT_Dailyentryretail (SNO,Branch) values
    (1,'ABC'), 
	(2,'ABC'), 
	(3,'ABC'), 
	(5,'ABC'),
	 (6,'ABC'),
	  (8,'ABC'), 
	  (10,'ABC') 
--select * from K_RT_Dailyentryretail

-- Here is the query to select only the missing No from the given table.

select KRD.SNO + 1 as MissingNos
from K_RT_Dailyentryretail as KRD
  left outer join K_RT_Dailyentryretail as LKRD on KRD.SNO + 1 = LKRD.SNO
where 
LKRD.SNO is null
 
Share this answer
 
Comments
[no name] 24-Sep-14 1:56am    
In your select Query Get Missing Numbers..Right,But is it possible to shows Sno with Branch Name
syed shanu 24-Sep-14 1:57am    
oops you can do that.chk this query.is this are you asking.


select KRD.SNO + 1 as MissingNos,KRD.Branch
from K_RT_Dailyentryretail as KRD
left outer join K_RT_Dailyentryretail as LKRD on KRD.SNO + 1 = LKRD.SNO
where
LKRD.SNO is null
I can't see how you can do that, unless all rows in the table have the same Branch.
SQL
SELECT DISTINCT Branch FROM K_RT_Dailyentryretail;

But that is most likely not the case, I would presume.

The rows are deleted, right? So where to get the info?

One option could be to add a column called Status that has the values Active/Inactive and instead of deleting the row, you change the status.

Another option is to store deleted rows in its own table.
 
Share this answer
 
v3
Comments
[no name] 24-Sep-14 1:25am    
I am not deleted rows in my table? My table entered as it is..
whenever user forget an Sno ,then we get the Branch wise missing sno's
George Jonsson 24-Sep-14 1:32am    
Good piece of information to add to you question.
Still, I don't see how you can get the value of the Branch column if there is no information.
You say you get missing sno 'Branch wise'. How is the data sent to you?
SQL
declare @id int
set @id = 0
declare @maxid int
 
--set @id = @fromvalue
select @maxid = (select count(*) dcno  from K_RT_Dailyentryretail nolock
 )
 
 PRINT @maxid
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
 

 create table #MissingNumbers
(
    id int,
    BRANCH VARCHAR(MAX)
)

INSERT INTO #MissingNumbers(id) 
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

 DECLARE @count INT = (SELECT MAX(id) FROM #MissingNumbers)/50
 DECLARE @value INT = 1
 
while @value<=@count--whatever you max is
begin
	UPDATE #MissingNumbers SET BRANCH = (SELECT TOP 1 BRANCH FROM K_RT_Dailyentryretail WHERE dcno BETWEEN ((@value-1)*50) AND (@value*50)) WHERE id IN (SELECT id FROM #MissingNumbers where id BETWEEN ((@value-1)*50) AND (@value*50))
    set @value= @value + 1
  -- print @maxid
end

  SELECT * FROM #MissingNumbers
  drop table #IDSeq
  drop table #MissingNumbers
 
Share this answer
 
Can't you do

SQL
Select Sno, Branch
From table as T
where not exists(select 1 from table as T1 Where T1.Sno = T.Sno + 1)


?
 
Share this answer
 
try this:

SQL
DECLARE @LIMIT INT = 10// set your Count of Sno
;WITH CTE_Series AS
(
    SELECT 1 [Sequence] UNION ALL
    SELECT [Sequence] + 1 FROM CTE_Series WHERE [Sequence] < @LIMIT
)
SELECT [Sequence] AS SkippedCustCodes,'ABC' FROM CTE_Series
EXCEPT
SELECT id ,'ABC' FROM tbl_test



you have only one branchname in your table ,so you set Static name


Refer this Link

How Can I Select That?[^]
 
Share this answer
 
v2
my solution is similar to syed shanu's one

but i prefer using CTE table to generate the "list of items"
i think it is a lot clearer

SQL
--// Get the max id
DECLARE @MAX AS INT
SET @MAX = (select MAX(id) from K_RT_Dailyentryretail WITH (nolock))

--// Generate number serie from 1 to @MAX
;WITH Numbers AS (
     SELECT 1 as Num
     UNION ALL
     SELECT n.Num + 1
       FROM Numbers n
      WHERE n.Num + 1 <= @MAX
)
--// Selecting the missing
select n.Num  as Missing, 'ABC'; as Branch
from Numbers as n
left join K_RT_Dailyentryretail as der on n.Num = der.SNO
where der.SNO is null


Regards
 
Share this answer
 
v3
Comments
[no name] 24-Sep-14 3:26am    
you can't mention branch name directly.If i have more than one branch name,then what can you do?
nrgjack 24-Sep-14 4:38am    
yep you haven't mentioned.
what would you expect ?
SQL
/*table and data*/
CREATE TABLE #tblTest(
	Sno BIGINT,
	Branch VARCHAR(100)
)
INSERT INTO #tblTest
VALUES (1,'ABC'), 
	(2,'ABC'), 
	(3,'ABC'), 
	(5,'ABC'),
	(6,'ABC'),
	(8,'ABC'), 
	(10,'ABC') 

/*Soultion*/
WITH MaxMin
AS
(
	SELECT MAX(Sno) AS MaxVal, MIN(Sno) AS MinVal
		FROM #tblTest	
),
Ranges
AS
(
	SELECT MinVal AS Sno FROM MaxMin
		UNION ALL
	 SELECT Sno + 1 
		FROM Ranges 
		WHERE Sno < (SELECT MaxVal AS Sno FROM MaxMin)
 )
SELECT Sno AS MissingNo, 'ABC' AS Branch
	FROM Ranges
	WHERE Sno NOT IN (SELECT Sno FROM #tblTest)


If you would give proper ans the solution would be better :(
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900