create table Job (JobNumber int, Entered datetime)
insert into Job
select '4000', '01/01/2011' union
select '4001', '01/02/2011' union
select '4001', '01/03/2011' union
select '4002', '01/09/2011' union
select '9668', '04/20/2011' union
select '9669', '04/30/2011'

DECLARE @result int, @reset datetime, @default int
set @reset = '2010.01.01'
set @default = 4000	
	
declare @tvJob table (JobNumber int)
insert into @tvJob select distinct JobNumber from Job where JobNumber >= @default and Entered >= @reset

select top 1 JobNumber + 1 from
	(select JobNumber,
		(select top 1 JobNumber from @tvJob where /*Entered >= @reset and*/ JobNumber = j.JobNumber+1 order by JobNumber desc) NextJobNumber
	from @tvJob j /*where JobNumber >= @default and Entered >= @reset*/) t
	where t.NextJobNumber is null
	order by JobNumber asc

SELECT MAX(jobNumber)+1 
FROM Job
WHERE JobNumber >= @default 
	AND Entered >= @reset 
	--limit the value by evaluating a modulus
	AND JobNumber % @default < 100

declare @default int
set @default = 4003
while (@default < 5000)	
begin
	insert into Job select @default, getdate()
	set @default = @default + 1
end

quote:

---

Originally posted by ehorn

SELECT MAX(jobNumber)+1 
FROM Job
WHERE JobNumber >= @default 
	AND Entered >= @reset 
	--limit the value by evaluating a modulus
	AND JobNumber % @default < 100

---

quote:

---

Originally posted by sfortner

Well, that's definitely faster : ) but it's not returning the right results as is. If I add more values to be closer to what we actually have in production with this sql statement:

declare @default int
set @default = 4003
while (@default < 5000)	
begin
	insert into Job select @default, getdate()
	set @default = @default + 1
end

it incorrectly returns 4100. The mod check seems to be more of a "hard-coded" soln where we need to find the first non-sequential number.

quote:

---

Originally posted by ehorn

SELECT MAX(jobNumber)+1 
FROM Job
WHERE JobNumber >= @default 
	AND Entered >= @reset 
	--limit the value by evaluating a modulus
	AND JobNumber % @default < 100

---

--Steve

---

quote:

---

Originally posted by sfortner

Maybe it would help to have more info. This db is used by a job board web app and when they create a new job the GetNewJobNumber udf is called to get the next available job number. There's currently job numbers 4000 - 8677 that are in the range where we want to create the next job number. There's other job numbers in the 1 - 2344 range that are from the old system that we don't want to use, nor do we want to use any job numbers in the 2345 - 3999 range (at least not yet). There's also some other job numbers seeded from 9668 - 9999 that we don't want to use either - these are used for special contract jobs. It's something I inherited, so I can't change it.

That said, that's why we have a @default variable
that gets set to 4000 so it uses the 4000 - 9999 range. All the GetNewJobNumber udf should do is get the first available job number, where it hasn't been used yet. At some point job number 5000 in our example could get created, and to confirm, when a new job number is created, it just gets the next available number. The query should determine when the job numbers are not in sequence and return the next one.

--Steve

---

SELECT MIN(n)
FROM dbo.numbers n
LEFT JOIN Job j ON j.jobNumber = n.n
WHERE n.n >= @default AND n.n < 9668
AND j.jobNumber IS NULL

quote:

---

Originally posted by ehorn


I see, thanks for clarifying the need. Perhaps another method would be to create and use a table of numbers and then compare the numbers table to what exists in your jobNumbers table. Then Find the minimum available number for which there is no corresponding jobnumber. Something like this;

SELECT MIN(n)
FROM dbo.numbers n
LEFT JOIN Job j ON j.jobNumber = n.n
WHERE n.n >= @default AND n.n < 9668
AND j.jobNumber IS NULL

So here, dbo.numbers contains only one int field [n] which is a primary key and contains those numbers for which jobs can be assigned. The first null value it finds when joined to your Jobs table is then the value returned.

HTH.

---

quote:

---

Originally posted by sfortner

Brilliant - works perfectly - and it even does it in 0 ms vs 11333 ms for the existing soln! The old Celko numbers table to the rescue. I'm surprised that it runs in 0 ms too - wouldn't have thought that would have been possible, but these are fairly small tables and the est execution plan shows it's using the new non-clustered index I added and the pk index on the Numbers table. Great job - thanks again!

--Steve

---