Distinct Count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Distinct Count

Author

Topic

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-12-19 : 13:38:40

Hi There,
I need to count just the number of claims using this query and not the number of detail lines which is why I am using distinct. This causes the query to run much longer. Any suggestions on how to speed this up?



select 
		a.proc_date, 
		a.tax_id,
		b.full_name as provider_name,
		Sum(c.Charge) Amount_Billed,
		Sum(c.price) Amount_Repriced,
		
		Count(distinct a.claim) Total_Count,
		'BUILD_STBL_CLAIMS_BY_PROVIDER', --sys_source
		getdate() --sys_created
		
	from  HMPDW_staging.dbo.master a
	join hmpdw_staging.dbo.detail c on a.claim = c.claim and a.version = c.version
and a.source = c.source
	left join HMPDW_Staging.dbo.provider b on a.prov_num = b.prov_num
	where proc_date >= dateadd(year, -2, getdate())
	group by a.proc_date, a.tax_id, b.full_name