pokerplayer23 pokerplayer23 - 1 year ago 57
SQL Question

SQL Server - Using Case in a Where IN section of a Join

I'm trying to use

for a
clause of my code in a join. Is this not possible to do or is my syntax just bad? Any suggestions would be greatly appreciated. Thanks.

MyTable ON MyTable.ID = SomeTable.ID
AND MyTable.ResultDate IN (CASE
WHEN metricid IN ('0040','0015')
THEN '('01312016','03312016','06302016')'
ELSE '('20160630')'

I am using SQL Server 2014.

Answer Source

You can't but you can do something like this:

    MyTable ON MyTable.ID = SomeTable.ID 
            AND ((metricid IN ('0040','0015') AND MyTable.ResultDate IN ('01312016','03312016','06302016')) OR ((metricid NOT IN ('0040','0015') AND MyTable.ResultDate='20160630')))

It's ugly but it should work :)

Anoter approach would be to have to queries and do a UNION. So, the first one filters by metricid IN ('0040','0015') and does a LEFT JOIN to MyTable based on ResultDate IN ('01312016','03312016','06302016'). And the other one filters by metricid NOT IN.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download