Top 500 Codes with Detail - 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

General SQL Server Forums

New to SQL Server Programming

Top 500 Codes with Detail

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2012-01-27 : 11:08:55

I have a table that I need to find the top 500 used CPT Codes, Which I know how to do.

Which is this...

SELECT TOP (500)
clms_proc, COUNT(clms_proc) AS 'cptcount'
From dbo.ConsiliumClaims c
Join impact.dbo.clms on clms_id = clm_id1
Where CLM_DOUT between '1/1/2010' and '12/31/2011' and negdup is null and iserror <> 1
GROUP BY clms_proc
ORDER BY 'cptcount' DESC

Now I want to take those 500 and add them details (total charges, client, etc....

This is what I have for that...

Select* from (Select Distinct
clm_id1,clm_65a as Client, TIN, clm_tchg as TotalCharges, clm_sppo as TotalSavings, accessfeefinal,[Closed Status],clm_net as Network,

clmsID,CPTCODE,claimLine, medicare

from dbo.ConsiliumClaims

Left Join
(Select clms_id as clmsID, clms_proc as CPTCODE, clms_line as claimLine, clms_uac as medicare from impact.dbo.clms )cpt on cpt.clmsID = clm_id1
Where CLM_DOUT between '1/1/2010' and '12/31/2011' and negdup is null and iserror <> 1) Table1

How can I link the first query with this or am I going about doing this the wrong way.

My main goal is to find the top utilized CPTCODES and them later sum the total charges with the client name aside them.

Thanks for you help.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-01-27 : 11:28:39

Use it as a derived table and inner join to it on the clms_proc field.



select
...
FROM
(...) Table1
INNER JOIN
(
SELECT TOP (500)
clms_proc, COUNT(clms_proc) AS 'cptcount'
From dbo.ConsiliumClaims c 
Join impact.dbo.clms on clms_id = clm_id1
Where CLM_DOUT between '1/1/2010' and '12/31/2011' and negdup is null and iserror <> 1
GROUP BY clms_proc
ORDER BY 'cptcount' DESC
) top500
on
Table1.clms_proc = top500.clms_proc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-27 : 11:36:08

what if count values are same for lot of codes? do you need to accomodate all of them or only stop at first 500?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-27 : 12:22:19

Not sure...need table DDL and sample data...and expected results




SELECT * FROM DetailTable
WHERE clms_ID IN (
		 SELECT TOP 500 clms_ID
		   FROM dbo.ConsiliumClaims c 
	 INNER JOIN impact.dbo.clms 
			 ON clms_id = clm_id1
		  WHERE CLM_DOUT BETWEEN '1/1/2010' AND '12/31/2011' 
			AND negdup IS NULL 
			AND iserror <> 1
	   GROUP BY clms_proc
	   ORDER BY COUNT(*) DESC)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2012-01-30 : 09:31:30

I have to make sure that I am pulling the most used Codes. It's okay that they are the same codes. I hope that anwers your question.

quote:
Originally posted by visakh16

what if count values are same for lot of codes? do you need to accomodate all of them or only stop at first 500?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2012-01-30 : 09:33:08

Thank you rick. This worked perfectly!

quote:

Originally posted by RickD

Use it as a derived table and inner join to it on the clms_proc field.



select
...
FROM
(...) Table1
INNER JOIN
(
SELECT TOP (500)
clms_proc, COUNT(clms_proc) AS 'cptcount'
From dbo.ConsiliumClaims c 
Join impact.dbo.clms on clms_id = clm_id1
Where CLM_DOUT between '1/1/2010' and '12/31/2011' and negdup is null and iserror <> 1
GROUP BY clms_proc
ORDER BY 'cptcount' DESC
) top500
on
Table1.clms_proc = top500.clms_proc

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-30 : 13:06:52

"Rick..Rick...you must hide me.."

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-01-31 : 03:49:49

quote:
Originally posted by X002548

"Rick..Rick...you must hide me.."

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

What, for using SELECT *..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources