calculations and negatives - 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

General SQL Server Forums

New to SQL Server Programming

calculations and negatives

Author

Topic

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-03-15 : 14:56:45

I am using a pretty simple calculation based off about 4 other temp tables and just noticed something. Since I am using COALESCE(*,0) in the temp tables, it will default nulls to 0. This is the calculation so far in the main table.

COALESCE((nc.[IM NC]-#temp3.IM),nc.[IM NC])

Is there anyway to restrict the calculation so that if it returns a negative value it will return 0 instead.

Sachin.Nand

2937 Posts

Posted - 2010-03-15 : 15:08:52

Try
COALESCE(nullif(left(your expression,1),'-'),0)

PBUH

detlion1643
Yak Posting Veteran

67 Posts

Posted - 2010-03-15 : 15:20:58

Well, I tried:

COALESCE(NULLIF(LEFT((nc.[IM NC]-#temp3.IM),1)'-'),nc.[IM NC])

and while it seemed to work for what was normally returning negatives, my normal calculations would be chopped down to single digits (random, not all were)?

EDIT - just tried this

CASE WHEN (nc.[IM NC]-#temp3.IM) <0 THEN (nc.[IM NC]) ELSE (nc.[IM NC]-#temp3.IM) AS [IM TC]

This is giving me syntax errors near keyword 'AS'
EDIT AGAIN - Found the mistake, forgot an END at the end

shan
Yak Posting Veteran

84 Posts

Posted - 2010-03-15 : 16:54:51

select
CASE WHEN (nc.[IM NC]-#temp3.IM) <0 THEN (nc.[IM NC]) ELSE (nc.[IM NC]-#temp3.IM)
end
AS [IM TC]

You need to include End

-Shan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources