I will be posting a solution shortly but in the meantime take a look at Joe Celko's Article titled "Relational Division" at http://www.dbazine.com/ofinterest/oi-articles/celko1

This is a lot easier without the procedure code such as the while loop and no variables are needed.

create table stats_2

(Counterinteger not null primary key

, childcampvarchar(255) not null

, deptvarchar(255)

)

insert into...

Sorry, Jeff, but the use of STR does not work as the expected results are incorrect. With Banker's Rounding to 2 decimal points:

For 3.455, since the third decimal position...

"The argument from the original developers is that adding the primary key will negatively affect performance."

Acually, declaring primary keys, foreign key constraints and insuring that all columns are not null...

"I'll use the barCode, that HAS to be unique."

Actually, no it does not need to be unique. I ran into this problem a few years ago in the health...

Jeff and Ryan, try your solutions with these four test cases.

The differences between the pairs is in the second decimal position number being odd or even.

The difference between...

So the banker's rounding algorithm must be applied from right to left? Try this:

create FUNCTION RoundBanker

( @Amtnumeric(38,16)

, @RoundToDecimal tinyint

)

RETURNS numeric(38,16)

AS

BEGIN

declare@RoundedAmtnumeric(38,16)

,@WholeAmtinteger

,@Decimaltinyint

,@Tennumeric(38,16)

set@Ten= 10.0

set@WholeAmt= ROUND(@Amt,0, 1 )

set@RoundedAmt= @Amt - @WholeAmt

set@Decimal= 16

While...

This SQL uses the banker's rounding algorithm:

DECLARE@Decimalssmallint

set@Decimals= 2

select BRAmt

,BRExpectedAmt

, CASE

WHEN 5 = ( ROUND(BRAmt * POWER( 10, @Decimals + 1 ) ,0,1) - (ROUND(BRAmt * POWER( 10,...

DTS Global Variables as parameters do not work very well with SQL statements due too many limitations such as you have indicated. But Global Variables work fine with stored...

Somehow, the SQL Server configuration has been modified to allow system administrators to drop or change system components.

To view the configuration, run stored procedure sp_configure and look at the value...

This is a variation on the problem of finding contigous theater seats. Search for CELKO and "theater seats" for some other solutions.

The solution is rather long. First you...

This can only be done by using xp_cmdshell. For privileges, this stored procedure can only be executed by someone with system administration rights.

P.S.

I recently read Joe Celko's SQL book and...

When you DENY access to BUILTIN/Administrator, this overrides any grants to any member of the local administrators group and therefore you cannot login.

You are not the first person with this situation. ...

"I assume that the results of the update, delete, insert statement would cause corruption or partially changed data."  It could if the cause of the crash were a disk drive...

Triggers are not allowed on SQL Server 2000 system tables and any attempt to create a trigger will cause the the following error message:

Server: Msg 229, Level 14, State 5, Procedure sysobjects_tia, Line...