Solved

Trying to filter out Zero rows

Posted on 2011-09-27
9
198 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:sammySeltzer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 2

Expert Comment

by:ramkihardy
ID: 36714548
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
 
LVL 39

Accepted Solution

by:
appari earned 167 total points
ID: 36714557
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
 
LVL 10

Expert Comment

by:Umar Topia
ID: 36714640
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 10

Assisted Solution

by:Umar Topia
Umar Topia earned 167 total points
ID: 36714644
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 36714829
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 166 total points
ID: 36715159
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
 
LVL 29

Author Comment

by:sammySeltzer
ID: 36716443
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
 
LVL 29

Author Comment

by:sammySeltzer
ID: 36716592
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
 
LVL 29

Author Closing Comment

by:sammySeltzer
ID: 36716610
Thanks again all for your assistance. The response was overwhelming and accurate.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question