CODEWITHSUNDEEP
sqlsql-servert-sql
007
007

Reputation: 2186

T-SQL - having arithmetic deficiency syndrome

DECLARE @Table TABLE (CID int, C1 varchar(5), C2 decimal(18,6), C3 decimal(18,6))
INSERT INTO @Table
SELECT 1,'A',0.0,0.0
UNION ALL
SELECT 2,'A',0.1,0.0
UNION ALL
SELECT 3,'A',0.1,0.1
UNION ALL
SELECT 4,'B',0.0,0.0
UNION ALL
SELECT 5,'B',0.1,0.0
UNION ALL
SELECT 6,'B',0.1,0.1

SELECT * FROM @Table
WHERE C1 NOT IN ('B') AND (C2 >= 0 AND C3 >= 0)

/* Desired output
CID     C1      C2      C3
1       A       0.0     0.0
2       A       0.1     0.0
3       A       0.1     0.1
4       B       0.0     0.0
*/

If C2 and C3 are greater than 0 then no record with C1 = B should be returned. I'm having trouble figuring out the logic. I can't seem to figure out the arithmetic. :(

Thank you

p.s. a non-subquery sln would be awesome!

Upvotes: 0

Views: 62

Answers (2)

Anon
Anon

Reputation: 10908

Inclusive, consider the rows you do want:

SELECT * FROM @Table WHERE (C1 NOT IN ('B')) OR (C2 <= 0 AND C3 <= 0)

Exclusive, consider the rows you do not want:

SELECT * FROM @Table WHERE NOT (C1 IN ('B') AND (C2 > 0 OR C3 > 0))

These are algebraically equivalent, so pick the one that reads best.

Upvotes: 1

Michael Petito
Michael Petito

Reputation: 13161

Your logic is: "do not include the record if C2 > 0 AND C3 > 0 AND C1 = 'B'"

Negate it, which results in: C2 <= 0 OR C3 <= 0 OR C1 <> 'B' and use this condition to include all of the other records.

Note, if you meant to exclude records where (C2 > 0 OR C3 > 0) AND C1 = 'B', then you would negate that to include the other records: (C2 <= 0 AND C3 <= 0) OR C1 <> 'B'

Upvotes: 1

Related Questions