[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

SQL Server returning bogus results

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?
0
ggrm
Asked:
ggrm
1 Solution
 
chapmandewCommented:
is your timestamp column a datetime column or an actual timestamp datatype?
0
 
zadeveloperCommented:


select count(distnict name) 
from 
myTable 
where 
[timestamp] between cast('1 November 2009 00:00:00' as datetime) and cast('1 December 2009 23:59:59' as DatTime) 
and type = 1 
and name not in (select distinct name from myTable where [timestamp] between cast('1 September 2009 00:00:00' as datetime) and cast('1 November 2009 23:59:59' as DatTime) and [type] = 1)

Open in new window

0
 
ggrmAuthor Commented:
Its a datetime column.
zadeveloper testing your suggestion now......
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
ralmadaCommented:
There's no need to cast to datetime there. Also be careful how you apply your filters. Your original query is excluding November 1, 2009. (same with the subquery it's excluding September 1) and the suggestion of zadeveloper is including December 1, 2009. Check this:
select count(distinct name) 
from myTable 
where 	[timestamp] >= '2009-11-01' and 
	[timestamp] < '2009-12-01' and 
	type = 1 and 
	name not in (select distinct name from myTable 
			where 	[timestamp] >= '2009-09-01' and 
				[timestamp] < '2009-11-01' and 
				type = 1)

Open in new window

0
 
jamesguCommented:
please run query 1 see what do you get, if you still get 0,

run query 2 and 3 see what do you get from them
-- query 1
select count(distinct name) from myTable a 
where [timestamp] >= 'November 1, 2009' and [timestamp] < 'December 1, 2009' and type = 1 and 
not exists (select 1 from myTable b 
where b.[timestamp] >= 'September 1, 2009' 
and b.[timestamp] < 'November 1, 2009' 
and b.type = 1
and a.name = b.name)

-- query 2
select count(*) from myTable a 
where [timestamp] >= 'November 1, 2009' and [timestamp] < 'December 1, 2009' and type = 1 and 
exists (select 1 from myTable b 
where b.[timestamp] >= 'September 1, 2009' 
and b.[timestamp] < 'November 1, 2009' 
and b.type = 1
and a.name = b.name)

-- query 3
select count(*) from myTable a 
where [timestamp] >= 'November 1, 2009' and [timestamp] < 'December 1, 2009' and type = 1

Open in new window

0
 
ralmadaCommented:
ggrm,
Can you please advise why you selected that comment as the answer?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now