complex SQL Query with multiple ANDs and ORs

ive got a table that has 3 different date fields, 1 task status field, 1 employee ID field (there are more  fields, but those are the important ones).  I'm building a report where i collect the daily activity of an employee.  It needs to return any record where the current date resides in either of the 3 date fields, AND that only returns records that belong to the specified employee, AND of those records, only brings back records where the status is 'completed'

SELECT     *
FROM         myTable
WHERE     (date_completed BETWEEN '04/13/2010' AND '04/13/2010')
OR            (date_due BETWEEN '04/13/2010' AND '04/13/2010')
OR            (date_assigned BETWEEN '04/13/2010' AND '04/13/2010')
AND          (empID = '55') AND (taskStatus = 'completed')
bg_marketingAsked:
Who is Participating?
 
chapmandewCommented:
SELECT     *
FROM         myTable
WHERE    
(
 (date_completed BETWEEN '04/13/2010' AND '04/13/2010')
OR            (date_due BETWEEN '04/13/2010' AND '04/13/2010')
OR            (date_assigned BETWEEN '04/13/2010' AND '04/13/2010')
)
AND          (empID = '55') AND (taskStatus = 'completed')
0
 
bg_marketingAuthor Commented:
curiously, I tried that and it didn't work.  the weird thing is that Management Studio returns the query rewritten to something "optimized" and the resulting new query doesn't return any results, where I know it should be bring back several. :(  Additionally before the question comes up, i mentioned the report as being a daily, but the begin and end dates are set at the time report launches, so the date range for all 3 days IS necessary
0
 
Rey Obrero (Capricorn1)Commented:
SELECT     *
FROM         myTable
WHERE    
 ((empID = '55') AND (taskStatus = 'completed')) And
((date_completed BETWEEN '04/13/2010' AND '04/13/2010')
OR            (date_due BETWEEN '04/13/2010' AND '04/13/2010')
OR            (date_assigned BETWEEN '04/13/2010' AND '04/13/2010'))
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
lwadwellCommented:
Do your date fields contain only dates (i.e. the time is always midnight) or do they also have time (i.e. not midnight) components also?  If they have time - the between your using has the same date will only include those records where the time component is midnight only, in that case you might want to TRUNC() the dates or change the 'to' end of your between values.
0
 
ThomasianCommented:
Does the date fields involved have a "time" value? Note that '04/13/2010' = '04/13/2010 12:00 AM', so your query will only return when the date is 4/13/2010 and the time is 12:00 AM.

To return all records from that date (ignoring the time field):
SELECT *
FROM myTable
WHERE
  ((date_completed >= '04/13/2010' AND date_completed < '04/14/2010') 
   OR (date_due >= '04/13/2010' AND date_due < '04/14/2010') 
   OR (date_assigned >= '04/13/2010' AND date_assigned < '04/14/2010')
  ) AND (empID = '55') AND (taskStatus = 'completed')

Open in new window

0
 
chapmandewCommented:
>>curiously, I tried that and it didn't work.  the weird thing is that Management Studio returns the query rewritten to something "optimized" and the resulting new query doesn't return any results

Are you posting it in a new query window, and NOT the query designer?
0
 
LowfatspreadCommented:
SELECT     *
FROM         myTable
WHERE empID = '55'
     AND taskstatus= 'completed'
    AND (date_completed BETWEEN '20100413' and '20100413 23:59:59.999'  
             OR date_due BETWEEN '20100413' and '20100413 23:59:59.999'
             OR date_assigned BETWEEN '20100413' and '20100413 23:59:59.999'
           )      
0
 
bg_marketingAuthor Commented:
i realized i was doing something incorrect, when i fixed it chapmandew's answer worked

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.