How to do logic OR AND NOT across rows in SQL

  • Article

Scenario

You have a normalized table that has values in rows.   Let's take an example that has a history table with an approved flag:

 Date  Approved  DollarValue  Customer
1/1/2008  Y    13.5    A
2/1/2008  N    12      A
3/1/2008  Y    14      A

1/1/2008  Y    11.5    B
2/1/2008  Y    9        B
3/1/2008  Y    17      B

1/1/2008  Y    13.5    C
2/1/2008  Y    29        C
3/1/2008  Y    18      C 

You want to find know if all the customers with all 3 monthly values approved and the values are < 20.    Now I know you can get at this result multiple ways but I just want to show how you can apply logic across rows.

First you know that there are 3 monthly records for all the months data and you are also looking for an approved.

SELECT DISTINCT Customer
FROM MyTable
WHERE Approved = 'Y' AND DollarValue < 20
GROUP BY Customer
HAVING COUNT(Customer) = 3

You should see an output with only Customer B.

Similar techniques can be applied for OR and NOT.