Case with parameters - 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 2000 Forums

Transact-SQL (2000)

Case with parameters

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-10-17 : 10:35:56

I have the following SQL code that I use in sql reporting services, the @state and @date are dropdown boxes.

select sum(AccountMV)
from Snapsraw
where (Branchstate = @state) and sicid is not null
and monthend = @date

Everything works fine, except that when a user select a certain state
to run the report off of, lets say Wisconsin, I want the code to looking something like this instead:

select sum(accountmv) from SnapsRaw where branchstate = @state
and ((SicID is not null and sicid <>'00') or (sicid = '00' and SectorID <> '100')) and sicid <> '85'
and MonthEnd = @date

I realize I may need to use a case statement, but am having some issues figuring out how it should be layed out, any help would be greatly appreciated.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 10:52:09

Why case? what's wrong with the second query you posted? What is correlation between state and other conditions?

Harsh Athalye
India.
"Nothing is Impossible"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-10-17 : 10:59:56

I need to include both in my stored procedure, how would I instruct the logic to use one over the other when a certain state is picked. Lets say i have 10 states that they can pick from, 9 out of the 10 need to use the first statement, and the 10th state needs to use the 2nd set

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 11:17:37

why not UNION?

select 
	sum(AccountMV) AccountMV
from
	Snapsraw
where
	Branchstate = @state and sicid is not null and monthend = @date and @state = 'winsconsin'
union all

select 
	sum(accountmv) AccountMV
from
	SnapsRaw 
where 
	branchstate = @state
	and ((SicID is not null and sicid <>'00') or (sicid = '00' and SectorID <> '100')) 
	and sicid <> '85'
	and MonthEnd = @date
	and @state <> 'winsconsin'

Harsh Athalye
India.
"Nothing is Impossible"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-10-17 : 11:20:31

Wont this bring back both results than? I only want the second select statement to run if the state is wisconsin, if not, run the first statement.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 11:22:55

quote:
Originally posted by duhaas

Wont this bring back both results than? I only want the second select statement to run if the state is wisconsin, if not, run the first statement.

Check out the last condition in both SELECT statements...either state can be 'winsconsin' or it can not...either of query will only execute, not both.

Harsh Athalye
India.
"Nothing is Impossible"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-10-17 : 11:30:18

My apologizes, I think that might just work, I will do some testing, I appreciate your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources