Suppress Values in derived calculation

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

General SQL Server Forums

New to SQL Server Programming

Suppress Values in derived calculation

Author

Topic

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-01 : 18:47:26

Hi;

This query is similar to one just posted but maybe there is a simpler solution.

I have a query that calculates variance

Select (T.OldCost-S.NewCost)/T.OldCost as Variance
from OldTable as T , NewTable as S.

in some cases the value is 0 because both costs are zero.
How do I suppress these since it is a derived calculation?

I tried WHERE VARIANCE > 0 but it doesnt work.

thx for any help I am sure this is simple but I am not able to see it.

r&r

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-01 : 19:10:11

select variance from
(Select (T.OldCost-S.NewCost)/T.OldCost as Variance
from OldTable as T , NewTable as S) dt
where variance > 0

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 23:39:52

quote:
Originally posted by revdnrdy

Hi;

This query is similar to one just posted but maybe there is a simpler solution.

I have a query that calculates variance

Select (T.OldCost-S.NewCost)/T.OldCost as Variance
from OldTable as T , NewTable as S.

in some cases the value is 0 because both costs are zero.
How do I suppress these since it is a derived calculation?

I tried WHERE VARIANCE > 0 but it doesnt work.

thx for any help I am sure this is simple but I am not able to see it.

r&r

not sure how you got variance as 0 when both costs were 0. I would have expected it to return divide by zero eror as per your above code.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-02 : 10:37:56

Thx tara I will try that & just need to find a way to integrate it into my query statement. The one I posted was truncated a bit.

Visakh what happens is the result gets rounded to zero due to precision and using the round function. So a result of 0.0004 will get rounded to 0 in the result set.

Otherwise yes you are correct and a divide by zero error occurs if oldcost is zero. I didn't paste that part of the code since I simply wanted to be able to put a where clause on a derived column called VARIANCE.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 10:42:41

Maybe you are victim of INTEGER division?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 10:45:42

[code]SELECT 1.0E * (t.OldCost - s.NewCost) / NULLIF(t.OldCost, 0) AS Variance
FROM OldTable AS t
INNER JOIN NewTable AS s ON s.Col1 = t.Col1[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts