CASE statement with AND? - 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

CASE statement with AND?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-04-24 : 06:03:25

I've got a query which ends with


AND ActivitySubTypeId IN (1, 6)

All works well except a new subtype has entered the mix and I need to vary this bit of code.

Basically, if ActivityTypeId = 3 then the bit of code above needs to be


AND ActivitySubTypeId IN (1, 6, 9)

otherwise it stays the same.....so it means having a dynamic AND (of sorts).

I've tied myself up in knots trying to combine a CASE with the AND.

Any advice appreciated, as always.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-24 : 06:12:35

Can you explain clearly? I'm not getting your point...

Follow these links to post your query

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
http://www.sqlservercentral.com/articles/Best+Practices/61537/

--
Chandu

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-04-24 : 06:17:21

[code]
AND
(
ActivitySubTypeId IN (1, 6)
OR
(
ActivityTypeId = 3 AND ActivitySubTypeId = 9
)
)
[/code]

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-04-24 : 06:17:28

[CODE]
SELECT
TransactionDate,
ActivityTypeID,
ActivitySubtypeID,
SUM(ROUND(TransactionValue, 2))

FROM TRANSACTIONS

WHERE TransactionDate >= CONVERT(CHAR(8),DATEADD(MM,-1,GETDATE()),112)
AND TransactionDate <= CONVERT(CHAR(8),GETDATE(),112)

AND ActivitySubtypeId IN (1, 6)

GROUP BY TransactionDate, ActivityTypeID, ActivitySubtypeID
[/CODE]

If ActivityTypeID = 3 then the red bit of code needs to be


AND ActivitySubtypeId IN (1, 6, 9)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-24 : 06:28:47

I think Ifor's solution will work fine...

--
Chandu

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-04-24 : 06:29:03

Do not do needless casts, they are expensive:


WHERE TransactionDate >= DATEADD(day, DATEDIFF(day, 0, DATEADD(month, -1, CURRENT_TIMESTAMP)), 0)
	AND TransactionDate <= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
	AND
	(
		ActivitySubTypeId IN (1, 6)
		OR
		(
			ActivityTypeId = 3 AND ActivitySubTypeId = 9
		)
	)

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-04-24 : 06:41:52

Thanks for the input, I will try it later

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-04-26 : 14:00:24

Just a follow-up, Ifor's code worked perfectly.

Many thanks, much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources