How to use SELECT statement with different conditions in SQL SERVER

Question

I have a sql server database table with information such as below:

TEST Priority [tolerance %] [% Diff] 
24  critical    5    0.000000000000000

21  critical    5    12.000000000000000
20  critical    5    0.000000000000000
17  critical    5    0.189035916824100
19  critical    5    0.000000000000000
18  critical    5    20.000000000000000

29  critical    5    0.000000000000000
121 critical    5    0.862068965517200
28  critical    5    0.000000000000000
30  critical    5   -1.030927835051500
34  critical    5   -0.080671184252900
32  critical    5    0.000000000000000
33  critical    5    0.000000000000000
35  medium     15    0.000000000000000
31  critical    5    0.000000000000000
36  critical    5    0.000000000000000
37  critical    5   -7.246376811594200
38  high       10    0.000000000000000
39  medium     15   30.197604790419100
40  medium     15   -0.529100529100500
41  critical    5   0.831255195344900
44  critical    5   -0.141843971631200
43  critical    5   -0.147167034584200
42  critical    5   -9.478468899521500
48  medium     15   0.000000000000000
45  medium     15   0.116144018583000
46  medium     15   -2.469135802469100
47  medium     15   0.000000000000000
49  critical   5    0.000000000000000
51  low       20    0.000000000000000
53  high      10    -1.420118343195200
52  high      10    -23.125786163522000
54  medium    15    0.000000000000000

I want to extract a report which meets the following conditions:

1. priority = critical and [% Diff] is either >= 5 or <= -5 
2. priority = high and [% Diff] is either >= 10 or <= -10
3. priority = low and [% Diff] is either >= 20 or <= -20
4. priority = medium and [% Diff] is either >= 15 or <= -15

Here is my code:

    Select * from Table1
    where Priority = 'critical' 
    and ([% Diff] > = 5 or [% Diff ] <= -5 )
    OR (Priority = 'high' and ([% Diff ] > = 10 or [% Diff] <= -10 ))
    OR (Priority = 'LOW' and ([% Diff ] > = 20 or [% Diff ] <= -20 ))
    OR (Priority = 'MEDIUM' and ([% Diff ] > = 15 or [% Diff ] <= -15 ))

It seems like am missing something here, can anyone assist me please? Someone suggested the use of case statement but not sure how this is going to work?

With `or` you are initially getting all the records. You need to apply case statements [case](https://stackoverflow.com/a/5487936/2630817) — Just code, Aug 23 '17 at 12:46
What r you getting? it seems like you are missing parentheses on `Priority = 'critical' and ([% Diff] > = 5 or [% Diff ] <= -5 )` — Guy Segev, Aug 23 '17 at 12:48
What is your result now and what you want it to be? (It seems to me your bracktes are ok) — etsa, Aug 23 '17 at 12:50

score 1 · Accepted Answer · answered Aug 23 '17 at 12:47

You're just missing a pair of parenthesis around the first set of conditions:

Select   * 
From     Table1
Where    (Priority = 'critical' And ([% Diff] >= 5 or [% Diff ] <= -5 ))
Or       (Priority = 'high'     And ([% Diff ] >= 10 or [% Diff] <= -10 ))
Or       (Priority = 'LOW'      And ([% Diff ] >= 20 or [% Diff ] <= -20 ))
Or       (Priority = 'MEDIUM'   And ([% Diff ] >= 15 or [% Diff ] <= -15 ))

N1gthm4r3 · Answer 2 · 2017-08-23T12:53:03.673

-1

You can imporve your code like this ->

Select * from Table1
where (Priority = 'critical' and [% Diff] not Between  -5 and 5 )
OR (Priority = 'high' and [% Diff ] not between -10 and 10 )
OR (Priority = 'LOW' and [% Diff ] not between -20 and 20 )
OR (Priority = 'MEDIUM' and [% Diff ] not between -15 and 15)

edited Aug 23 '17 at 12:53

answered Aug 23 '17 at 12:49

N1gthm4r3

755
1
11
23

`BETWEEN` doesn't make any sense here if they want everything that's *not* between those numbers... – Siyual Aug 23 '17 at 12:52
This would still exclude the outer points, which are meant to be included (i.e. they're wanting everything `<= -5` and `>= 5` - `NOT BETWEEN -5 AND 5` would also exclude `-5` and `5` from returning.) – Siyual Aug 23 '17 at 12:54

How to use SELECT statement with different conditions in SQL SERVER

2 Answers2