Exclude SQL report records with all zero values in transaction fields

W D
W D used Ask the Experts™
on
I have a complicated SQL report based upon the joining of several tables.
The report includes these fields:
ProductNumber, ProductName, BeginningQOH, QtyReceived, QtyPicked,QtyReturned,QtyCycleCounted, QtyDamaged, QtyRecycled, EndQOH

Sometimes records have all  0's in the qty fields: BeginningQOH, QtyReceived, QtyPicked,QtyReturned,QtyCycleCounted, QtyDamaged, QtyRecycled, EndQOH

I'd like to exclude the records that have all 0's in these fields. I tried using a HAVING statement such as HAVING BeginningQOH  + QtyReceived + QtyPicked + QtyReturned + QtyCycleCounted + QtyDamaged + QtyRecycled + EndQOH > 0

but records such as BeginningQOH = 28, QtyCycleCounted = -28, EndQOH = 0 (the other fields have 0) were left out.

Then I tried using a series of ANDs such as HAVING BeginningQOH > 0 AND QtyReceived > 0 AND QtyPicked > 0, etc but this excluded records that shouldn't be excluded also.

Any thoughts on what I can do to exclude the records that have all 0's in these fields?

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Can you post your query that you are using please?
P.
Commented:
Also, instead of using a HAVING clause use a WHERE clause with OR and see if it helps. Something like this:
Select ProductNumber, 
       ProductName, 
       BeginningQOH, 
       QtyReceived, 
       QtyPicked,
       QtyReturned, 
       QtyCycleCounted, 
       QtyDamaged, 
       QtyRecycled, 
       EndQOH
FROM   YourTable
Where  BeginningQOH > 0 OR 
       QtyReceived > 0 OR 
       QtyPicked > 0 OR
       QtyReturned > 0 OR
       QtyCycleCounted > 0 OR 
       QtyDamaged > 0 OR 
       QtyRecycled > 0 OR 
       EndQOH > 0

Open in new window

W DData Analyst

Author

Commented:
pssandhu, the series of OR statements work!
I just went through the report results and they were correct.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial