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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.