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

  • 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:

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

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