Trying to filter out Zero rows

Greetings again mates,

This is one of the simplest, yet one of the weirdest issues I have had to deal with.

I am still working on someone else's work.
 
When records are created and stored in the db, an extra row or two with zero records are stored.

Please see attached screenshot.

The problem is that most of the time, 2 or 3 rows have values either in the check AMount column or Cash column. The remaining 2 or so rows have 0 values.

We would like to show only the rows with data.

The screenshot shows only 3 rows have data 2 in check column and 1 row in cash column. The remaining rows have zeros (0).

The code below doesn't work because it either  shows 1 row or blank rows.

Any help is of course appreciated.


SELECT employee_ded_amts.ID, employee_ded_amts.employee_ID, employee_ded_amts.charity_code, employee_ded_amts.chcknum, employee_ded_amts.check_amt, 
                         employee_ded_amts.one_time, employee_ded_amts.bi_weekly, employee_ded_amts.cash, employee_ded_amts.donate_choice, 
                         employee_ded_amts.date_stamp, employee_ded_amts.amb_approved, active_empl.dept_code, active_empl.empl_first, active_empl.empl_last
                         FROM employee_ded_amts INNER JOIN active_empl ON employee_ded_amts.employee_ID = active_empl.employee_id
                         WHERE ((cash <> 0 and employee_ded_amts.check_amt <> 0) AND employee_ded_amts.employee_id = '00088899999' ORDER BY employee_ded_amts.employee_ID

Open in new window


donations.JPG
LVL 29
sammySeltzerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ramkihardyCommented:
I understand your question sammy, Could you please post in what condition you want select the data or any sample result that you want..
Regards
Ramki
0
appariCommented:
try this
SELECT employee_ded_amts.ID, employee_ded_amts.employee_ID, employee_ded_amts.charity_code, employee_ded_amts.chcknum, employee_ded_amts.check_amt, 
                         employee_ded_amts.one_time, employee_ded_amts.bi_weekly, employee_ded_amts.cash, employee_ded_amts.donate_choice, 
                         employee_ded_amts.date_stamp, employee_ded_amts.amb_approved, active_empl.dept_code, active_empl.empl_first, active_empl.empl_last
                         FROM employee_ded_amts INNER JOIN active_empl ON employee_ded_amts.employee_ID = active_empl.employee_id
                         WHERE ((cash <> 0 or employee_ded_amts.check_amt <> 0) AND employee_ded_amts.employee_id = '00088899999' ORDER BY employee_ded_amts.employee_ID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Umar Topia.Net Full Stack DeveloperCommented:
Try this
SELECT employee_ded_amts.ID, employee_ded_amts.employee_ID, employee_ded_amts.charity_code, employee_ded_amts.chcknum, employee_ded_amts.check_amt, 
                         employee_ded_amts.one_time, employee_ded_amts.bi_weekly, employee_ded_amts.cash, employee_ded_amts.donate_choice, 
                         employee_ded_amts.date_stamp, employee_ded_amts.amb_approved, active_empl.dept_code, active_empl.empl_first, active_empl.empl_last
                         FROM employee_ded_amts INNER JOIN active_empl ON employee_ded_amts.employee_ID = active_empl.employee_id
                         WHERE (cash > 0 and employee_ded_amts.check_amt > 0) AND employee_ded_amts.employee_id = '00088899999' ORDER BY employee_ded_amts.employee_ID

Open in new window

0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Umar Topia.Net Full Stack DeveloperCommented:
Sorry about the previous post
SELECT employee_ded_amts.ID, employee_ded_amts.employee_ID, employee_ded_amts.charity_code, employee_ded_amts.chcknum, employee_ded_amts.check_amt, 
                         employee_ded_amts.one_time, employee_ded_amts.bi_weekly, employee_ded_amts.cash, employee_ded_amts.donate_choice, 
                         employee_ded_amts.date_stamp, employee_ded_amts.amb_approved, active_empl.dept_code, active_empl.empl_first, active_empl.empl_last
                         FROM employee_ded_amts INNER JOIN active_empl ON employee_ded_amts.employee_ID = active_empl.employee_id
                         WHERE (cash > 0 or employee_ded_amts.check_amt > 0) AND employee_ded_amts.employee_id = '00088899999' ORDER BY employee_ded_amts.employee_ID

Open in new window

0
Bhavesh ShahLead AnalysistCommented:
Hi,

Your query seems right.

But there could be possibilities that columns having NULL value, in that case your filter wont work.

Try using IsNull(Cash,0) > 0

- Bhavesh
0
LowfatspreadCommented:
actually shouln't it be cash<>0 or check_amt <>0

and you need to allow for negative amts ?




SELECT DA.ID, DA.employee_ID, DA.charity_code, DA.chcknum, DA.check_amt, 
       DA.one_time, DA.bi_weekly, DA.cash, DA.donate_choice, 
       DA.date_stamp, DA.amb_approved, AE.dept_code, AE.empl_first, AE.empl_last
  FROM employee_ded_amts as DA
 INNER JOIN active_empl as AE
    ON DA.employee_ID = AE.employee_id
 WHERE (DA.cash <> 0 or DA.check_amt <> 0)
   AND DA.employee_id = '00088899999' 
 ORDER BY DA.employee_ID

Open in new window

0
sammySeltzerAuthor Commented:
Thanks for the tremendous responses.

I will start testing out the various codes.

This is really weird as I have tried a variation of codes.

Just a by the way, the checkamount and cashamount fields are of money data type.

I neglected to indicate that last night.

No possibility of negative amounts but you never know.
0
sammySeltzerAuthor Commented:
I tested your codes (Lowfatspread, umartopia,appari) and I must say that I am truly impressed.

Not only did I try this many different ways, I have a "sql guru" here who tried as well and we could not get it to work.

You guys did it.

I am grateful, very grateful.
0
sammySeltzerAuthor Commented:
Thanks again all for your assistance. The response was overwhelming and accurate.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.