By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,948 Members | 1,535 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,948 IT Pros & Developers. It's quick & easy.

How to find a hole in records? (next available number)

P: n/a
Hi all!

I need your help to realize algorithm for stored proc or trigger.

tool: SQL

TABLE:

[unique_id] [mynumber] [week]

[unique_id] - bigint,primary key, identity auto-increnment

[week] - int, 1-53, week number
[mynumber] - int, 1 - 7, for every week, daily record one per day, up
to 7 per week

so, for every week we have a mynumber from 1 to 7
or nothing (if no records for that day),

we can insert or delete mynubers in any order, at will

EXAMPLE:

week 1, mynumber 1,2,3 - so if we insert a new record, mynumber value
= 4
week 2, mynumber 1,2,3,5,7 - so next mynumber = 4

QUESTION:

How to use _only_ SQL find a missed numbers for particular week when
I'm insert a records?

Thanks.
Chapai

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Chapai wrote:
Hi all!

I need your help to realize algorithm for stored proc or trigger.

tool: SQL

TABLE:

[unique_id] [mynumber] [week]

[unique_id] - bigint,primary key, identity auto-increnment

[week] - int, 1-53, week number
[mynumber] - int, 1 - 7, for every week, daily record one per day, up
to 7 per week

so, for every week we have a mynumber from 1 to 7
or nothing (if no records for that day),

we can insert or delete mynubers in any order, at will

EXAMPLE:

week 1, mynumber 1,2,3 - so if we insert a new record, mynumber value
= 4
week 2, mynumber 1,2,3,5,7 - so next mynumber = 4

QUESTION:

How to use _only_ SQL find a missed numbers for particular week when
I'm insert a records?


You might get a more elegent solution in comp.databases.ms-sqlserver.

You could create a temp table:
create table #tmp (week int, mynumber int)

then loop through and insert all weeks and days, e.g.

--- code begins ---
declare @week int, @mynumber int
set @week=0
while @week < 53
begin
set @mynumber=0
set @week = @week + 1
while @mynumber < 7
begin
set @mynumber = @mynumber + 1
if not exists(select * from mytable where week=@week and
mynumber=@mynumber)
begin
insert into #tmp (week, mynumber) values (@week, @mynumber)
end
end
end
select week, mynumber from #tmp
--- code ends ---
--
This sig left intentionally blank
Nov 13 '05 #2

P: n/a
Hi!

Hmm. Good idea, I fogot about while in sql. thanks
create proc stupidproc ( @week as int )
as

declare @mynumber as int, @availablenumber as int
set @mynumber = 1

WHILE @mynumber < 8
begin
IF NOT EXISTS ( select mynumber from MYTABLE
where mynumber = @mynumber and [week]=@week)
begin
set @availablenumber = @mynumber
break
end
else
set @mynumber = @mynumber + 1
CONTINUE
end

select @availablenumber

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.