Post reply

Quick question regarding CASE WHEN statement

  • December 9, 2014 at 10:10 am

    #312969

    I'm trying to use CASE WHEN to group insurance data. I need three groups, Medicare, Medicaid, and AllPayers. AllPayers should include Medicare and Medicaid. If I group them using CASE WHEN, the groups become exclusive. In other words AllPayers does not include Medicare and Medicaid.

    Here's my code. Any thoughts?

    select case when c.InsuranceID is not null then 'AllPayers'

    when c.InsuranceID='MCD' then 'Medicaid'

    when c.InsuranceID='MCR' then 'Medicare'

    end as 'InsuranceGroup'

    from dbo.TEST

    create table dbo.TEST

    (

    VisitID varchar(25),

    InsuranceID varchar(25),

    DischargeDisp varchar(25)

    )

    go

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749414100','MCR','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749437589','UNICARE','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749451966','BHMOBG','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749452061','THPMCRBG','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749456573','BHMOBG','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749456788','MCR','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749456805','TUFPPO','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749456829','BCHMOMCRBG','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749457137','MCR','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749457619','MCR','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749457646','NETWRKHP','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749457662','FALLONHP','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749458600','BCMA','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749458784','MCR','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749459431','NETWRKCC','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749461262','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749462250','MCR','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749463034','SRWHLTH','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749463633','THPMCRBG','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749463857','NHPMCD','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749464381','BCHMOMCRBG','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749465633','MCR','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749465957','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749466066','TUFBG','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749466495','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749466615','MCR','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749467633','UCFFCP','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749468289','BCMA','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749468915','NHPMCD','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749468926','HLTHNET','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749470221','PHP','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749470317','UCFFCP','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749470577','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749470870','MCR','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749471380','UCFFCP','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749471608','ZZMBHP','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749472180','NHPMCD','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749473605','BHMOMCR','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749473673','ZZMBHP','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749473797','MCR','DischargedOther')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749473851','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749474153','MCDPCC','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749474168','BHMOBG','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749474366','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749474456','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749474472','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749474760','BCMA','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749474812','MCR','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749475075','MCDPCC','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749475086','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749475173','MCR','DischargedOther')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749475179','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749475196','MCDPCC','DischargedOther')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749475197','BCELECT','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749475207','BCHMOMCRBG','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749475228','THPMCRBG','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749475242','MCR','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749475258','UCFFCP','DischargedOther')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749475261','HLTHNET','DischargedOther')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749475264','BCMA','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749375477','MCR','DischargedOther')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749407060','MCR','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749435730','MCR','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749441523','ZZMBHP','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749447848','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749449623','BHMOBG','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749451583','THPMCRBG','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749454174','SRWHLTH','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749455056','TUFBG','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749458628','NHPCC','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749459496','MCR','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749460410','MCDPCCBG','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749461079','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749461502','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749462284','MCR','DischargedOther')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749462388','HLTHNCC','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749463639','MCR','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749464346','MCR','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749465293','BCELECT','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749466592','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749466654','MCR','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749468118','HLTHNET','DischargedOther')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749468178','NETWRKCC','DischargedOther')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749469058','CCA','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749469563','ZZMBHP','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749470086','BCFEDPPO','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749470155','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749470164','MCR','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749470289','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749470550','MCR','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749470822','THPMCR','DischargedOther')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749471321','TUFBG','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749471679','MCR','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749471808','THPMCR','DischargedToHomeCare')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749472085','NHPMCD','DischargedOther')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749472108','PHPBG','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749472612','UCFFCP','DischargedHome')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749472884','MCR','DischargedToSNF')

    insert into TEST(VisitID, InsuranceID, DischargeDisp) values( '9749473752','BCELECT','DischargedHome')

  • December 9, 2014 at 10:24 am

    #1763603

    You just need to change the order in your conditions.

    SELECT CASE

    WHEN C.Insuranceid = 'MCD' THEN 'Medicaid'

    WHEN C.Insuranceid = 'MCR' THEN 'Medicare'

    WHEN C.Insuranceid IS NOT NULL THEN 'AllPayers'

    END AS 'InsuranceGroup'

    FROM Dbo.Test C;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 9, 2014 at 10:43 am

    #1763607

    Thanx.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply