Need help on summarizing data into categories
Posted on 2011-10-20
Some background on issue: members may be non-compliant (nc) on ANY of 4 measures defined, so the combos may be nc on all 4/4, 3/4, 2/4 or 1/4.
When the member has a compliant measure, the date of service (DOS) needs to be populated (which I've got a query currently doing from claims data).
So for example, I currently have a temp table from similar coding to
select member, submeasureiddesc,denominator,numerator, max(dos) dos
nsum=case when numerator='Y' then 1 else 0 end,
dsum=case when denominator='Y' then 1 else 0 end
from #data d
group by member, mem_id, submeasure, dos, numerator, denominator
order by member, mem_id, submeasure, dos, numerator, denominator
which shows a fictitious member ID and data this way:
memberID Submeasure dos Num Denom
001 Eye 10/2 1 1 -- where 0 is nc (test not done) and 1 is compliant (test done so DOS shown)
001 Nose 0 1
001 Teeth 10/14 1 1
001 Arm 0 1
What I'm wrestling with is I want to see at a glance all the memberIDs collapsed by count to see who is non-compliant on which # out of 4 measures.
I should see at a glance that member 001 is compliant for 2/4 measures by summing the numbers. For some reason though, I'm having issues getting the # out of 4 for the Num column and Denom column.
Instead, I'm tinkering with code like below:
Submeasure, --member, mem_id,
count(dsum) as Denom, count(nsum) as compliant, --dos,
100*cast(cast(sum(nsum) as float)/cast(sum(dsum) as float) as float) Rate
--WHERE Submeasure IN('LDL-C Screening (CDC)','HbA1c Testing','Medical Attention for Nephropathy','Eye Exam')
--and nsum = 0 and dsum = 1
group by member, mem_id, submeasure, dos, nsum
order by member, mem_id, submeasure, dos, nsum
which tells me that member 001, for example, shows
memberID Submeasure dos Num Denom Rate
001 Eye 10/2 1 1 = 100%
001 Nose 0 1 = 50%
001 Teeth 10/14 1 1 = 100%
001 Arm 0 1 = 50%
but this isn't helping me get to a place where I can without manual manipulation just put each member into a category based on 4/4, 3/4, 2/4 or 1/4.
Any help is much appreciated!