Solved

VB.Net - SQL Queries Not Adding Up

Posted on 2013-12-12
7
298 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
t-sql Joining Issue 10 40
date diff with Fiscal Calendar 4 30
insert wont work in SQL 14 22
transaction in asp.net, sql server 6 33
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

777 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