July 7, 2009 at 2:15 pm
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.
July 7, 2009 at 2:22 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 7, 2009 at 2:33 pm
ah, thanks. The computed column approach sounds good. I will look it up in Books Online.
July 8, 2009 at 3:18 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply