NEED HELP WITH SQL 2000 edition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

NEED HELP WITH SQL 2000 edition

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-17 : 07:54:16

Sonia writes "I need codes! Desperately...

Here is the 'Account' table I made earlier:

create table Account(Account_Number varchar(6)NOT NULL PRIMARY KEY, Branch_Name varchar(12)NOT NULL REFERENCES Branch (Branch_Name), Balance money)

insert into Account values(10101, 'First Bank', 5500)

insert into Account values(20202, 'US Bank', 3550)

insert into Account values(30303, 'Commerce', 7550)

-------------------------------------------------------------------------

Now I need to output the Mean and Standard Deviation of the above Account. So I tried this code but I'm not sure if its right since when I Analyze it, some values come out as 'null'.

CREATE PROCEDURE project2question2 AS

DECLARE @Mean MONEY

DECLARE @Deviation MONEY

SET @Mean = (SELECT avg(cast(Balance as float)) as mean from Account)

SET @Deviation = (SELECT Balance, STDEV(Balance) st_deviation

FROM Account

GROUP BY Balance)

-------------------------------------------------------------------------

The second question was to create a Trigger that would output the name "Account" and the time of change/update to it. So I had this so far, but I KNOW its not right, so can anyone revise this for me? I basically need to create a trigger named ActionHistory that would have two columns: TableName and ActionTime. They should tie to my Account table so that when it was changed/updated then the name 'Account' would appear under TableName in my trigger, and the datetime of update would appear under ActionTime. But I just can't figure this one out.

Create table ActionHistory(TableName varchar(12) NOT NULL, ActionTime datetime NOT NULL)

CREATE TRIGGER trigger_ex2

ON ActionHistory

AFTER INSERT, UPDATE

AS

BEGIN

UPDATE ActionHistory

SET Account = UPPER(LName) WHERE TableName in (SELECT TableName FROM ActionHistory)

SET Loan = UPPER(LName) WHERE IDN in (SELECT TableName FROM ActionHistory)

-------------------------------------------------------

If someone could give me these codes I would be very grateful. I know you guys are the SQL wizzards. Thanks. (by the way, I use SQL 2000 edition)

- SOnia"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources