SQL Server returning bogus results
Posted on 2010-01-04
I have a table with a name, type and timestamp columns. There are something like 300M rows in this table. if I run the following query I get 0.
select count(distnict name) from myTable where [timestamp] > 'November 1, 2009' and [timestamp] < 'December 1, 2009' and type = 1 and name not in (select distinct name from myTable where [timestamp] > 'September 1, 2009' and [timestamp] < 'November 1, 2009' and type = 1)
Basically I'm looking for all names that occured in november, but where not in the previous 2 months.
If you run these seperatly you end up with 800K for the first query (no in) and about 20M for the in statement. If I select into another table I'll end up with 300K duplicates meaning about 500K of the first query are new names.
What am I doing wrong? Overloading 'in' clause?