CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-server-2012
IeeTeY
IeeTeY

Reputation: 93

SQL, OR AND STATEMENT

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')

Upvotes: 0

Views: 79

Answers (3)

Lostblue
Lostblue

Reputation: 419

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()))
      )
    )

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

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
    )

Upvotes: 1

Ben Thul
Ben Thul

Reputation: 32687

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.

Upvotes: 2

Related Questions