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?
 
appariConnect With a Mentor Commented:
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
 
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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Umar TopiaConnect With a Mentor .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
 
LowfatspreadConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.