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

Chapai wrote:
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

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