Help with a case statement

  • Ok, below is the case statement that is in my query. Works just fine. But, now I found out that the patient has to sign a Hippa consent form for the values to show on the report:

    ,case when Dyn_Assessments.field_name = '*Previous Treatment (Medication(s) patient has previously received for same or co-morbid diagnosis - from referral forms):' then Dyn_Assessments.answer else '' end Previous_Text2

    so, I tried to set it up to display when the consent form field is yes...but the results are blank...even for those that I know checked yes. But, I can't tell what I'm doing wrong.

    case when Dyn_Assessments.field_name = '*Was patient-signed manufacturer HIPAA consent received?' and Dyn_Assessments.answer = 'yes'

    then (case when Dyn_Assessments.field_name = '*Previous Treatment (Medication(s) patient has previously received for same or co-morbid diagnosis - from referral forms):'

    then Dyn_Assessments.answer

    else '' end

    ) else '' end Previous_Text

  • cory.bullard76 (11/20/2015)


    Ok, below is the case statement that is in my query. Works just fine. But, now I found out that the patient has to sign a Hippa consent form for the values to show on the report:

    ,case when Dyn_Assessments.field_name = '*Previous Treatment (Medication(s) patient has previously received for same or co-morbid diagnosis - from referral forms):' then Dyn_Assessments.answer else '' end Previous_Text2

    so, I tried to set it up to display when the consent form field is yes...but the results are blank...even for those that I know checked yes. But, I can't tell what I'm doing wrong.

    case when Dyn_Assessments.field_name = '*Was patient-signed manufacturer HIPAA consent received?' and Dyn_Assessments.answer = 'yes'

    then (case when Dyn_Assessments.field_name = '*Previous Treatment (Medication(s) patient has previously received for same or co-morbid diagnosis - from referral forms):'

    then Dyn_Assessments.answer

    else '' end

    ) else '' end Previous_Text

    Can't tell from the snippet either. If you can mock up a sandbox version of the table(s) involved, some sample data (not real production data), and expected results based on the sample data we may be able to provide better answers.

    Just looking at the snippet I'd wonder what is the value of Dyn_assessments.answer that is being evealuated? Just from the code I'd suspect it is the same value in both WHEN clauses, not the answer to each question when evaluated.

  • You're comparing the same value (Dyn_Assessments.field_name) twice and expecting it to have different values, which won't happen because it compares row by row.

    I'm not sure about the rules to work with your table, but you might need something like this:

    case when Dyn_Assessments.field_name = '*Previous Treatment (Medication(s) patient has previously received for same or co-morbid diagnosis - from referral forms):'

    AND EXISTS (SELECT *

    FROM Dyn_Assessments i

    WHERE i.field_name = '*Was patient-signed manufacturer HIPAA consent received?'

    and i.answer = 'yes'

    AND i.SomeKey = Dyn_Assessments.SomeKey)

    then Dyn_Assessments.answer

    else '' end Previous_Text2

    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
  • Luis....your suggestion worked perfect when I inserted that case statement (Aliased 'Previous_Text5) into my test query:

    SELECT case when Dyn_Assessments.field_name = '*Previous Treatment (Medication(s) patient has previously received for same or co-morbid diagnosis - from referral forms):'

    AND EXISTS (SELECT *

    FROM Dyn_Assessments i

    WHERE i.field_name = '*Was patient-signed manufacturer HIPAA consent received?'

    and i.answer = 'yes'

    AND i.mrn = Dyn_Assessments.mrn)

    then Dyn_Assessments.answer

    else '' end Previous_Text5

    ,hr.mrn

    --,Dyn_Assessments.*

    FROM [CPRSQL].[dbo].[Dyn_Assessments] join hr on Dyn_Assessments.mrn = hr.mrn

    where hr.mrn = 007582 --field_name = '*If consent form was received, please indicate the date it was received:'

    BUT, when I add it into the query that I'm using for the report, I get the following message:

    Msg 130, Level 15, State 1, Line 83

    Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

    If I take the max off it tells me to add the fields in the case to the group by and then my record count goes from 34 to 224....here is that query. Its the last part of the select where the case is located:

    select *

    from(

    select

    /*case when labels.siteno in (1,2,3,4) then 'Amber'

    when labels.siteno = 5 then 'HPS'

    end Pharmacy*/

    client.npi Pharmacy_NPI

    ,client.clientname Pharmacy

    ,inscomp.pharmno

    ,max(case when Assessments_View_Derived.field_name = '*Referral Source:' then Assessments_View_Derived.answer else '' end) Refsource

    ,refsourc.org Alt_RefSource

    ,max(case when Assessments_View_Derived.field_name = '*Xtandi Patient HUB ID (Required if known to SP. Not anticipated on records received on the first day of patient enrollment.):' then Assessments_View_Derived.answer else '' end) ProgramID

    ,[cprsql].dbo.HR.MRN Pharmacy_ID

    --,[cprsql].dbo.HR.[LAST_NAME] Last_Name

    ,max(case when Assessments_View_Derived.field_name = '*Was patient-signed manufacturer HIPAA consent received?' then (case when Assessments_View_Derived.answer = 'yes' then HR.[LAST_NAME] end)end) Last_Name

    ,max(case when Assessments_View_Derived.field_name = '*Was patient-signed manufacturer HIPAA consent received?' then (case when Assessments_View_Derived.answer = 'yes' then HR.[FIRST_NAME] end)end) First_Name

    --,[cprsql].dbo.HR.[FIRST_NAME] First_Name

    ,max(case when Assessments_View_Derived.field_name = '*Was patient-signed manufacturer HIPAA consent received?' then (case when Assessments_View_Derived.answer = 'yes' then CONVERT(VARCHAR(10),hr.dob, 101) else CONVERT(VARCHAR(10),year(hr.dob))end)end) DOB

    --,CONVERT(VARCHAR(10),hr.dob, 101) DOBs

    ,max(case when Assessments_View_Derived.field_name = '*Was patient-signed manufacturer HIPAA consent received?' then (case when Assessments_View_Derived.answer = 'yes' then hr.sex end)end) Gender

    --,hr.sex Sex

    ,max(case when Assessments_View_Derived.field_name = '*Was patient-signed manufacturer HIPAA consent received?' then (case when Assessments_View_Derived.answer = 'yes' then hr.address end)end) Patient_Address1

    --,hr.address Patient_Address1

    ,max(case when Assessments_View_Derived.field_name = '*Was patient-signed manufacturer HIPAA consent received?' then (case when Assessments_View_Derived.answer = 'yes' then hr.address2 end)end) Patient_Address2

    --,hr.address2 Patient_Address2

    ,max(case when Assessments_View_Derived.field_name = '*Was patient-signed manufacturer HIPAA consent received?' then (case when Assessments_View_Derived.answer = 'yes' then hr.city end)end) Patient_City

    --,hr.city Patient_City

    ,hr.state Patient_State

    ,max(case when Assessments_View_Derived.field_name = '*Was patient-signed manufacturer HIPAA consent received?' then (case when Assessments_View_Derived.answer = 'yes' then hr.zip end)end) Patient_Zip

    --,hr.zip Patient_Zip

    ,max(case when Assessments_View_Derived.field_name = '*If consent form was received, please indicate the date it was received:' then (case when Assessments_View_Derived.answer = '' then '' else hr.phone end)end) Patient_phone

    --,hr.phone Patient_Phone

    ,max(case when Assessments_View_Derived.field_name = '*Was patient-signed manufacturer HIPAA consent received?' then (case when Assessments_View_Derived.answer = 'yes' and icd10_codes.rank = 1 then dbo.f_Icd_ConvertCode(icd10_codes.Code) end)end) DXCode1

    --,dbo.f_Icd_ConvertCode(icd10_codes.Code) ICD10DiagnosisCode

    ,max(case when Assessments_View_Derived.field_name = '*Was patient-signed manufacturer HIPAA consent received?' then (case when Assessments_View_Derived.answer = 'yes' and icd10_codes.rank = 1 then icd10_codes.description end)end) DXCode1_Desc

    ,max(case when Assessments_View_Derived.field_name = '*Was patient-signed manufacturer HIPAA consent received?' then (case when Assessments_View_Derived.answer = 'yes' and icd10_codes.rank = 2 then dbo.f_Icd_ConvertCode(icd10_codes.Code) end)end) DXCode2

    ,max(case when Assessments_View_Derived.field_name = '*Was patient-signed manufacturer HIPAA consent received?' then (case when Assessments_View_Derived.answer = 'yes' and icd10_codes.rank = 2 then icd10_codes.description end)end) DXCode2_Desc

    --,icd10_codes.description ICD9_Desc

    ,labels.doc Prescriber

    ,doctors.ph_npi Doc_NPI

    ,doctors.ph_dea Doctors_DEA

    ,doctors.ph_address Doctor_Address

    ,doctors.ph_addr2 Doctor_Address2

    ,doctors.ph_city Doctor_City

    ,doctors.ph_state Doctor_State

    ,doctors.ph_zip Doctor_Zip

    ,doctors.ph_phone Doctor_Phone

    ,doctors.ph_fax Doctor_Fax

    ,CONVERT(VARCHAR(10),labels.orig_rx, 101) Presription_Start_Date

    ,labels.scriptext RX_Number

    ,LABELS.QTYALLOWED #_of_Refills

    ,(convert(int,NCPDP.REFILLSAUT) - convert(int,NCPDP.NEWREFILL)) RefillsRemaining

    ,hr.pat_stat Status_Code

    ,NCPDP.ndc NDC

    ,NCPDP.drugname Drug

    ,NCPDP.qtydisp Quantity

    ,parts.strengthu UnitMeasure

    ,NCPDP.dayssupply

    ,CONVERT(VARCHAR(10),lablog.CURDATE, 101) DateFilled

    ,'5:00PM' as ShipTime

    ,max(case when patins.rank = 1.0 then patins.payor else '' end) Primary_Payor

    ,max(case when patins.rank = 1.0 then INSCOMP.ORG else '' end) Primary_Insurance

    ,max(case when patins.rank = 2.0 then patins.payor else '' end) Secondary_Payor

    ,max(case when patins.rank = 2.0 then INSCOMP.ORG else '' end) Secondary_Insurance

    ,'' as Deductible

    ,'' as MaxOOP

    ,max(case when claimno = 2 then claims.EXPECTED end) PayCoPay

    ,max(case when Assessments_View_Derived.field_name = '*Previous Treatment (Medication(s) patient has previously received for same or co-morbid diagnosis - from referral forms):' then Assessments_View_Derived.answer else '' end) Previous_Text

    ,max(case when Assessments_View_Derived.field_name = '*Concurrent Treatment (Medication(s) patient is currently receiving for same or co-morbid diagnosis - from referral forms):' then Assessments_View_Derived.answer else '' end) Concurrent_Text

    ,max(case when Assessments_View_Derived.field_name = '*If consent form was received, please indicate the date it was received:' then Assessments_View_Derived.answer else '' end) Consent_Date

    ,max(case when Assessments_View_Derived.field_name = 'Xtandi Patient Status Code:' then Assessments_View_Derived.answer else '' end) Patient_Status_Code

    ,max(case when Assessments_View_Derived.field_name = 'Provide the name of the pharmacy or "Other Location" where the prescription information was transferred to:' then Assessments_View_Derived.answer else '' end) XferPharmName

    ,max(case when Assessments_View_Derived.field_name = 'Provide the NPI of the location that the prescription information was transferred / triaged to:' then Assessments_View_Derived.answer else '' end) XferPharmNPI

    ,max(case when Assessments_View_Derived.field_name = 'Date prescription information was transferred / triaged out:' then Assessments_View_Derived.answer else '' end) XferDate

    ,max(case when Assessments_View_Derived.field_name = '*Previous Treatment (Medication(s) patient has previously received for same or co-morbid diagnosis - from referral forms):'

    AND EXISTS (SELECT *

    FROM Dyn_Assessments i

    WHERE i.field_name = '*Was patient-signed manufacturer HIPAA consent received?'

    and i.answer = 'yes'

    AND i.mrn = Assessments_View_Derived.mrn)

    then Assessments_View_Derived.answer

    else '' end) Previous_Text5

    from [CPRSQL].dbo.INSCOMP

    join [CPRSQL].dbo.CLAIMS

    on [CPRSQL].dbo.INSCOMP.[NO] = [CPRSQL].dbo.CLAIMS.[CFK_INSCOMP]

    join [cprsql].dbo.HR

    on [CPRSQL].dbo.CLAIMS.CFK_HR = [CPRSQL].dbo.HR.[MRN]

    join NCPDP

    ON NCPDP.mrn = hr.mrn and NCPDP.BILLNO = claims.CFK_INVOICES

    join parts

    on parts.name_ = NCPDP.drugname

    join patins

    on PATINS.CPK_PATINS = claims.CFK_PATINS

    join labels

    on labels.cpk_labels = NCPDP.cfk_labels

    join doctors

    on doctors.no = labels.docno

    join refsourc

    on refsourc.cpk_refsourc = hr.CFK_REFSOURC

    join icdpatient

    on icdpatient.cfk_hr = hr.mrn

    join dbo.ICDMASTERLIST ICD9

    on ICD9.CPK_ICDMASTERLIST = icdpatient.CFK_ICDMASTERLIST_ICD9

    join dbo.ICDMASTERLIST ICD10

    on ICD10.CPK_ICDMASTERLIST = icdpatient.CFK_ICDMASTERLIST_ICD10

    join invoices

    on invoices.cfk_hr = hr.mrn

    join client

    on client.cpk_client = invoices.cfk_client

    join lablog

    on lablog.CPK_LABLOG = NCPDP.LABLOGNO

    --join Dyn_Assessments on Dyn_Assessments.mrn = hr.mrn

    left join ( select mrn,field_name,answer

    from Dyn_Assessments

    where (field_name like '*Referral Source:' or

    field_name like '*Xtandi Patient HUB ID (Required if known to SP. Not anticipated on records received on the first day of patient enrollment.):' or

    field_name like '*Was patient-signed manufacturer HIPAA consent received?' or

    field_name like '*Previous Treatment (Medication(s) patient has previously received for same or co-morbid diagnosis - from referral forms):' or

    field_name like '*Concurrent Treatment (Medication(s) patient is currently receiving for same or co-morbid diagnosis - from referral forms):' or

    field_name like '*If consent form was received, please indicate the date it was received:' or

    field_name like 'Xtandi Patient Status Code:' or

    field_name like 'Provide the name of the pharmacy or "Other Location" where the prescription information was transferred to:' or

    field_name like 'Provide the NPI of the location that the prescription information was transferred / triaged to:' or

    field_name like 'Date prescription information was transferred / triaged out:' )

    )Assessments_View_Derived on Assessments_View_Derived.mrn = hr.mrn

    join POPUPDATA on POPUPDATA.cpk_popupdata = hr.CFK_POPUPDATA_ENTTEAMS

    join raws

    on raws.name_ = NCPDP.drugname

    left join (

    SELECT CFK_HR as MRN, dbo.f_Icd_ConvertCode(Code) as CODE, DESCRIPTION, RANK--, ICDPATIENT.DELFLAG, ICDMASTERLIST.DELFLAG, ICDPATIENT.RANK--, *

    FROM [ICDPATIENT]

    join dbo.ICDMASTERLIST on ICDMASTERLIST.CPK_ICDMASTERLIST = CFK_ICDMASTERLIST_ICD10

    where

    --RANK = 1

    [ICDPATIENT].DELFLAG <> 1

    and ICDMASTERLIST.DELFLAG <> 1

    ) icd10_codes on icd10_codes.MRN = hr.MRN

    where NCPDP.drugname like '%Xtandi%'

    --and [cprsql].dbo.HR.MRN = 007582

    and claims.CFK_INSCOMP <> 99999

    and [CPRSQL].dbo.INSCOMP.DELFLAG= 0

    and [CPRSQL].dbo.CLAIMS.DELFLAG= 0

    and [cprsql].dbo.HR.DELFLAG = 0

    and NCPDP.OCC = 08

    group by /*case when labels.siteno in (1,2,3,4) then 'Amber'

    when labels.siteno = 5 then 'HPS'

    end*/

    client.clientname

    ,client.npi

    ,inscomp.pharmno

    ,refsourc.org

    ,[cprsql].dbo.HR.MRN

    ,[cprsql].dbo.HR.[FIRST_NAME]

    --,[cprsql].dbo.HR.[LAST_NAME]

    --,CONVERT(VARCHAR(10),hr.dob, 101)

    --,hr.sex

    --,hr.address

    --,hr.address2

    --,hr.city

    ,hr.state

    ,hr.zip

    --,hr.phone

    --,dbo.f_Icd_ConvertCode(icd10_codes.Code)

    --,icd10_codes.description

    ,labels.doc

    ,doctors.ph_npi

    ,doctors.ph_dea

    ,doctors.ph_address

    ,doctors.ph_addr2

    ,doctors.ph_city

    ,doctors.ph_state

    ,doctors.ph_zip

    ,doctors.ph_phone

    ,doctors.ph_fax

    ,CONVERT(VARCHAR(10),labels.orig_rx, 101)

    ,labels.scriptext

    ,LABELS.QTYALLOWED

    ,(convert(int,NCPDP.REFILLSAUT) - convert(int,NCPDP.NEWREFILL))

    ,hr.pat_stat

    ,NCPDP.ndc

    ,NCPDP.drugname

    ,NCPDP.qtydisp

    ,parts.strengthu

    ,NCPDP.dayssupply

    ,CONVERT(VARCHAR(10),lablog.CURDATE, 101)

    ,Assessments_View_Derived.mrn

    ,Assessments_View_Derived.field_name

    ,Assessments_View_Derived.answer

    --order by 5

    ) A

    where A.DateFilled between '08/01/2015'and '08/31/2015'

    --order by 1

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

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