Missing Sequence - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Missing Sequence

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2009-04-15 : 20:17:27

Guys,

I have a table in which the sequence is populated from the application and not from the identity property of the SQL Server. For example

Empid LastName
1 smith
2 doe
4 bell

For a given input of empid I want to find out what numbers are missing for empid in the above example if I provide the range of empid between 1 and 4, the missing empid should be 3. Is there any effective way to build a function to find out missing number in sequence column

Any suggestion and inputs would help

Thanks

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2009-04-15 : 20:31:00

a quick search gives you this solution

Create table #SequenceTable
( SeqNumber Int
)

Declare @Seq int

Set @Seq = 10

while @Seq <= 100
Begin
Insert into #SequenceTable values(@Seq)
Set @Seq = @Seq + 1
End

Insert into #SequenceTable values(101)
Insert into #SequenceTable values(102)
Insert into #SequenceTable values(103)
Insert into #SequenceTable values(105)
Insert into #SequenceTable values(106)
Insert into #SequenceTable values(107)
Insert into #SequenceTable values(114)
Insert into #SequenceTable values(115)
Insert into #SequenceTable values(116)
Insert into #SequenceTable values(129)
Insert into #SequenceTable values(130)
Insert into #SequenceTable values(131)

declare @min int
declare @max int
select @min = min(SeqNumber), @max = max(SeqNumber) from [#SequenceTable]
create table #tmp (SeqNumber int)
while @min <= @max
begin
if not exists (select * from [#SequenceTable] where SeqNumber = @min)
insert into #tmp (SeqNumber) values (@min)
set @min = @min + 1
end
select * from #tmp
drop table #tmp

HTH

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-15 : 22:49:02

try this one too


Create table #SequenceTable
( SeqNumber Int
)

Insert into #SequenceTable values(101)
Insert into #SequenceTable values(102)
Insert into #SequenceTable values(103)
Insert into #SequenceTable values(105)
Insert into #SequenceTable values(106)
Insert into #SequenceTable values(107)
Insert into #SequenceTable values(114)
Insert into #SequenceTable values(115)
Insert into #SequenceTable values(116)
Insert into #SequenceTable values(129)
Insert into #SequenceTable values(130)
Insert into #SequenceTable values(131)

declare @min int
declare @max int
select @min = min(SeqNumber), @max = max(SeqNumber) from [#SequenceTable]
select number from 
 master..spt_values as m where m.type ='p' 
and  number not in  (select seqnumber from #SequenceTable) and number between @min and @max

select number from 
master..spt_values as m where m.type ='p' 
and  not exists (select * from #SequenceTable where number = seqnumber) and number between @min and @max

select number from 
master..spt_values as m 
left join #SequenceTable s on m.number = s.seqnumber
where s.seqnumber is null
and m.type ='p' 
and number between @min and @max

drop table #SequenceTable

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-04-16 : 09:58:23

something like this:

select
from
( select row_number() over() as rn from table , table) x
left outer join table
on (table.empId=x.rn) where x.rb is null
where rn between @start and @finish

can't work it out exactly - don't have SQL Server here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 11:16:35

[code]
DECLARE @startid int,@endid int
SELECT @startid=1,@endid=4 (passed values)
;With Range (IDVal)
AS
(SELECT @startid
UNION ALL
SELECT IDVal+1 FROM Range WHERE IDVal+1<=@endid
)

SELECT r.IDVal
FROM Range r
LEFT JOIN YourTable t
ON t.Empid=r.IDVal
WHERE t.Empid IS NULL

OPTION (MAXRECURSION 0)
[/code]

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-17 : 02:49:33

Hi,
Check out this simple one

declare @table2 table (id int,outDate datetime,outAmount float )
insert into @table2 values (1,'01/01/2009',1)
insert into @table2 values (2,'01/01/2009',2)
insert into @table2 values (3,'01/01/2009',5)
insert into @table2 values (5,'01/2/2009',6)
insert into @table2 values (6,'01/3/2009',8.5)

select rnum from
(
select row_number() over (order by id) rnum from @table2
) a
where rnum not in (select id from @table2)

Kunal

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-17 : 03:32:14

declare @StartPoint int, @EndPoint Int
set @StartPoint=1
set @EndPoint=10

declare @table2 table (id int,outDate datetime,outAmount float )
insert into @table2 values (1,'01/01/2009',1)
insert into @table2 values (2,'01/01/2009',2)
insert into @table2 values (3,'01/01/2009',5)
insert into @table2 values (5,'01/2/2009',6)
insert into @table2 values (6,'01/3/2009',8.5)
insert into @table2 values (10,'01/3/2009',8.5)

; with CTERange(Num)
as
(
select @StartPoint
union all
Select Num+1 from CTERange where num <@EndPoint
)
select Num from CTERange where num not in (select id from @table2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources