November 20, 2015 at 1:55 pm
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
November 20, 2015 at 2:09 pm
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.
November 20, 2015 at 2:15 pm
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
November 20, 2015 at 4:25 pm
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