case condition - 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

SQL Server 2012 Forums

Transact-SQL (2012)

case condition

Author

Topic

indralang
Starting Member

7 Posts

Posted - 2015-02-26 : 01:27:12

I have data like this:
Code as varchar, Amount as numeric(3,0)


Code      Amount  
A          100
B          150
C          200
D          250
E         -100
F         -150
G         -200
H         -250
I           0

I have parameter @overbudget as char

if @overbudget = '1', then the result is (E,F,G,H).
if @overbudget = '0', then the result is (A,B,C,D,I).

This is my query.


SELECT * FROM TEST 
GROUP BY CODE, AMOUNT 
HAVING
(CASE WHEN ISNULL(@Overbudget,'') = '1' THEN AMOUNT ELSE NULL END) < 0
OR
(CASE WHEN ISNULL(@Overbudget,'') = '0' THEN AMOUNT ELSE NULL END) > 0

Is there any simple query to select my data?
Thank you for your help.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-26 : 02:29:45

that is simple! but please use WHERE instead of GROUP BY/HAVING since you are not aggregating anything