Need help on summarizing data into categories

Posted on 2011-10-20
Last Modified: 2012-05-12
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!
Question by:karen1974
    LVL 7

    Accepted Solution

    If you remove the submeasure and dos columns I believe you will get the results you are looking for.
    LVL 67

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Suggested Solutions

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now