October 13, 2004 at 7:09 pm
I need to be able update all the rows in Table using information from another Table and a Formula.
s ONE Record)
GoodMails (Int)
BadMails (Int)
Table #2
Tokens (String)
GoodTokens (Int)
BadTokens (Int)
Score (Numeric(18,4)
I only want to read Table 1 ONCE
I then need to Update ALL the Records in Table 2 with a calculation based on the numeric data.
PSEUDO CODE:
Table2.Score = (Table2.BadTokens / Table1.BadMails) / ((Table2.BadTokens / Table1.BadMails) + (Table2.GoodTokens / Table1.GoodMails))
I need to do this with a Stored Procedure with no Parameters passed. Can anyone help?
Brian Gillham
FailSafe Systems
October 14, 2004 at 1:23 am
CREATE PROC usp_setScore AS
SET NOCOUNT ON -- Suppress record count messages for a performance boost.
-- Declare two variables.
DECLARE @lGoodMails Int , @lBadMails Int
-- Populate the variables with values from table1
SELECT @lGoodMails = GoodMails , @lBadMails = BadMails
FROM dbo.Table1
-- Perform the update
UPDATE dbo.Table2
SET Score = (Table2.BadTokens/@lBadMails) / ((Table2.BadTokens/@lBadMails)+(Table2.GoodTokens/@lGoodMails))
GO
October 14, 2004 at 4:14 pm
Is the Decimal Conversions correct here or is there a simpler way.
CREATE PROC spUpdateTokenScore AS
SET NOCOUNT ON -- Suppress record count messages for a performance boost.
-- Declare two variables.
DECLARE @HAMS Numeric(18,4), @SPAMS Numeric(18,4)
-- Populate the variables with values from the Params Table
SELECT @HAMS = dbo.Params.BayeHam, @SPAMS = dbo.Params.BayeSpam FROM dbo.Params WHERE Config = 0
-- Perform the update
IF @HAMS > 0 AND @SPAMS > 0
UPDATE dbo.Tokens
SET dbo.Tokens.Score = (CONVERT(Numeric(18,4),dbo.Tokens.Spams) / @SPAMS) / ((CONVERT(Numeric(18,4),dbo.Tokens.Spams) / @SPAMS)+(CONVERT(Numeric(18,4),dbo.Tokens.Hams) / @HAMS))
GO
Brian Gillham
FailSafe Systems
October 15, 2004 at 1:35 am
Unless you class the CAST(dbo.Tokens.Spams AS NUMERIC(18.4)) as simpler, no.
The only time I use CONVERT these days is for Date conversions
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply