“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?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.