Solved

VB.Net - SQL Queries Not Adding Up

Posted on 2013-12-12
7
304 Views
Last Modified: 2013-12-12
Good Day Expert!

I have finally narrowed down my total counts not matching issue to my queries.  Hopefully you can help.

Select count(*) as 'Count' from [Paidfile] where [Check Date] >= '11/1/2012' and [Check Date] <= '10/31/2013'

Select count(*) as 'EDI Count' from [Paidfile] where [Check Date] >= '11/1/2012' and [Check Date] <= '10/31/2013' and [Batch] = 'XF'      

Select count(*) as 'Paper Count' from [Paidfile] where [Check Date] >= '11/1/2012' and [Check Date] <= '10/31/2013' and [Batch] <> 'XF'  

Ok.  My first query returns 101030.  The second one returns 71729.  The third one returns 29290.  I tracked the 11 count difference to 1 record where [Batch] was blank and 10 records where [Batch] was null.

Shouldn't that 3rd query have caught it when I asked [Batch] <> XF? So, I added [Batch] = '' and it caught the one record where [Batch] = ''.  How do I ask for when [Batch] is Null?

Thanks,
jimbo99999
0
Comment
Question by:Jimbo99999
[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
  • 4
  • 2
7 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 250 total points
ID: 39714411
I'm not certain where the difference of 11 that you speak of is coming from. You don't show any numbers that vary by difference of 11. Where is the 11 coming from?

How do I ask for when [Batch] is Null?
Quite literally exactly what you said:

Select count(*) as 'Paper Count' from [Paidfile] where [Check Date] >= '11/1/2012' and [Check Date] <= '10/31/2013' and [Batch] IS NULL

Open in new window

0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
ID: 39714420
>Shouldn't that 3rd query have caught it when I asked [Batch] <> XF?
Nope nope nope nope nope.

T-SQL can't compare NULL to anything, so <> 'any value' (XF, banana, or even wackadoo) will not pick it up.   In order to get the row count auditing correct you'll have to add this query.

Select count(*) as 'EDI Count' from [Paidfile] 
where [Check Date] IS NULL OR [Batch] IS NULL

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39714425
{Somewhat of a duplicate post, sorry..}
0
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.

 

Author Comment

by:Jimbo99999
ID: 39714432
101030 is the TotalCount
71729 is EDICount where [Batch] = 'XF'
29290 is PaperCount where [Batch] <> 'XF

When adding 71729 and 29290, I get 101019.

I don't understand why the totals don't add up.  The query where [Batch] <> 'XF' is not getting the null values.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39714451
>The query where [Batch] <> 'XF' is not getting the null values.
Correct.  NULL will NOT (not not not) be captured by <> 'XF', because NULL can't be compared to anything, meaning it will return a NULL back.  You'll need to use ISNULL() or COALESCE() to handle the NULLS.

Such as..
Select count(*) as 'Paper Count' from [Paidfile] 
where [Check Date] >= '11/1/2012' and [Check Date] <= '10/31/2013' and ISNULL([Batch], 'wahooooo') <> 'XF'

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39714493
To illustrate, copy-paste the below code into your SSMS, and let 'er rip
IF OBJECT_ID('tempdb..#foo') IS NOT NULL
   DROP TABLE #foo
GO

CREATE TABLE #foo (num int)

INSERT INTO #foo (num) 
VALUES 
   (1), (1), (1), (1), (1), 
   (14), (42), (99), (90210), (812), 
   (NULL), (NULL)
	
SELECT 'Row count, including NULL', COUNT(*) FROM #foo
SELECT 'Row count, not including NULL', COUNT(num) FROM #foo
SELECT 'The 1s', COUNT(*) FROM #foo WHERE num = 1
SELECT 'The not 1s, not including NULLs', COUNT(*) FROM #foo WHERE num <> 1
SELECT 'The not 1s, including NULL', COUNT(*) FROM #foo WHERE ISNULL(num, -1) <> 1
SELECT 'The NULLs', COUNT(*) FROM #foo WHERE num IS NULL

Open in new window

0
 

Author Closing Comment

by:Jimbo99999
ID: 39714537
Thanks very much for both of your responses.  It is working now!

I always enjoying learning new things everyday when it comes to programming.

Thanks again,
jimbo99999

jim: I will try your example here this afternoon...I have to get the report formatted and to the requestor.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

749 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