Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VB.Net - SQL Queries Not Adding Up

Posted on 2013-12-12
7
Medium Priority
?
311 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 1000 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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 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 66

Expert Comment

by:Jim Horn
ID: 39714425
{Somewhat of a duplicate post, sorry..}
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 66

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 66

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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 ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

879 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