Hoe to get the missedout values from the DataBase

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 2000 Forums

SQL Server Development (2000)

Hoe to get the missedout values from the DataBase

Author

Topic

ramana123
Yak Posting Veteran

57 Posts

Posted - 2006-02-13 : 01:00:44

hi all,
i came across one problem,
in my database i have a filed called p_number in this i have stored up to 9999.some times i will be deleting some of the numbers based ion some conditions.if i reached 9999 limit then i need to generate next minimum number within the range(1-9999) which are deleted.
for this i need to get the delted numbers or missing numbers within the range.could you give me the solution for this problem

nay kind of suggestions that could be greate.

thankx
Rams

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-13 : 01:11:35

Make use of the number table function from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

Select  * 
from    yourtable t
where   not exists (select * from dbo.F_TABLE_NUMBER_RANGE(1, 9999) x where x.number = t.p_number)

or use LEFT JOIN F_TABLE_NUMBER_RANGE to yourtable

----------------------------------
'KH'
everything that has a beginning has an end

ramana123
Yak Posting Veteran

57 Posts

Posted - 2006-02-13 : 01:15:51

hi what is this F_TABLE_NUMBER_RANGE table called

ramana123
Yak Posting Veteran

57 Posts

Posted - 2006-02-13 : 01:22:02

hi all,

is there any chance to get the missout values by simple query from the Databsae.

thanx
rams..!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-13 : 01:52:35

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro04/html/sp04d8.asp

Madhivanan

Failing to plan is Planning to fail

ramana123
Yak Posting Veteran

57 Posts

Posted - 2006-02-13 : 05:02:03

hi all,

is there any chance to get the missout values by simple query from the Databsae.

thanx
rams..!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-13 : 05:09:01

The suggested links are simple

Madhivanan

Failing to plan is Planning to fail

CLages
Posting Yak Master

116 Posts

Posted - 2006-02-13 : 09:36:34

Try this, works for me
returns all VEN_CODIGO not used in Table VENDEDOR

Clages

declare @tmpcli table (i int)

declare @i int
select @i = 1
while @i < (select max(ven_codigo) from vendedor )
begin
set @i = @i + 1
if not exists (select ven_codigo from vendedor where ven_codigo = @i )
begin
insert @TmpCli select @i
end
else
CONTINUE
end
(select * from @TmpCli)

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources