Link to home
Start Free TrialLog in
Avatar of karen1974
karen1974Flag for United States of America

asked on

Need help on summarizing data into categories

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
into #update
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:

SELECT  
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
--into #temp_rate
from #update
--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!
ASKER CERTIFIED SOLUTION
Avatar of BusyMama
BusyMama
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Qlemo
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.