how to fine a hole in a records?

[Chapai]

Hi all!

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

tool: MS SQL server 2000, T-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_ T-SQL find a missed numbers for particular week when
I'm insert a records?


Thanks.
Chapai

[--CELKO--]

Re: how to fine a hole in a records?

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Rows are not records and you have no relational key in your
pseudo-code. Ignoring that the design is fundamentally bad because you
should be using temporal datatypes for temporal data, your table should
have looked like this:

CREATE TABLE Foobar
(week_nbr INTEGER NOT NULL
CHECK(week_nbr > 0),
day_nbr INTEGER NOT NULL
CHECK(day_nbr BETWEEN 1 AND 7),
PRIMARY KEY(week_nbr, day_nbr));
[color=blue][color=green]
>> for every week we have a day_nbr from 1 to 7 or nothing (if no[/color][/color]
record [sic] for that day), we can insert or delete day_nbr in any
order, at will . . How to use _only_ T-SQL to find a missed number for
particular week when I am inserting records [sic]? <<

This is a little ugly looking, but it is fast.

CREATE PROCEDURE InsertNewFoobar (@new_week_nbr INTEGER)
BEGIN
DECLARE @new_day_nbr INTEGER;
SET @new_day_nbr
= CASE WHEN 1 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 1
WHEN 2 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 2
WHEN 3 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 3
WHEN 4 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 4
WHEN 5 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 5
WHEN 6 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 6
WHEN 7 NOT IN
(SELECT day_nbr FROM Foobar WHERE week_nbr = @new_week_nbr)
THEN 7
ELSE NULL END;

INSERT INTO Foobar (week_nbr, day_nbr)
VALUES (@new_week_nbr, @new_day_nbr);
-- if you have 7 days already, then you get a primary key violation
-- you gave no specs on how to handle it

END:
In Standard SQL, the CASE expression could be in the VALUES () list

[Erland Sommarskog]

Re: how to fine a hole in a records?

Chapai (racecar@mail.r u) writes:[color=blue]
> tool: MS SQL server 2000, T-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[/color]

I don't see the point with unique_id. Judging from your description
(week, mynumber) is unique. Then they should be the primary key.
[color=blue]
> 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_ T-SQL find a missed numbers for particular week when
> I'm insert a records?[/color]

Search Google or the subject "Thinking about code or SP", a recent
thread in microsoft.publi c.sqlserver.pro gramming for a whole range of
suggestions to a similar problem.

Since this problem is constrained to 1-7, here is a more simple-minded
solution:

SELECT MIN (n)
FROM (SELECT n = 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7) n
WHERE NOT EXISTS (SELECT *
FROM weeks w
WHERE w.weekno = @weekno
AND n.n = w.mynumber)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Chapai]

Re: how to fine a hole in a records?

Hi!

Thanks, I already find the same solution with while

create proc stupidproc ( @week as int )
as

declare @mynumber as int, @availablenumbe r as int
set @mynumber = 1

WHILE @mynumber < 8
begin
IF NOT EXISTS ( select mynumber from MYTABLE

where mynumber = @mynumber and [week]=...@week)

begin
set @availablenumbe r = @mynumber
break
end
else
set @mynumber = @mynumber + 1

CONTINUE
end

select @availablenumbe r

[Chapai]

Re: how to fine a hole in a records?

Hi!

Erland Sommarskog wrote:
[color=blue]
> I don't see the point with unique_id. Judging from your description
> (week, mynumber) is unique. Then they should be the primary key.[/color]

Just personal rule - always get a unique,independ ent id. I use sql in
web development - so to create a lists, etc.
[color=blue]
> Search Google or the subject "Thinking about code or SP", a recent
> thread in microsoft.publi c.sqlserver.pro gramming for a whole range of
> suggestions to a similar problem.[/color]
that was my second step. But it take a lot of wasted time.
[color=blue]
> Since this problem is constrained to 1-7, here is a more[/color]
simple-minded[color=blue]
> solution:
>
> SELECT MIN (n)
> FROM (SELECT n = 1
> UNION ALL
> SELECT 2
> UNION ALL
> SELECT 3
> UNION ALL
> SELECT 4
> UNION ALL
> SELECT 5
> UNION ALL
> SELECT 6
> UNION ALL
> SELECT 7) n
> WHERE NOT EXISTS (SELECT *
> FROM weeks w
> WHERE w.weekno = @weekno
> AND n.n = w.mynumber)[/color]
Cool. That is elegant. thanks.
Select x union all - that's nice.

[--CELKO--]

Re: how to fine a hole in a records?

>> already find the same solution with while .. <<

Wrong. You have a proprietary, procedural answer that is
computationally equal to what I gave you. This is a BIG difference and
until you can see this, you will always be a 3GL programmer writing in
3GL programs in some proprietary, non-portable SQL dialect.

The whole point of non-procedural languages is that you tell it WHAT
you want and it figures oiut HOW to do it. Looping is a HOW and not a
WHAT.

[Chapai]

Re: how to fine a hole in a records?

Hi!

--CELKO-- wrote:[color=blue]
> Wrong. You have a proprietary, procedural answer that is
> computationally equal to what I gave you.[/color]

yeah, right. I'm agree with you and I'm appreciate for your ideas and
help. But my version is more flexible and compact. what I gonna do with
your algorithm if I need more than 7 numbers? what about 50? 100?
Using a "while" I need to change only one variable and code are still
readable.
Easy to change, easy to support.
Portability is not important at all, especially for me, I'm a web
developer, so MS SQL cover all my (and my customers) needs. MS Access
cover the rest.
[color=blue]
> until you can see this, you will always be a 3GL programmer writing[/color]
in[color=blue]
> 3GL programs in some proprietary, non-portable SQL dialect.[/color]
Ok. This is a real world. If you use ASP/VBscript/C#/.NET - Oracle
hosting are too expensive, Mysql useless.
[color=blue]
> The whole point of non-procedural languages is that you tell it WHAT
> you want and it figures oiut HOW to do it. Looping is a HOW and not[/color]
a[color=blue]
> WHAT.[/color]
Nope. The whole point of non-procedural languages, and all other
programming languages - is to help you to make a money quickly. ;-)

[--CELKO--]

Re: how to fine a hole in a records?

>> But my version is more flexible and compact. <<

No, your procedural coding is weak, too. Here is your algorithm in
SQL/PSM, which you can translate into dialect.

CREATE PROCEDURE StupidProc (IN my_week INTEGER)
LANGUAGE SQL
BEGIN
DECLARE answer_nbr INTEGER;
SET answer_nbr = 1;
WHILE answer_nbr < 8
DO IF NOT EXISTS
(SELECT *
FROM Foobar
WHERE day_nbr = answer_nbr
AND week_nbr = my_week)
THEN RETURN answer_nbr;
ELSE SET answer_nbr = answer_nbr + 1;
END IF;
END WHILE;
RETURN answer_nbr; -- 8 is an error
END;

The use of extra variables and the hidden GOTO's in BREAK and CONTINUE
would cost you points in any freshman programming class.
[color=blue][color=green]
>> what I gonna do with your algorithm if I need more than 7 numbers?[/color][/color]
what about 50? 100? <<

Use a Sequence table instead of a constructed table expression. Here
is a general version with pure Standard SQL

SELECT MIN (n)
FROM (SELECT seq FROM Sequence WHERE seq <= :n)
EXCEPT
(SELECT day_nbr
FROM Weeks AS
WHERE W.week_nbr = my_weeknbr) AS N(n);
[color=blue][color=green]
>> The whole point of non-procedural languages, and all other[/color][/color]
programming languages - is to help you to make money quickly. ;-) <<
And the only way you can do this is with bad programming??

[Greg D. Moore \(Strider\)]

Re: how to fine a hole in a records?


"--CELKO--" <jcelko212@eart hlink.net> wrote in message
news:1105201718 .985293.286980@ c13g2000cwb.goo glegroups.com.. .[color=blue][color=green][color=darkred]
>> >> The whole point of non-procedural languages, and all other[/color][/color]
> programming languages - is to help you to make money quickly. ;-) <<
> And the only way you can do this is with bad programming??
>[/color]

Of course... that way the customer keeps coming back to you... until they
figure out they're paying too much. :-)

[Erland Sommarskog]

Re: how to fine a hole in a records?

Chapai (racecar@mail.r u) writes:[color=blue]
> Just personal rule - always get a unique,independ ent id. I use sql in
> web development - so to create a lists, etc.[/color]

Not sure that I see the point, but as long as you have a UNIQUE constraint
on the real primary key, that's alright. Without the UNIQUE constraint,
you're putting the integrity of your database at stake.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Chapai]

Re: how to fine a hole in a records?

Hi!

--CELKO-- wrote:[color=blue]
> No, your procedural coding is weak, too. Here is your algorithm in
> SQL/PSM, which you can translate into dialect.[/color]

I have a better idea for you - just put your code into query analyzer
and try to execute it. Oops! It does not work? Why?
Open your eyes and read the group name. DB2? Super-duper-sql-theory?
Microsoft does not support this standard in sql server 2000.
Period.
[color=blue]
> The use of extra variables and the hidden GOTO's in BREAK and[/color]
CONTINUE[color=blue]
> would cost you points in any freshman programming class.[/color]
Show me other faster and better way to use "while" in ms sql server
2000.
I'm wait.
[color=blue]
> Use a Sequence table instead of a constructed table expression. Here
> is a general version with pure Standard SQL[/color]
aha. Hundreds sets like set @i1 = 1, @i2=2, tables, temporary tables,
cross calls, .. Sure. Sommarskog's sample was finer and more
interesting.
[color=blue]
> And the only way you can do this is with bad programming??[/color]
It works? Works, fast? Fast. Simple? Simple. What else? Portability.
Strict adherence to standards. Ok. I'm not a student with ideas and not
an old professor with grey bolls - I'm MS web developer with hourly
rate.
Holy wars linux vs windows, c vs pascal. :-) You work on salary - right?

[Chapai]

Re: how to fine a hole in a records?

Hi!

Erland Sommarskog wrote:
[color=blue]
> Not sure that I see the point, but as long as you have a UNIQUE[/color]
constraint[color=blue]
> on the real primary key, that's alright. Without the UNIQUE[/color]
constraint,[color=blue]
> you're putting the integrity of your database at stake.[/color]

I'm a web developer. :-) Tomorrow customer can says to change
everything, or major part of logic. And I will get just couple hours to
realize that. other paradigm.

[Erland Sommarskog]

Re: how to fine a hole in a records?

Chapai (racecar@mail.r u) writes:[color=blue][color=green]
>> Not sure that I see the point, but as long as you have a UNIQUE
>> constraint on the real primary key, that's alright. Without the UNIQUE
>> constraint, you're putting the integrity of your database at stake.[/color]
>
> I'm a web developer. :-) Tomorrow customer can says to change
> everything, or major part of logic. And I will get just couple hours to
> realize that. other paradigm.[/color]

If you work under these cirumstances, it's even more important to
have your constraints right. Stressed development, unevitably leads
to bugs creeping in. Constraint is a means of preventing at least some
of these bugs causing bad data to be persisted.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Chapai]

Re: how to fine a hole in a records?

Hi!

Erland Sommarskog wrote:
[color=blue]
> If you work under these cirumstances, it's even more important to
> have your constraints right. Stressed development, unevitably leads[/color]

Ok. How you can link other tables without unique constraint?
Most fields are not unique, so, easy to get one guaranteed simple
unique field to reference. Or you know the other method?

table from discussed example (up to 7 workouts_num per week, for every
trainer/customer):

workout(workout _id, workout_num , workout_week, program_id, client_id)
and need to organise relations to table
program(program _id,program_nam e,trainer_id), table
exrcise(exercis e_id,exercise_n ame),
table exercise_workou t_link(workout_ id,exercise_id)

[Erland Sommarskog]

Re: how to fine a hole in a records?

Chapai (racecar@mail.r u) writes:[color=blue]
> Ok. How you can link other tables without unique constraint?[/color]

That's kind of difficult. Then again, I suggested that it was a
UNIQUE constraint that you should add to your table.
[color=blue]
> Most fields are not unique, so, easy to get one guaranteed simple
> unique field to reference. Or you know the other method?
>
> table from discussed example (up to 7 workouts_num per week, for every
> trainer/customer):
>
> workout(workout _id, workout_num , workout_week, program_id, client_id)
> and need to organise relations to table
> program(program _id,program_nam e,trainer_id), table
> exrcise(exercis e_id,exercise_n ame),
> table exercise_workou t_link(workout_ id,exercise_id)[/color]

It looks as if foreign-key constraint from workout to progam would be
possible. But not knowing the business rules, that is of course impossible
to tell.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp