Solved

VB.Net - SQL Queries Not Adding Up

Posted on 2013-12-12
7
294 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

914 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

15 Experts available now in Live!

Get 1:1 Help Now