Statements that are true only if all conditions exist

Question

Post reply

Statements that are true only if all conditions exist

cory.bullard76

Ten Centuries

Points: 1232
More actions
March 8, 2016 at 1:30 pm

#399824

I have 4 different cost fields....I'd like to get rid of the records that have 0.00 in all 4.
Example:
ID Cost1 Cost2 Cost3 Cost4
1122 14.00 0.00 50.00 25.00
1133 75.00 32.00 1.00 0.00
1144 0.00 0.00 0.00 0.00
I'd like to get rid of the record for ID 1144
BUT
when I do something like:
where cost1 <> '0.00'
and cost2 <> '0.00'
and cost3 <> '0.00'
and cost4 <> '0.00'
i eliminates all 3 records. Probably because the 1st two records have '0.00' in one of the cost fields. Is there a way to say to get rid of records that has all four fields with 0.00?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Y.B. SSChampion Points: 11649 More actions · Answer 1

Just need to tweak your logic...

DECLARE @myTable TABLE (ID INT, Cost1 NUMERIC(4,2), Cost2 NUMERIC(4,2), Cost3 NUMERIC(4,2), Cost4 NUMERIC(4,2))

INSERT INTO @myTable

VALUES (1122, 14.00, 0.00, 50.00, 25.00), (1133, 75.00, 32.00, 1.00, 0.00), (1144, 0.00, 0.00, 0.00, 0.00)

SELECT * FROM @myTable

SELECT *

FROM @myTable

WHERE Cost1 <> 0.00 OR Cost2 <> 0.00 OR Cost3 <> 0.00 OR Cost4 <> 0.00

What is happening is that as soon as any of the predicates return true you return the row. When they are all zero none of them are true and therefore not returned by the SELECT statement.

SELECT quote FROM brain WHERE original = 1
0 rows returned

cory.bullard76 Ten Centuries Points: 1232 More actions · Answer 2

cory.bullard76

Ten Centuries

Points: 1232

March 8, 2016 at 1:44 pm

#1863804

That worked....thanks!

Y.B. SSChampion Points: 11649 More actions · Answer 3

cory.bullard76 (3/8/2016)
That worked....thanks!

You're welcome!

SELECT quote FROM brain WHERE original = 1
0 rows returned

Lowell SSC Guru Points: 323518 More actions · Answer 4

i've done something similar, but i just add the items together, and test for greater than zero; my query is assuming cost is a positive value, never negative, is that a safe assumption?

WHERE Cost1 + Cost2 + Cost3 + Cost4 >0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

aaron.reese SSChampion Points: 13498 More actions · Answer 5

as lowel says, only use his if you only have positive values, you could do

ABS(Cost1) + ABS(Cost2) + ABS(Cost3) + ABS(Cost4) >0

This will protect against negative numbers.