“dynamic” where statement in sql server 2008 r2

I need to pass two different WHERE statements based on a result in the select statement. My base query is the following

SELECT table1.taskID, 
    table1.startTime, 
    table1.endTime, 
    table1.everyMinutes, 
    table3.dlid, 
    table3.run 
FROM table1
JOIN table2
    ON table1.taskID = table2.taskID
LEFT JOIN table3
    ON table1.taskID = table3.taskID
WHERE table2.active = '1'
ORDER BY taskID ASC

What I am struggling with is if table3.run = '1', then I need to include the following

CONVERT(DATE,GETDATE()) IN (SELECT dldate FROM table4 WHERE dlid = table3.dlid)

if table3.run = '0' I need to have the following

CONVERT(DATE,GETDATE()) NOT IN (SELECT dldate FROM table4 WHERE dlid = table3.dlid)

and finally, if table3.run = 'NULL' then I need to have neither of those statements

Answers


Try

SELECT table1.taskID, 
    table1.startTime, 
    table1.endTime, 
    table1.everyMinutes, 
    table3.dlid, 
    table3.run 
FROM table1
JOIN table2
     ON table1.taskID = table2.taskID
LEFT JOIN table3
    ON table1.taskID = table3.taskID
WHERE table2.active = '1'
    AND (
            (table3.run = '1' 
             and CONVERT(DATE,GETDATE()) 
             IN (SELECT dldate FROM table4 WHERE dlid = table3.dlid)) 

         or (table3.run = '0' 
             and CONVERT(DATE,GETDATE()) NOT IN 
            (SELECT dldate FROM table4 WHERE dlid = table3.dlid))

         or table3.run = 'NULL'   -- incredibly bad idea
         )
ORDER BY taskID ASC

Need Your Help

How to not load images for smaller screen

javascript image image-size

I have come across numerous solutions from different articles but which one would put the least strain on the CPU?