How do I filter my sql server 2005 query to values greater than 0?

Hi,

I have the attached cast statement to select values for a column in my sql server 2005 query. How can I alter my cast so it only returns values greater than zero?

Thanks.
cast	(
					case when isnull
						(
						cd.data1_surcharge_mtn,0) > 0.0 
						and cd.data1_surcharge_mtn_seperate_line = 0
						then cido.amount_net - round
						(
							(
							round(cido.amount_net,2) * isnull(cd.data1_surcharge_mtn,0.0)/100.0 
							),2
						)
						else cido.amount_net
						end
						as decimal(13,2)
					) * -1.0 as TOTAL PRICE

Open in new window

aspnet-scotlandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Gene_CypCommented:
If you mean so that the end result is positive instead of negative just remove the:
 
"* -1.0 " (but leave the "as TOTAL PRICE" intact.
 
 
0
 
aspnet-scotlandAuthor Commented:
Currently the end result is returning both negative and positive numbers along with zero valued numbers. I only want it to return numbers greater than zero if possible?
0
 
conrabaCommented:
If you can put your SQL in a CTE,  then yu can just query on the CTE table where TOTALPRICE > 0.

Infor about CTE:
http://msdn.microsoft.com/en-us/library/ms190766.aspx


0
 
cyberkiwiCommented:
This will return NULL if the TotalPrice would have shown 0 or less
cast	(
case when
					case when isnull
						(cd.data1_surcharge_mtn,0) > 0.0 
						and cd.data1_surcharge_mtn_seperate_line = 0
						then cido.amount_net - round
						(
							(
							round(cido.amount_net,2) * isnull(cd.data1_surcharge_mtn,0.0)/100.0 
							),2
						)
						else cido.amount_net
						end
> 0 then
					case when isnull
						(cd.data1_surcharge_mtn,0) > 0.0 
						and cd.data1_surcharge_mtn_seperate_line = 0
						then cido.amount_net - round
						(
							(
							round(cido.amount_net,2) * isnull(cd.data1_surcharge_mtn,0.0)/100.0 
							),2
						)
						else cido.amount_net
						end
else NULL
END
						as decimal(13,2)
					) * -1.0 as TOTAL PRICE

Open in new window

0
 
aspnet-scotlandAuthor Commented:
I was being stupid, I was actually able to do it by writting:

where cido.amount_net < '0.00'

The above only returned positive numbers which is what I was looking for.

Thanks anyway!
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.