CODEWITHSUNDEEP
sqlselectconditional-statements
TheTechGuy
TheTechGuy

Reputation: 17354

What is wrong with this query AND and OR Logic

    select [Financial Year],  cast(sum(case when [IMU Visit] = 1 then 1 else 0 end) as float) as IMU_Visits from DEO_Visits
    where TotalSchool is null and visittype='regular'
    and ((Season = 'Winter' and Month  NOT in ('January','February'))
    OR (Season = 'Summer' and Month NOT in ('July','August')))
    group by  [Financial Year]
    having sum([IMU Visit]) > 0

According to me, I have written the above query correctly. I want to exclude Jan and Feb months in winter and July and August months in summer but the query is not working. It returns 93762 records weather I include the last and .. OR.

If i replace Not in but In only then I get some result from the query. Those are the records that I want to subtract from the result. What am I doing wrong? Spend a lot of time on this seemingly simple problem. Thanks

Update

The following query returns result (that basically I want to exclude). I have only changed Not in by In

    select [Financial Year],  cast(sum(case when [IMU Visit] = 1 then 1 else 0 end) as float) as IMU_Visits from DEO_Visits
    where TotalSchool is null and visittype='regular'
    and ((Season = 'Winter' and Month  in ('January','February'))
    OR (Season = 'Summer' and Month  in ('July','August')))
    group by  [Financial Year]
    having sum([IMU Visit]) > 0

Upvotes: 0

Views: 93

Answers (4)

Harshil Doshi
Harshil Doshi

Reputation: 3592

Try the following query:

 select [Financial Year],  cast(sum(case when [IMU Visit] = 1 then 1 else 0  end) as float) as IMU_Visits from DEO_Visits
 where TotalSchool is null and visittype='regular' group by  [Financial Year]
 having sum([IMU Visit]) > 0
 MINUS
 select [Financial Year],  cast(sum(case when [IMU Visit] = 1 then 1 else 0 end) as float) as IMU_Visits from DEO_Visits
 where TotalSchool is null and visittype='regular'
 and ((Season = 'Winter' and Month  in ('January','February'))
 OR (Season = 'Summer' and Month  in ('July','August')))
 group by  [Financial Year]
 having sum([IMU Visit]) > 0

I know it's not optimized in any way but as you explained that the query works fine when you use 'in' and you want to substract those results, the above query should work.

Upvotes: 0

maresa
maresa

Reputation: 641

Think of it from the logic like this:

(A and B) or (C and D)

Where

  • A --> Season = 'Winter'
  • B --> Month in ('January','February')
  • C --> Season = 'Summer'
  • D --> Month in ('July','August')

What you want is the opposite of the condition above. In other words

not ((A and B) or (C and D))

Using De Morgan's law (https://en.wikipedia.org/wiki/Logical_equivalence), you can change it to Hamlet's suggestion

not (A and B) and not (C and D)

You can apply De Morgan's law once more to make it

(not A or not B) and (not C or not D)

Therefore, you can also write the following condition

(Season <> 'Winter' or Month  not in ('January','February'))
and (Season <> 'Summer' or Month not in ('July','August'))

Or the easiest way is to simply add NOT on your condition. Frankly, I'd go with this one as it's probably the easiest to understand if you go back and re-read your SQL query.

Upvotes: 1

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

AND((Season = 'Winter' and Month not in ('January','February')) AND(Season = 'Summer' and Month not in ('July','August')))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Your logic looks correct to me. I don't see why you are converting Visits to a float however:

select [Financial Year],
       sum(case when [Visit] = 1 then 1 else 0 end) as Visits
from Visits
where TotalSchool is null and
      visittype = 'regular' and
      ((Season = 'Winter' and Month not in ('January', 'February')) or
       (Season = 'Summer' and Month not in ('July', 'August'))
      )
group by [Financial Year]
having sum([Visit]) > 0;

One possibility is the spelling of the months. If the months are 'Jan', 'Feb', etc, then the not in will always pass.

Upvotes: 0

Related Questions