[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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!
0
karen1974
Asked:
karen1974
1 Solution
 
BusyMamaCommented:
If you remove the submeasure and dos columns I believe you will get the results you are looking for.
0
 
QlemoC++ DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now