August 2, 2011 at 4:13 pm
I have a procedure that groups claims by their denial codes and returns the percent of the total for each code, instead of the number. It runs fine but I think the code could be improved. Here's some code to show what I am looking for.
CREATE TABLE #DenialCodes
(DenialID int,
DenialCode varchar(10))
INSERT INTO #DenialCodes
SELECT 1, 'DC01' UNION ALL
SELECT 2, 'DC01' UNION ALL
SELECT 3, 'DC02' UNION ALL
SELECT 4, 'DC03' UNION ALL
SELECT 5, 'DC03' UNION ALL
SELECT 6, 'DC03' UNION ALL
SELECT 7, 'DC04' UNION ALL
SELECT 8, 'DC04' UNION ALL
SELECT 9, 'DC05' UNION ALL
SELECT 10, 'DC06'
The below produces the results I want but I'm sure there's a way to do this without hitting the table twice.
DECLARE @totalrows DECIMAL(5,2)
SET @totalrows = (SELECT COUNT(*) FROM #DenialCodes)
SELECT DenialCode, CAST(COUNT(DenialCode) / @totalrows as DECIMAL(5,2)) * 100 Percentage
FROM #DenialCodes
GROUP BY DenialCode
/** DESIRED RESULTS
DenialCode Percentage
---------- -----------
DC01 20.00
DC02 10.00
DC03 30.00
DC04 20.00
DC05 10.00
DC06 10.00
**/
What am I missing? :crazy: Thank you.
August 2, 2011 at 7:01 pm
Study up on Windowed Functions, they can be very beneficial. 🙂
Using your sample data ...
SELECT DISTINCT
DenialCode
,CAST(
COUNT(*) OVER (PARTITION BY DenialCode)
/ (COUNT(*) OVER () * 1.)
as DECIMAL(5,2)) * 100 Percentage
FROM #DenialCodes
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgAugust 2, 2011 at 7:27 pm
hwells (8/2/2011)
The below produces the results I want but I'm sure there's a way to do this without hitting the table twice.
Presumably the actual table is very much larger than just 10 rows - if not why worry about hitting it twice?
It's easy to avoid hitting the original table (#DenialCodes) twice. Here's a very simple way of doing it but probably not the most efficient way.
Construct an intermediate table by
select DenialCode, count(*) as Cnt into #CodeCounts from #DenialCodes
first. That's one scan of the original table.
Then the total rows can be calculated using
select sum(Cnt) from #CodeCounts
so it doesn't hit the original table, and there's no need to hit the original table again as teh intermediate table already has the counts for the individual codes.
Tom
August 3, 2011 at 9:08 am
Jason, thanks much for the help. I had tried something similar but didn't remember to remove the GROUP BY. Stupid me.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply