Solved

Trying to filter out Zero rows

Posted on 2011-09-27
9
191 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
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 28

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 28

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 28

Author Closing Comment

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Benefits of SMB Fileshare 3 63
Help writing a query 6 72
SQL Query with Sum and Detail rows 2 42
ISDATE() not working properly on my table? Any suggestions. 7 12
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now