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.
ChapaiPlease 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));
>> for every week we have a day_nbr from 1 to 7 or nothing (if no
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|||Chapai (racecar@.mail.ru) writes:
> 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
I don't see the point with unique_id. Judging from your description
(week, mynumber) is unique. Then they should be the primary key.
> 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?
Search Google or the subject "Thinking about code or SP", a recent
thread in microsoft.public.sqlserver.programming 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@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi!
Thanks, I already find the same solution with while
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|||Hi!
Erland Sommarskog wrote:
> I don't see the point with unique_id. Judging from your description
> (week, mynumber) is unique. Then they should be the primary key.
Just personal rule - always get a unique,independent id. I use sql in
web development - so to create a lists, etc.
> Search Google or the subject "Thinking about code or SP", a recent
> thread in microsoft.public.sqlserver.programming for a whole range of
> suggestions to a similar problem.
that was my second step. But it take a lot of wasted time.
> 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)
Cool. That is elegant. thanks.
Select x union all - that's nice.|||>> 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.|||Hi!
--CELKO-- wrote:
> Wrong. You have a proprietary, procedural answer that is
> computationally equal to what I gave you.
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.
> until you can see this, you will always be a 3GL programmer writing
in
> 3GL programs in some proprietary, non-portable SQL dialect.
Ok. This is a real world. If you use ASP/VBscript/C#/.NET - Oracle
hosting are too expensive, Mysql useless.
> 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.
Nope. The whole point of non-procedural languages, and all other
programming languages - is to help you to make a money quickly. ;-)|||>> 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.
>> what I gonna do with your algorithm if I need more than 7 numbers?
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);
>> The whole point of non-procedural languages, and all other
programming languages - is to help you to make money quickly. ;-) <<
And the only way you can do this is with bad programming??|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1105201718.985293.286980@.c13g2000cwb.googlegr oups.com...
>> >> The whole point of non-procedural languages, and all other
> programming languages - is to help you to make money quickly. ;-) <<
> And the only way you can do this is with bad programming??
Of course... that way the customer keeps coming back to you... until they
figure out they're paying too much. :-)|||Chapai (racecar@.mail.ru) writes:
> Just personal rule - always get a unique,independent id. I use sql in
> web development - so to create a lists, etc.
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@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi!
--CELKO-- wrote:
> No, your procedural coding is weak, too. Here is your algorithm in
> SQL/PSM, which you can translate into dialect.
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.
> The use of extra variables and the hidden GOTO's in BREAK and
CONTINUE
> would cost you points in any freshman programming class.
Show me other faster and better way to use "while" in ms sql server
2000.
I'm wait.
> Use a Sequence table instead of a constructed table expression. Here
> is a general version with pure Standard SQL
aha. Hundreds sets like set @.i1 = 1, @.i2=2, tables, temporary tables,
cross calls, .. Sure. Sommarskog's sample was finer and more
interesting.
> And the only way you can do this is with bad programming??
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?|||Hi!
Erland Sommarskog wrote:
> 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.
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.|||Chapai (racecar@.mail.ru) writes:
>> 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.
> 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.
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@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi!
Erland Sommarskog wrote:
> If you work under these cirumstances, it's even more important to
> have your constraints right. Stressed development, unevitably leads
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_name,trainer_id), table
exrcise(exercise_id,exercise_name),
table exercise_workout_link(workout_id,exercise_id)|||Chapai (racecar@.mail.ru) writes:
> Ok. How you can link other tables without unique constraint?
That's kind of difficult. Then again, I suggested that it was a
UNIQUE constraint that you should add to your table.
> 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_name,trainer_id), table
> exrcise(exercise_id,exercise_name),
> table exercise_workout_link(workout_id,exercise_id)
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@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
No comments:
Post a Comment