Locate next available number.

[Habalabam]

Hi, I want to find the SQL statement that gets the next unused number within that category.

Simplified Table:

Table name: MyUnit.
Attributes:
-UnitNo
-CategoryNo


Let's assume the statement
SELECT UnitNo FROM MyUnit WHERE (CategoryNo=@Category)
returns the set {1,2,3,4,5,7,8,10,11,12} if Category=3.

I want to find the SQL statement that have two parameters: "Category" and "MinimumNo"

Examples:

"Category" = 3
"MinimumNo" = 2 should return 6

"Category" = 3
"MinimumNo" = 5 should return 6

"Category" = 3
"MinimumNo" = 7 should return 9

"Category" = 3
"MinimumNo" = 12 should return 13

"Category" = 3
"MinimumNo" = 17 should return 17

I hope I don't have to create a separate table containing all valid Unit numbers...

 

[cheyney]

How about this?

(can probably be cleaned up for efficiency, but should work)

DECLARE @Category int
DECLARE @MinimumNo int

set @Category = 3
set @MinimumNo = 12


select IsNull(min(UnitNo) + 1, @MinimumNo)
from(
	select UnitNo , (select min(b.UnitNo)
			from MyUnit b
			where b.UnitNo > a.UnitNo
			and b.CategoryNo = a.CategoryNo
			) as NextHighest
	from MyUnit a
	where a.CategoryNo = @Category
	and a.UnitNo >= @MinimumNo - 1
) c
where UnitNo + 1 <> IsNull(NextHighest, 32000)

cheyney

 

[TysonLPrice]

This seems to work. Just another way to skin a cat:

DECLARE @UnitNo int
DECLARE @MinimumUnit int
DECLARE @NextUnitNo int

set @MinimumUnit = 12

Declare MyUnit Cursor
FOR SELECT UnitNo from MyUnit WHERE CategoryNo = 3 and unitno >= @minimumunit
Open MyUnit
Fetch Next From MyUnit Into @UnitNo
Set @nextunitno = @unitNo
While @@Fetch_Status = 0
begin
    if @unitno <> @NextUnitNO 
	begin
	    break
        end
    else
	set @NextUnitNO = @NextUnitNO + 1	         
 	
    FETCH   NEXT FROM MyUnit INTO  @UnitNo 
end 
print 'result = ' +    rtrim(@NextUnitNo)
Deallocate MyUnit