Query returns no records unless I add a date range to the WHERE clause

Question

Query returns no records unless I add a date range to the WHERE clause

dana 6761

Grasshopper

Points: 20
More actions
March 21, 2014 at 8:14 am

#291092

I have a query in a stored procedure that I want to put into a view to improve response time. The query checks to see if any records are missing from the Client Log (due to human error of course 🙂 ).
I've been calling this stored procedure and passing the parameters @BeginDate and @EndDate.
Note: there may be multiple entries for the same AcctNum in the Client Log table, but there should only be 1 record for each AcctNum in the Unique Values table.
SELECT DISTINCT AcctNum, LName + ', ' + FName as Client, GroupNum, DateOfService
FROM [ABC].[dbo].[tblUniqueValues]
WHERE AcctNum not in (
SELECT AcctNum from [EFG].[dbo].[tblClientLog]
where DateOfService between @BeginDate and @EndDate)
AND DateOfService between @BeginDate and @EndDate
ORDER BY DateOfService
The above query returns a few records, which I expected to see.
So I created the view based on this query but without the date ranges:
SELECT DISTINCT AcctNum, LName + ', ' + FName as Client, GroupNum, DateOfService
FROM [ABC].[dbo].[tblUniqueValues]
WHERE AcctNum not in (SELECT AcctNum from [EFG].[dbo].[tblClientLog] )
ORDER BY DateOfService
But this new query returns 0 records.
Incidentally, the query inside the WHERE clause:
SELECT AcctNum from [EFG].[dbo].[tblClientLog]
returns a quarter million records because the database covers several years worth of log entries.
The AcctNums are not reused, so records outside of my date range should have different AcctNums. I expected the new query (without the date range) to return more records than the original.
Any ideas why the query returns records when I use the date range, but returns no records when I don't?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

In your second query, you also left out the data range where clause in the subquery on [dbo].[tblClientLog].

This means you will now have the entire table of tblclientLog as a result, on which you do a NOT IN. It is apparently likely that an AcctNum has appeared at least once during your entire log history.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Koen Verbeeck (3/21/2014)
In your second query, you also left out the data range where clause in the subquery on [dbo].[tblClientLog].
This means you will now have the entire table of tblclientLog as a result, on which you do a NOT IN. It is apparently likely that an AcctNum has appeared at least once during your entire log history.

Or there's a NULL returned by SELECT AcctNum from [EFG].[dbo].[tblClientLog].

If that's the case, try changing it for NOT EXISTS.

Here's a reference to show the slight difference of NOT IN vs NOT EXISTS: http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

dana 6761 (3/21/2014)
I have a query in a stored procedure that I want to put into a view to improve response time...

It won't improve response time.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dana 6761 Grasshopper Points: 20 More actions · Answer 4

Luis Cazares (3/21/2014)
Koen Verbeeck (3/21/2014)
In your second query, you also left out the data range where clause in the subquery on [dbo].[tblClientLog].
This means you will now have the entire table of tblclientLog as a result, on which you do a NOT IN. It is apparently likely that an AcctNum has appeared at least once during your entire log history.
Or there's a NULL returned by SELECT AcctNum from [EFG].[dbo].[tblClientLog].
If that's the case, try changing it for NOT EXISTS.
Here's a reference to show the slight difference of NOT IN vs NOT EXISTS: http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

That was it: The SELECT statement did return a couple NULL values and that was causing the problem.

Thank you both, Koen and Luis for your help.