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.
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 |
|
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]SELECTTransactionDate,ActivityTypeID,ActivitySubtypeID,SUM(ROUND(TransactionValue, 2))FROM TRANSACTIONSWHERE 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. |
|
|
|
|
|
|
|