Need a Query for finding the Next available number from a set.

[KWade]

Hi all.
I have a question that I'd like to get some help on.

I have a table with a column (ProjectNumber) that contains something similar to the following:

04910010
04910020
04910030
04910050
04910100
04910110
04910120
04910500
etc...

I would like to find the next available number which is defined as:
1. Order the numbers in ascending sequence
2. start from the lowest number
3. search until you find 2 numbers that are greater than 10 apart from each other
3. return the lower number with 10 added to it.
(I want to keep 10 free numbers in between each project number)

ie. in the list above the next available number should be 04910040.

I can do this using ASP code but I'm wondering how to do this using an SQL query.
I just don't know SQL good enough to write a query for this.

Thanks,
Keith

 

[nigelrivett]

Have a look at

http://www.nigelrivett.net/FindGapsInSequence.html

something like
select top 1 num + 10
from tbl t1
where 10 < num - (select min(t2,num) from tbl t2 where t2.num > t1.num)
order by num



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[KWade]

Thanks nigelrivett,

Sorry I haven't responded but I just haven't had time to look at this until now. I was able to use some of your code from your "Find Gaps in Sequence" page.
Here is what I came up with. There may be a more elegant solution but this seems to be working.
Thanks,
KWade


-- Tables PR and ProjectDataSheet contain Project numbers (char values) in format: 04911000
-- 04 is year, 91 is office and 1000 is the number.
-- The number part range is 1000 to 9990

drop table #ProjNums
drop table #allnums

-- get current project numbers and convert to number
Select distinct Cast(SubString(WBS1,5,4) as int) as i into #ProjNums from GarverDB..PR
where (WBS1 like '0491%')
Union
Select distinct Cast(SubString(WBS1,5,4) as int) as i from ProjectDataSheets..ProjectDataSheet
where (WBS1 like '0491%')
order by i


-- create my table of numbers to compare against.
-- Numbers are incremented by 10
create table #allnums(i int)
declare @i int
select @i = 1000
while @i < 10000
Begin
set @i = @i + 10
insert #allnums select @i
end
-- Get the first available number and convert back to a char value for use elsewhere
select '0491' + (select top 1 CAST(ints.i as varchar(4)) from #allnums as ints
left outer join #ProjNums on ints.i = #ProjNums.i
where #ProjNums.i is null) as NextAvailabletNumber