SQL, OR AND STATEMENT

Question

Does the AND Statement after the OR statement have the same logic as something similar to

where  d.res_id = 125 and t.task_type in( 'PPB', 'PPO') 
 and 
  d.status = ('C')
 OR 
  d.status IN ('R' ,'D') 
     AND
    t.done_dt = DATEADD(month, -1, GETDATE())

where  d.res_id = 125 and t.task_type in( 'PPB', 'PPO')
IF d.status  IN ('R','D')
  t.done_dt = DATEADD(month, -1, GETDATE())
 ELSE 
  d.status = 'C'

Which one is the correct way to format it?

I'm trying to display all d.status with the 'C' status. But only if the t.done_dt is the previous month, do I want to display d.status IN ('R', 'D')

well second one doesnt have the right sintaxis. What you want to do?. You are probably asking the wrong question [**What is the XY problem?**](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) — Juan Carlos Oropeza, Mar 31 '17 at 15:10
Possible duplicate of [SQL Logic Operator Precedence: And and Or](http://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or) — JimmyB, Mar 31 '17 at 15:11
Im trying to display all d.status with the 'C' status. But only if the t.done_dt is the previous month, do I want to display d.status IN ('R', 'D') — IeeTeY, Mar 31 '17 at 15:13

score 2 · Answer 1 · answered Mar 31 '17 at 15:12

Because of Operator Precedence, and evaluates before or. So, you get something like this:

where  d.res_id = 125 and t.task_type in( 'PPB', 'PPO') 
 and 
  d.status = ('C')
 OR 
  (
    d.status IN ('R' ,'D') 
     AND
    t.done_dt = DATEADD(month, -1, GETDATE())
  )

That said, I like to use the parentheses explicitly so I don't have to think about it so hard.

score 1 · Accepted Answer · answered Mar 31 '17 at 15:16

I guess you want to translate this pseudo code into SQL?

where  d.res_id = 125 and t.task_type in( 'PPB', 'PPO')
AND
 IF d.status  IN ('R','D')
     t.done_dt = DATEADD(month, -1, GETDATE())
 ELSE 
     d.status = 'C'

That would be:

where  d.res_id = 125 and t.task_type in( 'PPB', 'PPO')
AND (    ( d.status = 'C' )  -- SHOW ALL 'C'
      OR ( d.status  IN ('R','D') and t.done_dt = DATEADD(month, -1, GETDATE()) )
                             -- SHOW ALL R,D on that date
    )

score 0 · Answer 3 · answered Mar 31 '17 at 15:29

In the first place don't bother with operators precedence and use explicit ( ). It will add to your readability.

If you want to see all status 'R','D' of your previous month you would need to change a bit your query:

where d.res_id = 125 and t.task_type in ('PPB', 'PPO') AND
   (  d.status = 'C'
   OR ( d.status IN ('R', 'D') and
        -- Compare the first day of the month of your date and first day of the current month 
        CONVERT(DATE,dateadd(dd,-(day(t.done_dt)-1),t.done_dt)) = DATEADD(MONTH, -1, CONVERT(DATE,dateadd(dd,-(day(getdate())-1),getdate()))
      )
    )

SQL, OR AND STATEMENT

3 Answers3