Inconsistent Query Results

[NWChowd]

I am baffled as to the results that I am receiving from the following Queries:

Query #1:
SELECT claimno,[lineno],pidate,deneop FROM claimlin
WHERE paiddate BETWEEN (GETDATE() - 11) AND (GETDATE() - 5)

returns 49462 records.

Query #2:
SELECT claimno,[lineno],pidate,deneop FROM claimlin
WHERE paiddate BETWEEN (GETDATE() - 11) AND (GETDATE() - 5)
AND deneop = '003'

returns 1397 records.

Query #3:
SELECT claimno,[lineno],pidate,deneop FROM claimlin
WHERE paiddate BETWEEN (GETDATE() - 11) AND (GETDATE() - 5)
AND deneop = 'DUP'

returns 1952 records.

Query #4:
SELECT claimno,[lineno],pidate,deneop FROM claimlin
WHERE paiddate BETWEEN (GETDATE() - 11) AND (GETDATE() - 5)
AND deneop IN ('003','DUP')

returns 3349 records (1397 + 1952).

Query #5:
SELECT claimno,[lineno],pidate,deneop FROM claimlin
WHERE paiddate BETWEEN (GETDATE() - 11) AND (GETDATE() - 5)
AND deneop NOT IN ('003','DUP')

returns only 3319 records.


Shouldn't Query #5 return 46113 records (49462-3349)??? Shouldn't that be logical when using IN...() and NOT IN() ??

Can someone offer an explanation or something I should look for in the data that would cause this??

Thanks in advance.
DMill

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[cLFlaVA]

Try putting parentheses around your BETWEEN statement...

[tt]
SELECT
claimno,[lineno],pidate,deneop FROM claimlin
WHERE
(paiddate BETWEEN (GETDATE() - 11) AND (GETDATE() - 5))
AND deneop NOT IN ('003','DUP')
[/tt]

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[NWChowd]

I figured out the problem. the deneop has numerous NULL values, which throw off the clause "AND deneop NOT IN ('003,'DUP')." I had to modify the clause to read:

"AND COALESCE(deneop,'') NOT IN ('003','DUP')"

and the results came out as expected.

thanks,
DMill

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================