Calculate variance percentage - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Calculate variance percentage

Author

Topic

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2011-09-20 : 10:28:43

I want to write trigger that will update a table when a new record is inserted that calculates the variance percentage. I am having trouble with the calculation:

MyTable gets populated with
Target = 2.00
Total1 = 2.00
Total2 = 2.36
Total3 = 2.58
Average = Null
VariancePercentage = Null

I want to calculate the values and update my table with these values
Average = ((Total1 + Total2 + Total3)/3)
VariancePercentage = ((Average/Target)-1)*100

The Average table would be updated with 2.313
And the VariancePercentage = 15.65

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 10:49:37

why not make Average and VariancePercentage a calculated column so that it does calculation on the fly based on values of other fields?
then create table will be like


CREATE TABLE tableName
(

... ,
Target <datatype>,
Total1 <datatype>,
Total2 <datatype>,
Total3 <datatype>,
Average AS ((Total1 + Total2 + Total3)/3),
VariancePercentage AS ((Average/Target)-1)*100
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2011-09-20 : 11:06:49

Thanks for the response - I think I have got myself in a pickle here as I have actually answered my question in my question.

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2011-09-20 : 11:18:42

Oh yes and the reason I was using a trigger instead of a calculated field is the calculation is different depending on the type of record that is inserted.

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 11:20:53

how? i think even that can be accomodated inside calculated column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2011-09-20 : 11:44:09

Hi visakh,

This is how if I understand you correct. The calculation varies depending on a code selected by the user and there could be about 5 different calculation. My guess is this would be confusing in a calculated field and is easier to follow as a trigger.

Always open to suggestion if you have a better idea.

IF EXISTS (SELECT * FROM inserted WHERE FullCalc = 'S100')
BEGIN
UPDATE VibrationMonitor
SET VariancePercentage = ((Total1 + Total2 + Total3) / 3 / Target - 1) * 100
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 12:08:51

you can add the logic in a scalar UDF and then use it to link to calculated field.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2011-09-20 : 12:27:40

I will have to look into that. Scalar UDF are not something I am too familiar with so will have to check that out.

What is the main advantage over using that to a trigger.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 13:03:43

only difference is scalar udf it does computation inline and as computed column it wont be stored in db unless its PERSISTED

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources