Derive Values for Update

Question

Derive Values for Update

za34571

SSC Enthusiast

Points: 141
More actions
July 7, 2009 at 2:15 pm

#136077

I am new to sql server so I was hoping I could get some help? I have a table structured and populated as below.
The variance column is always derived by ValueA-ValueB, even if it is results in a negative value.
I need to populate the column that currently contains null values with the percentage representation of the variance.
Any idea how I would write the Update statement?
CREATETABLE #DeriveVariance
(
ValueA INT,
ValueB INT,
Variance INT,
VariancePercentage DECIMAL(9,2) NULL
)
GO
INSERT INTO #DeriveVariance
(
ValueA,
ValueB,
Variance,
VariancePercentage
)
SELECT 1000, 950, 50, NULL
UNION ALL
SELECT 900, 940, -40, NULL
GO
SELECT * FROM #DeriveVariance dv
Thanks in advance, Zak.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 1

First, if the variance column is always derived - then I would recommend creating a computed column instead of updating the value in that column. Example:

CREATE TABLE #DeriveVariance

(

ValueA INT,

ValueB INT,

ValueA-ValueB As Variance,

)

GO

Now, if you want the percentage representation - how are you currently calculating that value? You can create another computed column based upon that calculatation.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

za34571 SSC Enthusiast Points: 141 More actions · Answer 2

ah, thanks. The computed column approach sounds good. I will look it up in Books Online.

nathan.syverson Old Hand Points: 388 More actions · Answer 3

Unless you need to search based on the computed column. Then I would recommend a trigger or an indexed view, depending on how often that table is updated.