/****** Hyung Paek 7-29-2019 EMBED - pilot data (YNH EMERGENCY ADULT) EHR phenotype main query Pulls data for phenotype positive patients at EMBED study sites who were seen between start date and end date. Start and end dates are set based on @DataSubNumb Required: Data Submission Number Study start date Site IDs and names Opioid results.sql - need component ID Opioid SmartText.sql - need SmartText ID METRIC_ID from ACCESS_LOG_METRIC table - Active Guidelines web integration record accessed FDI record ID Assumptions: - Patient class: table ZC_PAT_CLASS z where z.name like 'emerg%' -- emergency room patient class or z.name like 'inpatient' -- inpatient patient class or z.name like '%psych%' -- pysch admission or observation or z.name like '%observ%' -- observation patient class or z1.name like 'inpa%psych%' -- inpatient psych - True patients: Table Patient p p.PAT_NAME not like 'zz%' and p.PAT_MRN_ID like 'mr%' - Consult team provider name ends with team when s.PROV_NAME like '%team' then 'Consult Team' - Opioid pharm class: tables ZC_PHARM_CLASS p, ZC_PHARM_SUBCLASS ps Where ((p.name like '%Opioid%' and p.name not like '%non-opioid%' and p.name not like '%non opioid%') or (s.NAME like 'naloxone%' or s.NAME like 'naltrexone%')) - Med order status (med.[ORDER_STATUS_C] in (2, 5, 11) or med.[ORDER_STATUS_C] is null) 1-pending, 2-sent, 3-resulted, 4-canceled, 5-completed, 8-suspend, 9-discontinued, 11-dispensed, null=historical - Order interfaced from Automated dispensing system (order_meds) med.ORD_CREATR_USER_ID <> 'EDIADSD' - Note type ([HNO_INFO]) (NOTE_TYPE_NOADD_C = '50' -- patient instruction or IP_NOTE_TYPE_C in ('37','61','62','63','64','65','66','67','68')) -- inpatient patient instruction for OUD note type - Durable medical equipments m.GENERIC_NAME <> 'MISCELLANEOUS MEDICAL SUPPLY MISC' ******/ Declare @Trial_Start_Dt date, @Start_Dt date, @End_Dt date, @time datetime Declare @Timer varchar, @DataSubNumb int, @FDI_ID int, @Metric_ID int Declare @Heathcare_System_Id int --set statistics time on set @Heathcare_System_Id = --10 --<<<----- Heathcare_System_Id set @DataSubNumb = --2 --<<<----- Data Submission Number set @Trial_Start_Dt = --'4/1/2019' --<<<----- Trial start date --set @Start_Dt = '4/1/2019' set @End_Dt = DATEADD(MM, @DataSubNumb, @Trial_Start_Dt) -- Data end date set @FDI_ID = --100036 --<<<----- FDI ID set @Metric_ID = --17302 --<<<----- Metric ID set @Timer='False' if (@Trial_Start_Dt = '') or (@Trial_Start_Dt is null) select 'Trial Start date required' if (@DataSubNumb = '') or (@DataSubNumb is null) select 'Data Submission Number required' if (@Trial_Start_Dt = '') or (@Trial_Start_Dt is null) or (@DataSubNumb = '') or (@DataSubNumb is null) return if (DATEADD(MM, -2, @End_Dt) < @Trial_Start_Dt) set @Start_Dt = @Trial_Start_Dt else set @Start_Dt = DATEADD(MM, -2, @End_Dt) -- Get 2 months of data IF OBJECT_ID('tempdb..#Param') IS NOT NULL DROP TABLE #Param select @Trial_Start_Dt Trial_Start_Dt ,@Start_Dt Start_Dt ,@End_Dt End_Dt ,@time Start_time ,@Metric_ID Metric_ID ,@FDI_ID FDI_ID ,@Heathcare_System_Id Heathcare_System_Id into #Param IF OBJECT_ID('tempdb..#Sites') IS NOT NULL DROP TABLE #Sites create table #Sites (Site_ID int, Dept_Name varchar(50)) ---------- see Data Definition Excel table for the site ID ---------- insert into #Sites (Site_ID, Dept_Name) values (11, 'SRC EMERGENCY DEPARTMENT'), (12, 'BH EMERGENCY DEPARTMENT'), (13, 'LMH EMERGENCY DEPARTMENT'), (14, 'GH EMERGENCY DEPARTMENT'), (90, 'YNH EMERGENCY ADULT') ------------------------------------------------------------------- --select @Trial_Start_Dt Trial_Start_Dt, @Start_Dt Start_Dt, @End_Dt End_Dt select CONCAT('Data submission number: ', @DataSubNumb) select * from #sites IF OBJECT_ID('tempdb..#Visits') IS NOT NULL DROP TABLE #Visits IF OBJECT_ID('tempdb..#ED_visits') IS NOT NULL DROP TABLE #ED_visits IF OBJECT_ID('tempdb..#In_Psyc_adm') IS NOT NULL DROP TABLE #In_Psyc_adm IF OBJECT_ID('tempdb..#AcctList') IS NOT NULL drop table #AcctList IF OBJECT_ID('tempdb..#OUDmed') IS NOT NULL DROP TABLE #OUDmed IF OBJECT_ID('tempdb..#PB') IS NOT NULL drop table #PB IF OBJECT_ID('tempdb..#FinalDx') IS NOT NULL DROP TABLe #FinalDx IF OBJECT_ID('tempdb..#ETOH_DX') IS NOT NULL DROP TABLE #ETOH_DX IF OBJECT_ID('tempdb..#Benzo_DX') IS NOT NULL DROP TABLE #Benzo_DX IF OBJECT_ID('tempdb..#PL_OUD_Dx') IS NOT NULL DROP TABLE #PL_OUD_Dx IF OBJECT_ID('tempdb..#CS_med') IS NOT NULL DROP TABLE #CS_med IF OBJECT_ID('tempdb..#PtCSmed') IS NOT NULL DROP TABLE #PtCSmed IF OBJECT_ID('tempdb..#PtRxNaloxone') IS NOT NULL DROP TABLE #PtRxNaloxone IF OBJECT_ID('tempdb..#PL_OUD_Tx') IS NOT NULL DROP TABLE #PL_OUD_Tx IF OBJECT_ID('tempdb..#PMH') IS NOT NULL DROP TABLE #PMH IF OBJECT_ID('tempdb..#PS') IS NOT NULL DROP TABLE #PS IF OBJECT_ID('tempdb..#PSH') IS NOT NULL DROP TABLE #PSH IF OBJECT_ID('tempdb..#CC') IS NOT NULL DROP TABLE #CC IF OBJECT_ID('tempdb..#CC_ETOH') IS NOT NULL DROP TABLE #CC_ETOH IF OBJECT_ID('tempdb..#CC_opioid') IS NOT NULL DROP TABLE #CC_opioid IF OBJECT_ID('tempdb..#CC_Narcan') IS NOT NULL DROP TABLE #CC_Narcan IF OBJECT_ID('tempdb..#Nx_med') IS NOT NULL DROP TABLE #Nx_med IF OBJECT_ID('tempdb..#smrt') IS NOT NULL DROP TABLE #smrt IF OBJECT_ID('tempdb..#OpiateResults') IS NOT NULL DROP TABLE #OpiateResults IF OBJECT_ID('tempdb..#PosLabs') IS NOT NULL DROP TABLE #PosLabs IF OBJECT_ID('tempdb..#PtInsNote') IS NOT NULL DROP TABLE #PtInsNote IF OBJECT_ID('tempdb..#Comb') IS NOT NULL DROP TABLE #Comb IF OBJECT_ID('tempdb..#PTAMeds') IS NOT NULL DROP TABLE #PTAMeds IF OBJECT_ID('tempdb..#PTAMedsMin') IS NOT NULL DROP TABLE #PTAMedsMin IF OBJECT_ID('tempdb..#RND') IS NOT NULL DROP TABLE #RND IF OBJECT_ID('tempdb..#FDI') IS NOT NULL DROP TABLE #FDI IF OBJECT_ID('tempdb..#web') IS NOT NULL DROP TABLE #web set @time = getdate() IF @Timer='T' select concat('Query started at ', @time) -- all ED, obs, psych patients -- -- IF OBJECT_ID('tempdb..#Visits') IS NOT NULL DROP TABLE #Visits SELECT distinct hsp.[PAT_ID] ,hsp.[PAT_ENC_CSN_ID] ,hsp.ADT_ARRIVAL_TIME ,hsp.HOSP_DISCH_TIME ,hsp.ED_DEPARTURE_TIME ,DATEDIFF(mi,hsp.ADT_ARRIVAL_TIME, hsp.HOSP_DISCH_TIME) Visit_len_Min ,zpc.NAME Pat_class ,hsp.BILL_ATTEND_PROV_ID ,hsp.[DEPARTMENT_ID] --most recent department ,dep.DEPARTMENT_NAME ,hsp.[ADT_PAT_CLASS_C] ,case when e.SUM_BLK_TYPE_ID = '2' then 1 else '0' end Pregnant into #Visits FROM [Clarity]..[V_PAT_ENC_HSP] hsp join ( SELECT distinct [PAT_ID],[PAT_ENC_CSN_ID] -- all contacts between start and end date FROM [Clarity].[dbo].[PAT_ENC_ED_AREA] where [CONTACT_DATE] >= @Start_Dt and [CONTACT_DATE] <= @End_Dt ) ed on hsp.PAT_ENC_CSN_ID=ed.PAT_ENC_CSN_ID left join clarity..ZC_PAT_CLASS zpc on hsp.ADT_PAT_CLASS_C=zpc.ADT_PAT_CLASS_C left join [Clarity]..[CLARITY_DEP] dep on hsp.DEPARTMENT_ID = dep.DEPARTMENT_ID left join clarity..PAT_EPISODE pe on hsp.PAT_ID=pe.PAT_ID left join (select * from clarity..episode ep where ep.SUM_BLK_TYPE_ID = 2 -- get episodes and ((ep.END_DATE >= @Start_Dt and ep.END_DATE <= @End_Dt) or ep.END_DATE is null) ) e on pe.EPISODE_ID=e.EPISODE_ID and hsp.ADT_ARRIVAL_TIME > e.START_DATE and ((hsp.ADT_ARRIVAL_TIME < e.END_DATE) or e.END_DATE is null) join -- Inpatient, Emergency, Observation, Observation Psychiatry, inpatient psych (select * from Clarity..ZC_PAT_CLASS z where z.name like 'emerg%' or z.name like 'inpatient' or z.name like '%psych%' or z.name like '%observ%' ) zc on hsp.ADT_PAT_CLASS_C=zc.ADT_PAT_CLASS_C where hsp.ADT_ARRIVAL_TIME >= @Start_Dt and hsp.ADT_ARRIVAL_TIME <= @End_Dt -- find preg patients and remove fake patients -- -- IF OBJECT_ID('tempdb..#ED_visits') IS NOT NULL DROP TABLE #ED_visits select v.PAT_ID ,HASHBYTES('SHA1', a.PAT_MRN_ID) Research_ID ,v.PAT_ENC_CSN_ID ,v.ADT_ARRIVAL_TIME ,v.ED_DEPARTURE_TIME ,datediff(dd, @Trial_Start_Dt, v.ADT_ARRIVAL_TIME) Arrival_DT ,datepart(hh, v.ADT_ARRIVAL_TIME) Arrival_Hr ,datediff(dd, @Trial_Start_Dt, v.HOSP_DISCH_TIME) Discharge_DT ,datediff(yy, a.BIRTH_DATE, v.ADT_ARRIVAL_TIME) - CASE WHEN v.ADT_ARRIVAL_TIME < DATEADD(YEAR, DATEDIFF(YEAR, a.BIRTH_DATE, v.ADT_ARRIVAL_TIME), a.BIRTH_DATE) -- fix prob with datediff in years THEN 1 ELSE 0 END Age ,v.HOSP_DISCH_TIME ,v.Visit_len_Min ,v.DEPARTMENT_NAME ,v.DEPARTMENT_ID ,v.BILL_ATTEND_PROV_ID ,max(v.Pregnant) Pregnant into #ED_visits from (select v1.* from #Visits v1 join (select z1.* from Clarity..ZC_PAT_CLASS z1 where z1.name like 'emerg%' ) z on v1.ADT_PAT_CLASS_C = z.ADT_PAT_CLASS_C where v1.Visit_len_Min <= 24*60 -- only patients who has been in ED for a day ) v JOIN (select p.pat_id, p.PAT_MRN_ID, p.BIRTH_DATE from clarity..patient p where p.PAT_NAME not like 'zz%' and p.PAT_MRN_ID like 'mr%' ) a on v.pat_id=a.pat_id group by v.ADT_ARRIVAL_TIME ,HASHBYTES('SHA1', a.PAT_MRN_ID) ,v.DEPARTMENT_NAME ,v.PAT_ENC_CSN_ID ,v.PAT_ID ,v.HOSP_DISCH_TIME ,v.Visit_len_Min ,v.DEPARTMENT_ID ,v.ED_DEPARTURE_TIME ,v.BILL_ATTEND_PROV_ID ,datediff(dd, @Trial_Start_Dt, v.ADT_ARRIVAL_TIME) ,datediff(dd, @Trial_Start_Dt, v.HOSP_DISCH_TIME) ,datediff(yy, a.BIRTH_DATE, v.ADT_ARRIVAL_TIME) - CASE WHEN v.ADT_ARRIVAL_TIME < DATEADD(YEAR, DATEDIFF(YEAR, a.BIRTH_DATE, v.ADT_ARRIVAL_TIME), a.BIRTH_DATE) THEN 1 ELSE 0 END -- Find Inpatient and Psych admissions -- -- IF OBJECT_ID('tempdb..#In_Psyc_adm') IS NOT NULL DROP TABLE #In_Psyc_adm -- select distinct ed.PAT_ID ,ed.PAT_ENC_CSN_ID ,ed.ADT_ARRIVAL_TIME ,inp.Last_adm Inp_Adm_Date ,psy.Last_adm Psy_Adm_Date ,case when inp.Last_adm is null then -99999 else datediff(dd, @Trial_Start_Dt, inp.Last_adm) end Inp_adm_DT ,case when psy.Last_adm is null then -99999 else datediff(dd, @Trial_Start_Dt, psy.Last_adm) end Psy_adm_DT into #In_Psyc_adm from #ED_visits ed left join (select distinct e.PAT_ID ,e.PAT_ENC_CSN_ID ,max(v.ADT_ARRIVAL_TIME) Last_adm from #ED_visits e join (select distinct v2.* from #Visits v2 join (select z1.* from Clarity..ZC_PAT_CLASS z1 where z1.name like 'Inpatient' ) z on v2.ADT_PAT_CLASS_C = z.ADT_PAT_CLASS_C ) v on v.PAT_ID=e.PAT_ID and e.ADT_ARRIVAL_TIME>v.ADT_ARRIVAL_TIME group by e.PAT_ID, e.PAT_ENC_CSN_ID ) Inp on ed.PAT_ENC_CSN_ID=inp.PAT_ENC_CSN_ID left join (select distinct e1.PAT_ID ,e1.PAT_ENC_CSN_ID ,max(v1.ADT_ARRIVAL_TIME) Last_adm from #ED_visits e1 join (select distinct v1.* from #Visits v1 join (select z1.* from Clarity..ZC_PAT_CLASS z1 where z1.name like 'inpa%psych%' ) z on v1.ADT_PAT_CLASS_C = z.ADT_PAT_CLASS_C ) v1 on v1.PAT_ID=e1.PAT_ID and e1.ADT_ARRIVAL_TIME>v1.ADT_ARRIVAL_TIME group by e1.PAT_ID, e1.PAT_ENC_CSN_ID ) psy on inp.PAT_ENC_CSN_ID=psy.PAT_ENC_CSN_ID IF @Timer='T' Select 'Population finished: ' + cast(datediff(mi, @time, getdate()) as varchar) --------------- ** Insurance information ** ----------------- -- Get Financial class from HB -- -- IF OBJECT_ID('tempdb..#AcctList') IS NOT NULL drop table #AcctList select distinct p.PAT_ID ,p.PAT_ENC_CSN_ID ,p.ADT_ARRIVAL_TIME ,a.ACCT_FIN_CLASS_C ,zc.NAME Fin_class ,p.BILL_ATTEND_PROV_ID into #AcctList from #ED_visits p left join [Clarity]..HSP_ACCOUNT a on a.PRIM_ENC_CSN_ID=p.PAT_ENC_CSN_ID left join Clarity..ZC_FINANCIAL_CLASS zc on a.ACCT_FIN_CLASS_C=zc.FINANCIAL_CLASS -- Get Financial class from PB -- -- multiple insurance for a visit -- -- IF OBJECT_ID('tempdb..#PB') IS NOT NULL drop table #PB select distinct a.* ,PB1.[SELF_PAY_YN] ,zc.NAME Fin_class into #PB from ( SELECT P.[PAT_ID] ,P.PAT_ENC_CSN_ID ,max(PB.PB_VISIT_ID) PB_VISIT_ID FROM #ED_visits p left join [Clarity]..[ARPB_VISITS] PB on pb.PRIM_ENC_CSN_ID=p.PAT_ENC_CSN_ID group by P.[PAT_ID], P.PAT_ENC_CSN_ID ) a left join [Clarity]..[ARPB_VISITS] Pb1 on pb1.PRIM_ENC_CSN_ID=a.PAT_ENC_CSN_ID and pb1.PB_VISIT_ID=a.PB_VISIT_ID left join Clarity..ZC_ACTN_FIN_CLASS zc on pb1.ACCT_FIN_CLASS_C=zc.ACTION_FIN_CLASS IF @Timer='T' Select 'Insurance finished: ' + cast(datediff(mi, @time, getdate()) as varchar) --------- ** Medications ** ------------ -- get all opioid meds and naloxone medication ID -- -- IF OBJECT_ID('tempdb..#CS_med') IS NOT NULL DROP TABLE #CS_med SELECT distinct m.[MEDICATION_ID] ,m.[NAME] med_name ,p.NAME pharm_name ,ps.NAME pharm_subclass ,s.NAME GenericName ,m.[STRENGTH] ,m.[FORM] ,m.[ROUTE] ,m.[CONTROLLED_MED_YN] ,m.[DEA_CLASS_CODE_C] ,d.NAME Dea_name into #CS_med FROM [Clarity].[dbo].[CLARITY_MEDICATION] m --left join Clarity..ZC_THERA_CLASS t on m.THERA_CLASS_C=t.THERA_CLASS_C left join Clarity..ZC_PHARM_CLASS p on m.PHARM_CLASS_C=p.PHARM_CLASS_C left join Clarity..ZC_PHARM_SUBCLASS ps on m.PHARM_SUBCLASS_C=ps.PHARM_SUBCLASS_C left join Clarity..ZC_SIMPLE_GENERIC s on m.SIMPLE_GENERIC_C=s.SIMPLE_GENERIC_C left join Clarity..ZC_DEA_CLASS_CODE d on m.DEA_CLASS_CODE_C=d.DEA_CLASS_CODE_C where (m.RECORD_STATE not in ('deleted','hidden') or m.RECORD_STATE is null) and ((p.name like '%Opioid%' -- find all meds with opioid pharm class and p.name not like '%non-opioid%' and p.name not like '%non opioid%') or (s.NAME like 'naloxone%' or s.NAME like 'naltrexone%')) -- find all patients currently on methadone, suboxone, bup -- IF OBJECT_ID('tempdb..#OUDmed') IS NOT NULL DROP TABLE #OUDmed SELECT distinct med.[ORDER_MED_ID] ,med.[PAT_ID] ,med.PAT_ENC_CSN_ID ,p.Pregnant ,med.[ORDERING_DATE] ,med.DISCON_TIME ,p.ADT_ARRIVAL_TIME ,c.NAME order_class_name ,med.[DESCRIPTION] --,med.[SIG] ,m.STRENGTH ,med.[DOSAGE] ,med.[QUANTITY] ,med.[REFILLS] ,med.[ORDER_INST] --date and time order was placed ,med.[DISPLAY_NAME] ,case when med.DISCON_TIME is null then '2099-01-01 00:00:00.000' else med.DISCON_TIME end DiscontinueDate ,med.[ORDER_STATUS_C] --1-pending, 2-sent, 3-resulted, 4-canceled, 5-completed, 8-suspend, 9-discontinued, 11-dispensed, ,med.[ACT_ORDER_C] --1-active med, 2-active procedure, 3-discont med, 4-canceled proc, 5-completed med, 6-completed proc ,med.ORD_PROV_ID ,med.AUTHRZING_PROV_ID ,med.ORDERING_MODE_C into #OUDmed FROM [Clarity]..[ORDER_MED] med join #ED_visits p on med.PAT_ID=p.PAT_ID and cast(p.ADT_ARRIVAL_TIME as date) >= cast(med.[ORDERING_DATE] as date) and ((cast(p.ADT_ARRIVAL_TIME as date) <= cast(med.DISCON_TIME as date)) or med.DISCON_TIME is null) left join clarity..CLARITY_MEDICATION m on med.MEDICATION_ID=m.MEDICATION_ID left join Clarity..ZC_ORDER_CLASS c on med.ORDER_CLASS_C=c.ORDER_CLASS_C where (med.[ORDER_STATUS_C] in (2, 5, 11) or med.[ORDER_STATUS_C] is null) --1-pending, 2-sent, 3-resulted, 4-canceled, 5-completed, 8-suspend, 9-discontinued, 11-dispensed, null=historical and med.[ACT_ORDER_C] in ('1', '2') and (m.GENERIC_NAME like 'methadone%' or m.GENERIC_NAME like 'suboxone%' or m.GENERIC_NAME like 'buprenorphine%' or m.GENERIC_NAME like 'naltrexone%') and ((cast(p.ADT_ARRIVAL_TIME as date) > cast(med.[ORDERING_DATE] as date) and med.[ORDER_STATUS_C] in (2, 5, 11)) --include meds ordered before ED or (cast(p.ADT_ARRIVAL_TIME as date) >= cast(med.[ORDERING_DATE] as date) and med.[ORDER_STATUS_C] is null)) --include historical meds entered in ED -- find all patients on the controlled substances as an outpatient when seen in ED in the past 90 days -- -- prior to being seen in ED -- IF OBJECT_ID('tempdb..#PtCSmed') IS NOT NULL DROP TABLE #PtCSmed SELECT distinct med.[ORDER_MED_ID] ,med.[PAT_ID] ,med.[PAT_ENC_CSN_ID] ,cast(med.[ORDER_INST] as date) order_inst --date and time order was placed ,med.[DISCON_TIME] ,zc.NAME order_class_name ,med.[DESCRIPTION] ,c.GenericName --,med.[SIG] ,m.STRENGTH ,med.[QUANTITY] ,left(med.quantity,charindex(' ',med.quantity)-1) Quant_Numb ,right(med.QUANTITY,len(med.quantity)-CHARINDEX(' ',med.quantity)) Quant_type ,datediff(dd,med.ORDER_START_TIME, med.ORDER_END_TIME) duration_time ,med.[REFILLS] ,med.[ORDER_STATUS_C] --1-pending, 2-sent, 3-resulted, 4-canceled, 5-completed, 8-suspend, 9-discontinued, 11-dispensed ,med.ORDERING_MODE_C --1=outpatient ,case when c.GenericName like 'naloxone%' then 1 else 0 end Narcan into #PtCSmed FROM [Clarity].[dbo].[ORDER_MED] med join #ED_visits e on med.PAT_ID=e.PAT_ID and med.ORDER_INST < e.ADT_ARRIVAL_TIME and datediff(dd, med.ORDER_INST, ADT_ARRIVAL_TIME) <= 90 --** CS ordered within the past 90 days of ED visit **-- --and ((med.DISCON_TIME >= e.ADT_ARRIVAL_TIME) or med.DISCON_TIME is null) join #CS_med c on med.MEDICATION_ID=c.MEDICATION_ID left join clarity..CLARITY_MEDICATION m on med.MEDICATION_ID=m.MEDICATION_ID left join Clarity..ZC_ORDER_CLASS zc on med.ORDER_CLASS_C=zc.ORDER_CLASS_C where med.ORDERING_MODE_c=1 --outpatients and (med.[ORDER_STATUS_C] in (2, 5, 11) or med.[ORDER_STATUS_C] is null) --1-pending, 2-sent, 3-resulted, 4-canceled, 5-completed, 8-suspend, 9-discontinued, 11-dispensed, null=historical -- find all naloxone/naltrexone/bup/methadone given in ED or Rx for home -- IF OBJECT_ID('tempdb..#Nx_med') IS NOT NULL DROP TABLE #Nx_med SELECT distinct med.[ORDER_MED_ID] ,med.[PAT_ID] ,med.[PAT_ENC_CSN_ID] ,e.ADT_ARRIVAL_TIME ED_Date ,med.[ORDERING_DATE] ,datediff(dd, (select p.Trial_Start_Dt from #Param p), med.[ORDERING_DATE]) Ordering_Dt ,med.[ORDER_INST] --date and time order was placed ,med.[DISCON_TIME] ,zc.NAME order_class_name ,c.NAME GenericName ,med.[DESCRIPTION] --,med.[SIG] ,m.STRENGTH ,med.[QUANTITY] ,left(med.quantity,charindex(' ',med.quantity)-1) Quant_Numb ,right(med.QUANTITY,len(med.quantity)-CHARINDEX(' ',med.quantity)) Quant_type ,med.[REFILLS] ,med.MED_PRESC_PROV_ID ,med.ORD_PROV_ID ,med.ORD_CREATR_USER_ID ,s.PROV_NAME ,s.CLINICIAN_TITLE ,med.ORDERING_MODE_C ,case when med.ORDERING_MODE_C=1 then 'Outpatient' when med.ORDERING_MODE_C=2 then 'Inpatient' end IN_Outpatient into #Nx_med FROM [Clarity]..[ORDER_MED] med join #ED_visits e on med.PAT_ENC_CSN_ID=e.PAT_ENC_CSN_ID join clarity..CLARITY_MEDICATION m on med.MEDICATION_ID=m.MEDICATION_ID left join Clarity..ZC_SIMPLE_GENERIC c on m.SIMPLE_GENERIC_C=c.SIMPLE_GENERIC_C left join Clarity..ZC_ORDER_CLASS zc on med.ORDER_CLASS_C=zc.ORDER_CLASS_C left join clarity..CLARITY_SER s on med.ORD_PROV_ID=s.PROV_ID where (med.[ORDER_STATUS_C] in (2, 5, 11) or med.[ORDER_STATUS_C] is null) --1-pending, 2-sent, 3-resulted, 4-canceled, 5-completed, 8-suspend, 9-discontinued, 11-dispensed, null=historical and (c.name like 'naloxone%' or c.name like 'naltrexone%' or c.name like 'buprenorphine%' or c.name like 'methadone%' ) and med.ORD_CREATR_USER_ID <> 'EDIADSD' -- interfaced from Automated dispensing system, creates duplicate order -- find all patients who were prescribed narcan within two year of ED visit -- IF OBJECT_ID('tempdb..#PtRxNaloxone') IS NOT NULL DROP TABLE #PtRxNaloxone SELECT distinct med.[PAT_ID] ,e.[PAT_ENC_CSN_ID] ,e.ADT_ARRIVAL_TIME ED_Date ,c.GenericName into #PtRxNaloxone FROM [Clarity].[dbo].[ORDER_MED] med join #ED_visits e on med.PAT_ID=e.PAT_ID and med.ORDER_INST < e.ADT_ARRIVAL_TIME join #CS_med c on med.MEDICATION_ID=c.MEDICATION_ID where (med.[ORDER_STATUS_C] in (2, 5, 11) or med.[ORDER_STATUS_C] is null) --1-pending, 2-sent, 3-resulted, 4-canceled, 5-completed, 8-suspend, 9-discontinued, 11-dispensed, null=historical and med.ORDERING_MODE_c=1 --outpatients and (c.GenericName like 'naloxone%') and datediff(m, med.[ORDER_INST], e.ADT_ARRIVAL_TIME ) <= 24 IF @Timer='T' Select 'Medication finished: ' + cast(datediff(mi, @time, getdate()) as varchar) ----------- ** Diagnosis ** ---------------- -- get ED discharge diagnosis ---------------- -- IF OBJECT_ID('tempdb..#FinalDx') IS NOT NULL DROP TABLE #FinalDx select distinct ev.* ,case when ped.DIAGNOSIS_CODE is null then ped.CURRENT_ICD10_LIST when ped.CURRENT_ICD10_LIST is null then ped.CURRENT_ICD9_LIST else ped.DIAGNOSIS_CODE end Diagnosis_Code ,ped.DX_NAME into #FinalDx from #ED_visits EV join (select d.DX_ID, d.CONTACT_DATE, d.PAT_ENC_CSN_ID, ce.DIAGNOSIS_CODE ,ce.CURRENT_ICD10_LIST, ce.CURRENT_ICD9_LIST, ce.DX_NAME from clarity..PAT_ENC_DX d join (select * from clarity..CLARITY_EDG e where (left(e.DIAGNOSIS_CODE,3) = 'F11' or left(e.CURRENT_ICD10_LIST,3) = 'F11' or left(e.DIAGNOSIS_CODE,5) in ('T40.0', 'T40.1', 'T40.2', 'T40.3', 'T40.4', 'T40.6') or left(e.CURRENT_ICD10_LIST,5) in ('T40.0', 'T40.1', 'T40.2', 'T40.3', 'T40.4', 'T40.6')) and e.RECORD_STATE_C is null --active diagnosis ) ce on d.DX_ID=ce.DX_ID where (d.CONTACT_DATE >= @Start_Dt and d.CONTACT_DATE <= @End_Dt) ) ped on EV.PAT_ENC_CSN_ID=ped.PAT_ENC_CSN_ID -- find all ETOH abuse in ED final DX --------------- -- IF OBJECT_ID('tempdb..#ETOH_DX') IS NOT NULL DROP TABLE #ETOH_DX select distinct ev.* ,case when ped.DIAGNOSIS_CODE is null then ped.CURRENT_ICD10_LIST when ped.CURRENT_ICD10_LIST is null then ped.CURRENT_ICD9_LIST else ped.DIAGNOSIS_CODE end Diagnosis_Code ,ped.DX_NAME into #ETOH_DX from #ED_visits EV join (select d.DX_ID, d.CONTACT_DATE, d.PAT_ENC_CSN_ID, ce.DIAGNOSIS_CODE ,ce.CURRENT_ICD10_LIST, ce.CURRENT_ICD9_LIST, ce.DX_NAME from clarity..PAT_ENC_DX d join (select * from clarity..CLARITY_EDG e where (e.DX_NAME like '%alcohol%' or e.DX_NAME like '%ETOH%') --and e.DIAGNOSIS_CODE <> 'Z87.898' -- can be null leading to excluding CSN and e.CURRENT_ICD9_LIST <> 'Z87.898' -- personal h/o other specified conditions and e.CURRENT_ICD10_LIST <> 'Z87.898' and e.RECORD_STATE_C is null ) ce on d.DX_ID=ce.DX_ID where (d.CONTACT_DATE >= @Start_Dt and d.CONTACT_DATE <= @End_Dt) ) ped on EV.PAT_ENC_CSN_ID=ped.PAT_ENC_CSN_ID -- find all Benzo abuse in ED final DX --------------- -- IF OBJECT_ID('tempdb..#Benzo_DX') IS NOT NULL DROP TABLE #Benzo_DX select distinct ev.* ,case when ped.DIAGNOSIS_CODE is null then ped.CURRENT_ICD10_LIST when ped.CURRENT_ICD10_LIST is null then ped.CURRENT_ICD9_LIST else ped.DIAGNOSIS_CODE end Diagnosis_Code ,ped.DX_NAME into #Benzo_DX from #ED_visits EV join (select d.DX_ID, d.CONTACT_DATE, d.PAT_ENC_CSN_ID, ce.DIAGNOSIS_CODE ,ce.CURRENT_ICD10_LIST, ce.CURRENT_ICD9_LIST, ce.DX_NAME from clarity..PAT_ENC_DX d join (select * from clarity..CLARITY_EDG e where e.DX_NAME like '%benzo%' and e.RECORD_STATE_C is null ) ce on d.DX_ID=ce.DX_ID and (d.CONTACT_DATE >= @Start_Dt and d.CONTACT_DATE <= @End_Dt) --and month(d.CONTACT_DATE)>3 ) ped on EV.PAT_ENC_CSN_ID=ped.PAT_ENC_CSN_ID -- find all opioid medical history -- IF OBJECT_ID('tempdb..#PMH') IS NOT NULL DROP TABLE #PMH SELECT mh.[PAT_ID] ,ed.ADT_ARRIVAL_TIME ,ed.PAT_ENC_CSN_ID ,case when e.DIAGNOSIS_CODE is null then e.CURRENT_ICD10_LIST when e.CURRENT_ICD10_LIST is null then e.CURRENT_ICD9_LIST else e.DIAGNOSIS_CODE end Diagnosis_Code ,e.DX_NAME ,mh.[CONTACT_DATE] ,datediff(dd, (select p.Trial_Start_Dt from #Param p), mh.[CONTACT_DATE]) PMH_contact_dt ,mh.[MEDICAL_HX_DATE] ,mh.[COMMENTS] into #PMH FROM [Clarity].[dbo].[MEDICAL_HX] mh join (select distinct ev.PAT_ID, ev.PAT_ENC_CSN_ID, ev.ADT_ARRIVAL_TIME from #ED_visits ev ) ed on mh.PAT_ID=ed.Pat_ID left join clarity..CLARITY_EDG e on mh.DX_ID=e.DX_ID where (e.DIAGNOSIS_CODE = 'F11' or left(e.DIAGNOSIS_CODE,5) in ('T40.0', 'T40.1', 'T40.2', 'T40.3', 'T40.4', 'T40.6') or e.DIAGNOSIS_CODE in ('304.00', '304.01', '304.02', '304.03', '304.70', '304.71', '304.72', '304.73', '305.50', '305.51', '305.52', '305.53', '965.00', '965.01', '965.02', '965.09', 'E850.0', 'E850.1', 'E850.2', 'E935.0', 'E935.1', 'E935.2') or e.CURRENT_ICD10_LIST = 'F11' or left(e.CURRENT_ICD10_LIST,5) in ('T40.0', 'T40.1', 'T40.2', 'T40.3', 'T40.4', 'T40.6') or left(e.CURRENT_ICD9_LIST,6) in ('304.00', '304.01', '304.02', '304.03', '304.70', '304.71', '304.72', '304.73', '305.50', '305.51', '305.52', '305.53', '965.00', '965.01', '965.02', '965.09', 'E850.0', 'E850.1', 'E850.2', 'E935.0', 'E935.1', 'E935.2') ) and cast(mh.CONTACT_DATE as date) <= cast(ed.ADT_ARRIVAL_TIME as date) -- find all opioid treatment in problem ist --- -- IF OBJECT_ID('tempdb..#PL_OUD_Tx') IS NOT NULL DROP TABLE #PL_OUD_Tx SELECT pl.[PAT_ID] ,ed.ADT_ARRIVAL_TIME ,ed.PAT_ENC_CSN_ID ,case when edg.DIAGNOSIS_CODE is null then edg.CURRENT_ICD10_LIST when edg.CURRENT_ICD10_LIST is null then edg.CURRENT_ICD9_LIST else edg.DIAGNOSIS_CODE end Diagnosis_Code ,edg.DX_NAME ,pl.DATE_OF_ENTRY ,case when pl.DATE_OF_ENTRY is not null then datediff(dd, @Trial_Start_Dt, pl.DATE_OF_ENTRY) else -99999 end PL_Entry_DT into #PL_OUD_Tx FROM [Clarity]..PROBLEM_LIST pl join (select distinct ev.PAT_ID, ev.PAT_ENC_CSN_ID, ev.ADT_ARRIVAL_TIME from #ED_visits ev ) ed on pl.PAT_ID=ed.Pat_ID join (select * from clarity..CLARITY_EDG e where (e.DX_NAME like '%subox%' or e.DX_NAME like '%buprenor%' or e.DX_NAME like '%methadone%') and (e.DX_NAME like '%treatment%' or e.DX_NAME like '%therapy%' or e.DX_NAME like '%maintenance%') and e.RECORD_STATE_C is null ) edg on pl.DX_ID=edg.dx_id where cast(pl.DATE_OF_ENTRY as date) <= cast(ed.ADT_ARRIVAL_TIME as date) -- find OUD in problem list -- IF OBJECT_ID('tempdb..#PL_OUD_Dx') IS NOT NULL DROP TABLE #PL_OUD_Dx SELECT pl.[PAT_ID] ,ed.ADT_ARRIVAL_TIME ,ed.PAT_ENC_CSN_ID ,case when edg.DIAGNOSIS_CODE is null then edg.CURRENT_ICD10_LIST when edg.CURRENT_ICD10_LIST is null then edg.CURRENT_ICD9_LIST else edg.DIAGNOSIS_CODE end Diagnosis_Code ,edg.DX_NAME ,pl.DATE_OF_ENTRY ,case when pl.DATE_OF_ENTRY is null then -99999 else datediff(dd, @Trial_Start_Dt, pl.DATE_OF_ENTRY) end PL_OUD_DX_DT into #PL_OUD_Dx FROM [Clarity]..PROBLEM_LIST pl join (select distinct ev.PAT_ID, ev.PAT_ENC_CSN_ID, ev.ADT_ARRIVAL_TIME from #ED_visits ev ) ed on pl.PAT_ID=ed.Pat_ID left join clarity..CLARITY_EDG edg on pl.DX_ID=edg.DX_ID where (edg.DIAGNOSIS_CODE = 'F11' or left(edg.DIAGNOSIS_CODE,5) in ('T40.0', 'T40.1', 'T40.2', 'T40.3', 'T40.4', 'T40.6') or edg.DIAGNOSIS_CODE in ('304.00', '304.01', '304.02', '304.03', '304.70', '304.71', '304.72', '304.73', '305.50', '305.51', '305.52', '305.53', '965.00', '965.01', '965.02', '965.09', 'E850.0', 'E850.1', 'E850.2', 'E935.0', 'E935.1', 'E935.2') or edg.CURRENT_ICD10_LIST = 'F11' or left(edg.CURRENT_ICD10_LIST,5) in ('T40.0', 'T40.1', 'T40.2', 'T40.3', 'T40.4', 'T40.6') or left(edg.CURRENT_ICD9_LIST,6) in ('304.00', '304.01', '304.02', '304.03', '304.70', '304.71', '304.72', '304.73', '305.50', '305.51', '305.52', '305.53', '965.00', '965.01', '965.02', '965.09', 'E850.0', 'E850.1', 'E850.2', 'E935.0', 'E935.1', 'E935.2') ) and cast(pl.UPDATE_DATE as date) <= cast(ed.ADT_ARRIVAL_TIME as date) IF @Timer='T' Select 'Diagnosis finished: ' + cast(datediff(mi, @time, getdate()) as varchar) ---------------------*** Social and CC ***----------------------- -- find all past social history of drug abuse -- IF OBJECT_ID('tempdb..#PS') IS NOT NULL DROP TABLE #PS SELECT distinct sh.[PAT_ID] ,sh.[CONTACT_DATE] ,datediff(dd, @Trial_Start_Dt, sh.[CONTACT_DATE]) SHx_Contact_Dt ,e.[PAT_ENC_CSN_ID] ,sh.[HX_LNK_ENC_CSN] ,sh.[IV_DRUG_USER_YN] ,sh.[ILLICIT_DRUG_FREQ] ,sh.[ILLICIT_DRUG_CMT] ,sh.[ILL_DRUG_USER_C] --1-yes, 2-no, 3-not asked, 4-defer into #PS FROM [Clarity]..[SOCIAL_HX] sh join #ED_visits e on sh.PAT_ID = e.PAT_ID and cast(sh.CONTACT_DATE as date) <= cast(e.ADT_ARRIVAL_TIME as date) where [IV_DRUG_USER_YN] = 'Y' or ILL_DRUG_USER_C = 1 or cast([ILLICIT_DRUG_FREQ] as decimal) > 0 -- IF OBJECT_ID('tempdb..#PSH') IS NOT NULL DROP TABLE #PSH select distinct ps.PAT_ID ,ps.PAT_ENC_CSN_ID ,max(a.Ill_User) Ill_user into #PSH from #PS ps left join ( select p.pat_id ,p.PAT_ENC_CSN_ID ,Case when p.ILL_DRUG_USER_C=1 or cast(p.[ILLICIT_DRUG_FREQ] as decimal) > 0 or p.[IV_DRUG_USER_YN] = 'Y' then 1 else 0 end Ill_User from #ps p ) a on ps.PAT_ENC_CSN_ID=a.PAT_ENC_CSN_ID group by ps.PAT_ID ,ps.PAT_ENC_CSN_ID ,ps.CONTACT_DATE -- find all Chief Complaints with words overdose, detox, withdrawal -- IF OBJECT_ID('tempdb..#CC') IS NOT NULL DROP TABLE #CC select distinct cast(ercc.PAT_ENC_CSN_ID as bigint) PAT_ENC_CSN_ID ,ercc.ER_COMPLAINT ,ercc.LINE into #CC from PAT_ENC_ER_COMPLNT ercc join #ED_visits ed on ercc.PAT_ENC_CSN_ID=ed.PAT_ENC_CSN_ID where (ercc.ER_COMPLAINT like '%overdose%' or ercc.ER_COMPLAINT like '%detox%' or ercc.ER_COMPLAINT like '%Withdrawal%') Union select distinct cast(rv.PAT_ENC_CSN_ID as int) PAT_ENC_CSN_ID ,rv.COMMENTS ER_COMPLAINT --,rv.ENC_REASON_NAME ,rv.LINE from clarity..PAT_ENC_RSN_VISIT rv join #ED_visits e on rv.PAT_ENC_CSN_ID=e.PAT_ENC_CSN_ID where (rv.COMMENTS like '%overdose%' or rv.COMMENTS like '%detox%' or rv.COMMENTS like '%Withdrawal%') -- find all Chief Complaints with words ETOH, alcohol, withdrawal alcohol -- IF OBJECT_ID('tempdb..#CC_ETOH') IS NOT NULL DROP TABLE #CC_ETOH select rv.PAT_ENC_CSN_ID ,rv.COMMENTS ER_COMPLAINT ,rv.LINE into #CC_ETOH from clarity..PAT_ENC_RSN_VISIT rv join #ED_visits e on rv.PAT_ENC_CSN_ID=e.PAT_ENC_CSN_ID join (select * from clarity..CL_RSN_FOR_VISIT c where (c.REASON_VISIT_NAME like 'ETOH%' or c.REASON_VISIT_NAME like 'alcohol%' or c.REASON_VISIT_NAME like '%withdrawal%alcohol%') ) r on rv.ENC_REASON_ID=r.REASON_VISIT_ID Union select distinct ercc.PAT_ENC_CSN_ID ,ercc.ER_COMPLAINT ,ercc.LINE from PAT_ENC_ER_COMPLNT ercc join #ED_visits ed on ercc.PAT_ENC_CSN_ID=ed.PAT_ENC_CSN_ID where (ercc.ER_COMPLAINT like '%alcohol%' or ercc.ER_COMPLAINT like '%ETOH%') -- find all Chief Complaints with words heroin, opiate, opioid -- IF OBJECT_ID('tempdb..#CC_opioid') IS NOT NULL DROP TABLE #CC_opioid select rv.PAT_ENC_CSN_ID ,rv.COMMENTS ER_COMPLAINT ,rv.LINE into #CC_opioid from clarity..PAT_ENC_RSN_VISIT rv join #ED_visits e on rv.PAT_ENC_CSN_ID=e.PAT_ENC_CSN_ID join ( select cv.REASON_VISIT_ID from clarity..CL_RSN_FOR_VISIT cv where cv.REASON_VISIT_NAME like '%heroin%' or cv.REASON_VISIT_NAME like '%opiate%' or cv.REASON_VISIT_NAME like '%opioid%' ) r on rv.ENC_REASON_ID=r.REASON_VISIT_ID Union select distinct ercc.PAT_ENC_CSN_ID ,ercc.ER_COMPLAINT ,ercc.LINE from PAT_ENC_ER_COMPLNT ercc join #ED_visits ed on ercc.PAT_ENC_CSN_ID=ed.PAT_ENC_CSN_ID where (ercc.ER_COMPLAINT like '%heroin%' or ercc.ER_COMPLAINT like '%opiate%' or ercc.ER_COMPLAINT like '%opioid%') union select rv.PAT_ENC_CSN_ID ,rv.COMMENTS ER_COMPLAINT ,rv.LINE from clarity..PAT_ENC_RSN_VISIT rv join #ED_visits e on rv.PAT_ENC_CSN_ID=e.PAT_ENC_CSN_ID where rv.COMMENTS like '%heroin%' or rv.COMMENTS like '%opiate%' or rv.COMMENTS like '%opioid%' -- find all Chief Complaints with words narcan or naloxone -- IF OBJECT_ID('tempdb..#CC_Narcan') IS NOT NULL DROP TABLE #CC_Narcan select rv.PAT_ENC_CSN_ID ,rv.COMMENTS CC_Comment ,rv.LINE into #CC_Narcan from clarity..PAT_ENC_RSN_VISIT rv join #ED_visits e on rv.PAT_ENC_CSN_ID=e.PAT_ENC_CSN_ID where rv.COMMENTS like '%narcan%' or rv.COMMENTS like '%naloxone%' Union select distinct ercc.PAT_ENC_CSN_ID ,ercc.ER_COMPLAINT ,ercc.LINE from PAT_ENC_ER_COMPLNT ercc join #ED_visits ed on ercc.PAT_ENC_CSN_ID=ed.PAT_ENC_CSN_ID where (ercc.ER_COMPLAINT like '%narcan%' or ercc.ER_COMPLAINT like '%naloxone%') IF @Timer='T' Select 'Social Hx & CC finished: ' + cast(datediff(mi, @time, getdate()) as varchar) -------------------*** Note ***------------------------------------ -- find all patient instruction with OUD related -- IF OBJECT_ID('tempdb..#PtInsNote') IS NOT NULL DROP TABLE #PtInsNote SELECT h.[NOTE_ID] ,h.[PAT_ID] ,h.[PAT_ENC_CSN_ID] ,h.[ENTRY_DATETIME] ,h.[INPATIENT_DATA_ID] ,h.NOTE_TYPE_NOADD_C ,h.[DEPT_SPECIALTY_C] ,h.[CREATE_INSTANT_DTTM] ,h.[DATE_OF_SERVIC_DTTM] ,h.[NOTE_PURPOSE_C] ,h.[AMB_NOTE_YN] into #PtInsNote FROM [Clarity].[dbo].[HNO_INFO] h join #ED_visits e on h.PAT_ID=e.PAT_ID and CONVERT(varchar(12),e.ADT_ARRIVAL_TIME, 101) = CONVERT(varchar(12),h.DATE_OF_SERVIC_DTTM, 101) where [DELETED_CAT_C] is null -- (2,4,6) not hidden, delete, hidden delete and (NOTE_TYPE_NOADD_C = '50' -- patient instruction or IP_NOTE_TYPE_C in ('37','61','62','63','64','65','66','67','68')) -- inpatient patient instruction for OUD note type and (h.[CREATE_INSTANT_DTTM] >= @Start_Dt and h.[CREATE_INSTANT_DTTM] <= @End_Dt) -- smarttext with opioid for patient instruction /** see query to extract SmartText ID **/ -- IF OBJECT_ID('tempdb..#smrt') IS NOT NULL DROP TABLE #smrt SELECT sr.[PAT_ENC_CSN_ID] ,sr.[LINE] ,sr.[PAT_ID] ,sr.[CONTACT_DATE] ,sr.[SEL_DOC_ETX_DAT] ,sm.* into #smrt FROM [Clarity]..[SEL_REF_DOCS_ETX] sr join #ED_visits e on sr.PAT_ENC_CSN_ID=e.PAT_ENC_CSN_ID left join (SELECT distinct tx.[SMARTTEXT_ID] ,st.SMARTTEXT_NAME ,zs.NAME FROM [Clarity]..[SMARTTEXT_TEXT] tx join (select t.SMARTTEXT_ID, max(t.CONTACT_DATE_REAL) DT from clarity..[SMARTTEXT_TEXT] t group by t.SMARTTEXT_ID) m on tx.SMARTTEXT_ID=m.SMARTTEXT_ID and tx.CONTACT_DATE_REAL=m.DT left join clarity..[SMARTTEXT_TYPES] ty on tx.SMARTTEXT_ID=ty.SMARTTEXT_ID left join clarity..SMARTTEXT st on tx.SMARTTEXT_ID=st.SMARTTEXT_ID left join Clarity..ZC_TYPE_OF_SMARTTE zs on ty.TYPE_OF_SMARTTEXT_C=zs.TYPE_OF_SMARTTE_C where /** see query to extract SmartText ID **/ tx.[SMARTTEXT_ID] in --('1600011291', '160003173', '1600008449', '160045326', '1600007567', '160006054', '160016858', '160001327', --'13978', '18491', '1600008223', '1600006976', '1600008228', '1600006276', '1600007534', '160001228', --'1600007293', '160003163', '160003229', '1600008141', '1600006113', '13689', '18649', '15155', '17648', --'15945', '18368', '13133', '18702', '14656', '17891', '15524', '13234', '14736', '19516') ) sm on sr.SEL_DOC_ETX_ID = sm.SMARTTEXT_ID where (sr.[CONTACT_DATE] >= @Start_Dt and sr.[CONTACT_DATE] <= @End_Dt) and [SEL_DOC_ETX_ID] in --('1600011291', '160003173', '1600008449', '160045326', '1600007567', '160006054', '160016858', '160001327', --'13978', '18491', '1600008223', '1600006976', '1600008228', '1600006276', '1600007534', '160001228', --'1600007293', '160003163', '160003229', '1600008141', '1600006113', '13689', '18649', '15155', '17648', --'15945', '18368', '13133', '18702', '14656', '17891', '15524', '13234', '14736', '19516') IF @Timer='T' Select 'Notes finished: ' + cast(datediff(mi, @time, getdate()) as varchar) ------------------*** Labs ***------------------------------ -- get all labs with opiate /** see subquery to pull lab component ID **/ -- IF OBJECT_ID('tempdb..#OpiateResults') IS NOT NULL DROP TABLE #OpiateResults SELECT or1.[ORDER_PROC_ID] ,en.PAT_ID ,OR1.[LINE] ,OR1.[RESULT_DATE] ,OR1.[COMPONENT_ID] --clarity_component --,OR1.[PAT_ID] --null ,OR1.[PAT_ENC_CSN_ID] ,OR1.[ORD_VALUE] ,OR1.[ORD_NUM_VALUE] ,OR1.[RESULT_FLAG_C] ,OR1.[RESULT_STATUS_C] ,OR1.[LAB_STATUS_C] ,OR1.[SERV_AREA_ID] ,OR1.[RESULT_TIME] ,OR1.[RESULTING_LAB_ID] ,OR1.[COMPONENT_COMMENT] ,OR1.[DATA_TYPE_C] into #OpiateResults FROM [Clarity]..[ORDER_RESULTS] OR1 left join clarity..PAT_ENC en on or1.PAT_ENC_CSN_ID=en.PAT_ENC_CSN_ID where (OR1.[RESULT_DATE] >= @Start_Dt and OR1.[RESULT_DATE] <= @End_Dt) and or1.RESULT_STATUS_C in ('3', '4') -- final, corrected and or1.COMPONENT_ID in /** see subquery to pull lab component ID **/ --('15281', '15285', '15357', '15286', '15358', '15356', '19712', '1810490', '15352', -- '1511017', '10372', '8969', '20851', '20846', '1199', '15283', '6534', '15353', -- '1510975', '6528', '19472', '1811435', '1510790', '11211', '1008', '1015', '11212', -- '1510791', '19311', '6557', '15282', '23829', '23823', '1510955', '6531', '6529', -- '6532', '6533', '17033', '17034', '6562', '1811411', '1811327', '29199', '19312', -- '1740080', '19313', '1811325', '1811328', '1811324', '1811326', '1810480', '6545', -- '6552', '23840', '23841', '23837', '23838', '23836', '23839', '23869', '10124', -- '10125', '10126', '1561923', '24140', '2036', '19315', '23663', '19940', '18725', -- '23664', '23684', '6612', '15287', '24127', '1555062', '18726', '17388') -- get only the postive labs -- IF OBJECT_ID('tempdb..#PosLabs') IS NOT NULL DROP TABLE #PosLabs select * into #PosLabs from (select o.PAT_ID ,o.PAT_ENC_CSN_ID ,c.COMMON_NAME ,o.ORD_VALUE ,o.COMPONENT_COMMENT ,o.ORD_NUM_VALUE ,o.RESULT_DATE from #OpiateResults o join #ED_visits e on o.PAT_ID=e.PAT_ID left join [Clarity].[dbo].[CLARITY_COMPONENT] c on o.COMPONENT_ID=c.COMPONENT_ID where ( COMPONENT_COMMENT like '%opioid%' or COMPONENT_COMMENT like '%opiate%' or COMPONENT_COMMENT like '%codeine%' or COMPONENT_COMMENT like '%fentanyl%' or COMPONENT_COMMENT like '%heroin%' or COMPONENT_COMMENT like '%hydrocodone%' or COMPONENT_COMMENT like '%hydromorphone%' or COMPONENT_COMMENT like '%morphine%' or COMPONENT_COMMENT like '%opium%' or COMPONENT_COMMENT like '%oxycodone%' ) ) a where (a.ORD_NUM_VALUE <> 99999) or (a.ORD_VALUE like 'pos%') IF @Timer='T' Select 'Labs finished: ' + cast(datediff(mi, @time, getdate()) as varchar) ------------------- ** Combine ** ------------------- -- IF OBJECT_ID('tempdb..#Comb') IS NOT NULL DROP TABLE #Comb select distinct e.PAT_ID, pt.PAT_MRN_ID, e.Research_ID ,e.PAT_ENC_CSN_ID ,e.ADT_ARRIVAL_TIME ,e.Arrival_DT ,e.Arrival_Hr ,e.Discharge_DT ,e.Visit_len_Min ,case when e.Age > 85 then 100 when e.age is null then -99999 else e.age end Age ,e.DEPARTMENT_NAME ,e.DEPARTMENT_ID ,case when si.Site_ID is null then -99999 else si.Site_ID end Site_ID ,ac.BILL_ATTEND_PROV_ID ,case when cc.PAT_ENC_CSN_ID is null then 2 else 1 end CC ,case when cc_o.PAT_ENC_CSN_ID is null then 2 else 1 end CC_Opioid ,case when cc_n.PAT_ENC_CSN_ID is null then 2 else 1 end CC_Narcan ,case when cc_e.PAT_ENC_CSN_ID is null then 2 else 1 end CC_ETOH ,case when d.PAT_ID is null then 2 else 1 end OUD_Dx ,case when OnCSmed.PAT_ID is null then 2 else 1 end On_CS_Med ,case when OnCSmed.CNT is null then 2 else OnCSmed.CNT end CSMed_refills ,case when NxIn.PAT_ENC_CSN_ID is null then 2 else 1 end Narcan_Inpatient ,case when NxOut.PAT_ENC_CSN_ID is null then 2 else 1 end Narcan_Outpatient ,case when pn.PAT_ENC_CSN_ID is null then 2 else 1 end Narcan24Mo ,case when pd.PAT_ENC_CSN_ID is null then 2 else 1 end PL_OUD_Dx ,case when pd.PAT_ENC_CSN_ID is null then -99999 else pd.PL_OUD_DX_DT end PL_OUD_DX_DT ,case when pm.PAT_ENC_CSN_ID is null then 2 else 1 end PMH ,case when ps.Ill_user = 1 then 1 else 2 end Ill_Drug ,case when sm.DC_instr is null then 2 else 1 end DC_inst ,case when pl.PAT_ID is null then 2 else 1 end Opiate_Labs ,case when ox.PAT_ENC_CSN_ID is null then 2 else 1 end Oxycodone_lab ,case when pb.Fin_class is not null then pb.Fin_class when ac.Fin_class is not null then ac.Fin_class else '-99999' end Fin_class ,ed.ED_visits Total_ED_visits ,v.Inp_adm_DT Last_Inp_Adm_DT --Last_adm before ED visit - 1/1/2018 ,pa.Psy_adm_DT Last_Psy_Adm_DT ,case when et.PAT_ENC_CSN_ID is null then 2 else 1 end ETOH_Dx ,case when bz.PAT_ENC_CSN_ID is null then 2 else 1 end Benzo_Dx ,case when m.PAT_ENC_CSN_ID is null then 2 else 1 end MOUD ,case when po.PAT_ENC_CSN_ID is null then 2 else 1 end PL_MOUD -- OUD Tx med in Problem list ,case when po.PAT_ENC_CSN_ID is null then -99999 else po.PL_Entry_DT end PL_MOUD_DT ,case when d1.PAT_ENC_CSN_ID is null then 1 else 2 end LWBS -- no final diagnosis thus LWBS ,case when e.Pregnant is null then 2 when e.Pregnant = 0 then 2 else e.Pregnant end Pregnant into #Comb from #ED_visits e left join clarity..patient pt on e.PAT_ID=pt.PAT_ID left join #FinalDx d on e.PAT_ENC_CSN_ID=d.PAT_ENC_CSN_ID left join (select * from #OUDmed om where om.ORDERING_MODE_C = 1 -- 1-outpatient, 2-inpatient ) m on e.PAT_ENC_CSN_ID=m.PAT_ENC_CSN_ID left join -- OnCSmed (select distinct p1.pat_ID, count(p1.PAT_ENC_CSN_ID) CNT from (select distinct p.pat_ID, p.PAT_ENC_CSN_ID from #PtCSmed p where ((cast(p.Quant_Numb as decimal)>=15 and p.Quant_type <> 'ml') --pills >15 or (cast(p.Quant_Numb as decimal)>=75 and p.Quant_type = 'ml') -- liquid >= 15*5cc or (cast(p.Quant_Numb as decimal)>= 5 and p.Quant_type = 'patch')) -- any patch and p.GenericName not like 'naloxone%' and p.GenericName not like 'naltrexone%' ) P1 group by p1.PAT_ID having count(p1.PAT_ENC_CSN_ID) > 2 --have had more than 2 prescriptions ) OnCSmed on e.PAT_ID=OnCSmed.PAT_ID left join #PtRxNaloxone pn on e.PAT_ENC_CSN_ID=pn.PAT_ENC_CSN_ID --Narcan24Mo left join #PMH pm on e.PAT_ENC_CSN_ID=pm.PAT_ENC_CSN_ID left join #PSH ps on e.PAT_ENC_CSN_ID=ps.PAT_ENC_CSN_ID left join (select distinct PAT_ENC_CSN_ID from #CC) cc on e.PAT_ENC_CSN_ID=cc.PAT_ENC_CSN_ID left join (select distinct PAT_ENC_CSN_ID from #CC_ETOH) cc_e on e.PAT_ENC_CSN_ID=cc_e.PAT_ENC_CSN_ID left join (select distinct PAT_ENC_CSN_ID from #CC_opioid) cc_o on e.PAT_ENC_CSN_ID=cc_o.PAT_ENC_CSN_ID left join (select distinct PAT_ENC_CSN_ID from #CC_Narcan) cc_n on e.PAT_ENC_CSN_ID=cc_n.PAT_ENC_CSN_ID left join (select * from #Nx_med nxo where nxo.GenericName like 'naloxone%' --Narcan_Outpatient ) NxOut on NxOut.PAT_ENC_CSN_ID=e.PAT_ENC_CSN_ID and NxOut.IN_Outpatient='outpatient' left join (select * from #Nx_med nxo where nxo.GenericName like 'naloxone%' --Narcan_Inpatient ) NxIn on NxIn.PAT_ENC_CSN_ID=e.PAT_ENC_CSN_ID and NxIn.IN_Outpatient='inpatient' left join (select st.PAT_ENC_CSN_ID ,case when st.SMARTTEXT_NAME like 'methadone%' then 'Methadone' else 'SUD' end DC_instr from #smrt st ) sm on e.PAT_ENC_CSN_ID=sm.PAT_ENC_CSN_ID left join #PosLabs pl on e.PAT_ID=pl.PAT_ID left join #AcctList ac on e.PAT_ENC_CSN_ID=ac.PAT_ENC_CSN_ID left join #pb pb on e.PAT_ENC_CSN_ID=pb.PAT_ENC_CSN_ID --Fin_class left join ( select e1.pat_id ,count(e1.PAT_ENC_CSN_ID) ED_visits from #ED_visits e1 group by e1.PAT_ID ) ed on ed.PAT_ID=e.PAT_ID -- count of ED visits since Study start left join #In_Psyc_adm v on v.PAT_ENC_CSN_ID=e.PAT_ENC_CSN_ID left join #In_Psyc_adm pa on pa.PAT_ENC_CSN_ID=e.PAT_ENC_CSN_ID left join #ETOH_DX et on e.PAT_ENC_CSN_ID=et.PAT_ENC_CSN_ID left join #Benzo_DX bz on e.PAT_ENC_CSN_ID=bz.PAT_ENC_CSN_ID left join --PL_MOUD (select t.PAT_ID, t.PAT_ENC_CSN_ID, max(t.PL_Entry_DT) PL_Entry_DT from #PL_OUD_Tx t group by t.PAT_ID, t.PAT_ENC_CSN_ID ) po on e.PAT_ENC_CSN_ID=po.PAT_ENC_CSN_ID left join --PL_OUD_Dx (select d.PAT_ID, d.PAT_ENC_CSN_ID, max(d.PL_OUD_DX_DT) PL_OUD_DX_DT from #PL_OUD_Dx d group by d.PAT_ID, d.PAT_ENC_CSN_ID ) pd on e.PAT_ENC_CSN_ID=pd.PAT_ENC_CSN_ID left join clarity..PAT_ENC_DX d1 on e.PAT_ENC_CSN_ID=d1.PAT_ENC_CSN_ID --LWBS no Dx for those who left before being seen left join --Oxycodone_lab (select distinct o.PAT_ID ,o.PAT_ENC_CSN_ID ,c.COMMON_NAME ,o.ORD_VALUE ,o.COMPONENT_COMMENT from #OpiateResults o join #ED_visits e on o.PAT_ID=e.PAT_ID left join [Clarity].[dbo].[CLARITY_COMPONENT] c on o.COMPONENT_ID=c.COMPONENT_ID where ([ORD_VALUE] like '%oxycodone%' or COMPONENT_COMMENT like '%oxycodone%') and [ORD_VALUE] like '%positive%' ) ox on e.PAT_ENC_CSN_ID=ox.PAT_ENC_CSN_ID left join #Sites si on e.DEPARTMENT_NAME=si.Dept_Name where si.Site_ID <> -99999 -- Remove all none participating departments/sites and d1.PAT_ENC_CSN_ID is not null -- not LWBS and (e.Pregnant = 0) or (e.Pregnant is null) -- not pregnant and e.Age >= 18 -- age >= 18 and e.Visit_len_Min <= 24*60 -- visit length <= 24 hrs IF @Timer='T' Select 'Combined finished: ' + cast(datediff(mi, @time, getdate()) as varchar) ------------------ All active or non-active meds for Phenotype pos patients ---------------------- -- IF OBJECT_ID('tempdb..#PTAMeds') IS NOT NULL DROP TABLE #PTAMeds SELECT distinct p.PAT_ID ,med.[ORDERING_DATE] ,med.DISCON_TIME ,zc.NAME order_class_name ,med.[DESCRIPTION] ,m.GENERIC_NAME --,med.[SIG] ,med.[QUANTITY] ,med.[REFILLS] ,med.[ORDER_INST] --date and time order was placed into #PTAMeds FROM [Clarity]..[ORDER_MED] med join (select distinct pat_id from #Comb c --only OUD Phen pos where c.OUD_Dx=1 and (c.ETOH_Dx=2 and c.Benzo_Dx=2 and (c.CC_Opioid=1 or c.CC_Narcan=1)) ) p on med.PAT_ID=p.PAT_ID left join [Clarity]..[CLARITY_MEDICATION] m on med.MEDICATION_ID=m.MEDICATION_ID left join Clarity..ZC_ORDER_CLASS zc on med.ORDER_CLASS_C=zc.ORDER_CLASS_C where (med.[ORDER_STATUS_C] in (2, 5, 11) or med.[ORDER_STATUS_C] is null) --1-pending, 2-sent, 3-resulted, 4-canceled, 5-completed, 8-suspend, 9-discontinued, 11-dispensed, null=historical and med.[ACT_ORDER_C] <> 4 --1-active med, 2-active procedure, 3-discont med, 4-canceled proc, 5-completed med, 6-completed proc and med.ORDERING_MODE_C = 1 -- 1-outpatient, 2-inpatient and m.GENERIC_NAME <> 'MISCELLANEOUS MEDICAL SUPPLY MISC' ---------------------- PTA meds -------------------- -- IF OBJECT_ID('tempdb..#PTAMedsMin') IS NOT NULL DROP TABLE #PTAMedsMin select c.Site_ID, c.Research_ID ,p.ORDERING_DATE ,c.[PAT_ID], c.PAT_ENC_CSN_ID ,c.Arrival_DT ,c.Arrival_Hr ,datediff(dd, (select Trial_Start_Dt from #Param), p.ORDER_INST) ORDER_INST_DT ,p.order_class_name ,p.DESCRIPTION ,p.GENERIC_NAME --,p.SIG ,p.QUANTITY ,left(p.quantity,charindex(' ',p.quantity)-1) Quant_Numb ,right(p.QUANTITY,len(p.quantity)-CHARINDEX(' ',p.quantity)) Quant_type ,p.REFILLS into #PTAMedsMin from #Comb c join #PTAMeds p on p.PAT_ID=c.PAT_ID where cast(c.ADT_ARRIVAL_TIME as date) >= cast(p.[ORDERING_DATE] as date) -- meds ordered before arrival and ((cast(c.ADT_ARRIVAL_TIME as date) <= cast(p.DISCON_TIME as date)) or p.DISCON_TIME is null) -- meds not discontinued before arrival and p.GENERIC_NAME <> 'MISCELLANEOUS MEDICAL SUPPLY MISC' IF @Timer='T' Select 'PTA med finished: ' + cast(datediff(mi, @time, getdate()) as varchar) ----------------------- Access log ---------------------------------------------- ---- look for FDI in the access log belonging to Embed ---- -- IF OBJECT_ID('tempdb..#FDI') IS NOT NULL DROP TABLE #FDI select * into #FDI from clarity..ACCESS_LOG_DTL d where d.DATA_MNEMONIC_ID = 'FDI' and d.STRING_VALUE = cast((select p.[FDI_ID] from #Param p) as varchar(20)) --<<<-- FDI record for Embed -- IF OBJECT_ID('tempdb..#web') IS NOT NULL DROP TABLE #web select * into #web from [Clarity]..[ACCESS_LOG] l where l.ACCESS_INSTANT >= 5622930000 -- 3/8/2019 and l.[METRIC_ID]= (select [METRIC_ID] from #Param) --<<<--- Active Guidelines web integration record accessed IF @Timer='T' Select 'Access log finished: ' + cast(datediff(mi, @time, getdate()) as varchar) ---------------------------- Phen positive Random pick for QI/QA ------------------------------- /***** use this to perform QI/QA by uncommenting this and commenting out the following select query ***/ -- IF OBJECT_ID('tempdb..#RND') IS NOT NULL DROP TABLE #RND --select top 10 -- c.* -- ,ac.CSN Intervention_CSN -- ,case when c.OUD_Dx=1 then 'Phen 1' -- when (c.ETOH_Dx=2 and c.Benzo_Dx=2 and (c.CC_Opioid=1 or c.CC_Narcan=1)) then 'Phen 2' -- else '' -- end Phen -- into #RND -- from #Comb c -- left join -- ( select distinct al.csn from #web al -- left join #FDI ad on al.ACCESS_INSTANT=ad.ACCESS_INSTANT -- ) ac on c.PAT_ENC_CSN_ID = ac.CSN -- where (c.ETOH_Dx=2 and c.Benzo_Dx=2 and (c.CC_Opioid=1 or c.CC_Narcan=1)) or (c.OUD_Dx=1) --phen positive only -- order by NEWID() -- random pick ------------------------------- Use all phen positive patients --------------------------------------- select c.* ,ac.CSN Intervention_CSN ,case when c.OUD_Dx=1 then 'Phen 1' when (c.ETOH_Dx=2 and c.Benzo_Dx=2 and (c.CC_Opioid=1 or c.CC_Narcan=1)) then 'Phen 2' else '' end Phen into #RND from #Comb c left join ( select distinct al.csn from #web al join #FDI ad on al.ACCESS_INSTANT=ad.ACCESS_INSTANT ) ac on c.PAT_ENC_CSN_ID = ac.CSN where (c.ETOH_Dx=2 and c.Benzo_Dx=2 and (c.CC_Opioid=1 or c.CC_Narcan=1)) or (c.OUD_Dx=1) --phen positive only -----------------------------------Data------------------------------------------------ -- Data - All phenotype 1 & 2 -- select 'OUD_Phen_1_2 (All phenotype 1 & 2)' select c.Site_ID ,c.Research_ID --,c.PAT_MRN_ID, c.PAT_ENC_CSN_ID ,c.Arrival_DT, c.Arrival_Hr, -- c.Discharge_DT, c.Phen OUD, c.Visit_len_Min, c.Age Age_admission, -- c.DEPARTMENT_NAME, --case when s.Site_ID is null then c.DEPARTMENT_ID else s.Site_ID end DEPARTMENT_ID, c.CC, c.CC_Opioid, c.CC_Narcan, c.CC_ETOH, c.OUD_Dx, c.On_CS_Med, c.CSMed_refills, c.Narcan_Inpatient, c.Narcan_Outpatient, c.Narcan24Mo, c.PL_OUD_Dx, c.PL_OUD_DX_DT, c.PMH, c.Ill_Drug, c.DC_inst, c.Opiate_Labs, c.Oxycodone_lab, case when c.Fin_class is not null then c.Fin_class else '-99999' end Fin_class, c.Total_ED_visits, c.Last_Inp_Adm_DT, c.Last_Psy_Adm_DT, c.ETOH_Dx, c.Benzo_Dx, c.MOUD, c.PL_MOUD, c.PL_MOUD_DT, c.LWBS, c.Pregnant, case when c.Intervention_CSN is null then 2 else 1 end Intervention from #RND c left join #Sites s on c.DEPARTMENT_NAME=s.Dept_Name where c.phen is not null and c.Site_ID <> -99999 order by c.Research_ID -- Data - Demographics -- select 'Demographics' select distinct (select Heathcare_System_Id from #param) Healthcare_System_Id, c.Research_ID --,c.PAT_MRN_ID, c.PAT_ID ,age.Age Age_demo ,zs.NAME Gender ,zr.NAME Race ,case when ze.NAME is null then '-99999' else ze.NAME end Ethnicity from #RND c left join (select a.PAT_ID, min(a.Age) Age from #RND a group by a.PAT_ID ) age on age.PAT_ID=c.PAT_ID left join clarity..patient p on c.PAT_ID=p.PAT_ID left join clarity..ZC_SEX zs on p.SEX_C=zs.RCPT_MEM_SEX_C left join ( select distinct b.PAT_ID, b.PATIENT_RACE_C from clarity..PATIENT_RACE b join (select r.PAT_ID, max(r.line) line from clarity..PATIENT_RACE r join #RND cr on cr.PAT_ID=r.PAT_ID group by r.PAT_ID ) g on b.pat_id=g.PAT_ID and b.LINE=g.line ) pr on c.PAT_ID=pr.PAT_ID left join clarity..ZC_PATIENT_RACE zr on pr.PATIENT_RACE_C=zr.PATIENT_RACE_C left join clarity..ZC_ETHNIC_GROUP ze on p.ETHNIC_GROUP_C=ze.ETHNIC_GROUP_C where c.phen is not null and c.Site_ID <> -99999 order by c.Research_ID -- Data - ED follow up info -- select 'ED_follow_up (ED follow up)' SELECT c.Site_ID --,c.PAT_MRN_ID, c.PAT_ENC_CSN_ID ,c.Research_ID ,c.Arrival_DT ,c.Arrival_Hr ,fu.LINE ED_FUP_LINE ,case when fu.ED_FUP_PROVIDER_ID is null then '-99999' else fu.ED_FUP_PROVIDER_ID end ED_FUP_PROVIDER_ID --,ser.PROV_NAME FUP_Provider ,case when ser.PRACTICE_NAME_C is null then -99999 else ser.PRACTICE_NAME_C end FUP_Practice_ID --,zn.NAME FUP_Practice_name --,ser.RPT_GRP_ONE PROV_MED_GROUP ,case when sd.Site_ID is not null then sd.Site_ID when fu.ED_FUP_DEPT_ID is not null then fu.ED_FUP_DEPT_ID else -99999 end ED_FUP_DEPT_ID ,case when fu.ED_FUP_CONTACT is null then '-99999' else fu.ED_FUP_CONTACT end ED_FUP_CONTACT ,case when fu.ED_FUP_SUMMARY is null then '-99999' else fu.ED_FUP_SUMMARY end ED_FUP_SUMMARY --,fu.ED_FUP_PRN_TEXT --,dep.department_name FUP_ED_Dept_Name , case when fu.[ED_FUP_HOW_C] = '0' then 'None specified' when fu.[ED_FUP_HOW_C] = '1' then 'Call' when fu.[ED_FUP_HOW_C] = '2' then 'Make Appointment' else '-99999' end ED_FU_How FROM #RND c left join [Clarity]..[ED_FOLLOWUP_INFO] fu on c.PAT_ENC_CSN_ID=fu.PAT_ENC_CSN_ID left join [Clarity].[dbo].clarity_dep dep on fu.[ED_FUP_DEPT_ID] = dep.[DEPARTMENT_ID] left join #Sites sd on fu.ED_FUP_DEPT_ID=sd.Site_ID left join [Clarity].[dbo].clarity_ser ser on ser.[PROV_ID] = fu.[ED_FUP_PROVIDER_ID] left join clarity..ZC_PRACTICE_NAME zn on ser.PRACTICE_NAME_C=zn.PRACTICE_NAME_C where fu.ED_FUP_CONTACT is not null and c.phen is not null and c.Site_ID <> -99999 order by c.Research_ID -- Data - ED Care Team, providers only-- select 'Care_teams (ED Care Team, providers only)' select c.Site_ID, c.Research_ID --,c.PAT_MRN_ID, c.PAT_ENC_CSN_ID ,c.Arrival_DT ,c.Arrival_Hr ,case when tt.TR_TEAM_ID is not null then tt.TR_TEAM_ID when c.BILL_ATTEND_PROV_ID is not null then c.BILL_ATTEND_PROV_ID else '-99999' end Prov_ID --,s.PROV_NAME ,case when s.CLINICIAN_TITLE is not null then s.CLINICIAN_TITLE when s1.CLINICIAN_TITLE is not null then s1.CLINICIAN_TITLE else '-99999' end CLINICIAN_TITLE --,tt.TR_TEAM_REL_C ,case when s.PROV_NAME like '%team' then 'Consult Team' when tt.NAME is not null then tt.NAME when tt.NAME is null then 'Attending' else '-99999' end Team_relation ,c.BILL_ATTEND_PROV_ID --,tt.TR_TEAM_SPEC_C --,zs.NAME Specialty ,case when s.IS_RESIDENT is null then 2 when s.IS_RESIDENT = 'Y' then 1 when s.IS_RESIDENT = 'N' then 2 else 2 end IS_RESIDENT from #RND c left join (select t.*, zr.name from treatment_team t join (select * from ZC_TRTMT_TEAM_REL where name in ('Attending', 'APRN', 'covering provider', 'fellow', 'Licensed Practical Nurse', 'Midlevel', 'Nurse Practitioner', 'Physician', 'Physician Assistant', 'Resident') ) zr on t.TR_TEAM_REL_C=zr.TRTMNT_TEAM_REL_C ) tt on tt.PAT_ENC_CSN_ID=c.PAT_ENC_CSN_ID left join ZC_SPECIALTY zs on tt.TR_TEAM_SPEC_C=zs.SPECIALTY_C left join CLARITY_SER s on tt.TR_TEAM_ID=s.PROV_ID left join clarity..PAT_ENC_HSP hs on c.PAT_ENC_CSN_ID=hs.PAT_ENC_CSN_ID left join clarity..clarity_ser s1 on hs.BILL_ATTEND_PROV_ID=s1.PROV_ID where c.phen is not null and c.Site_ID <> -99999 order by c.Research_ID -- Data - ED Diagnosis -- select 'ED_Dx (ED Diagnosis)' select c.Site_ID, c.Research_ID --,c.PAT_MRN_ID, c.PAT_ENC_CSN_ID ,c.Arrival_DT ,c.Arrival_Hr -- ,c.Discharge_DT ,case when edg.CURRENT_ICD10_LIST is not null then edg.CURRENT_ICD10_LIST when edg.DIAGNOSIS_CODE is not null then edg.DIAGNOSIS_CODE else '-99999' end ED_diagnosis_code ,edg.DX_NAME ED_DX_name from clarity..PAT_ENC_DX dx join #RND c on dx.PAT_ENC_CSN_ID=c.PAT_ENC_CSN_ID left join clarity..CLARITY_EDG edg on dx.DX_ID=edg.DX_ID where c.phen is not null and c.Site_ID <> -99999 order by c.Research_ID -- Data - All PMH -- select 'PMH' SELECT ed.Site_ID, ed.Research_ID --,ed.PAT_MRN_ID, ed.PAT_ENC_CSN_ID ,ed.Arrival_DT ,ed.Arrival_Hr ,case when e.CURRENT_ICD10_LIST is not null then e.CURRENT_ICD10_LIST when e.DIAGNOSIS_CODE is not null then e.DIAGNOSIS_CODE when e.CURRENT_ICD9_LIST is null then e.CURRENT_ICD9_LIST else '-99999' end PMH_Diagnosis_Code ,e.DX_NAME PMH_DX_NAME --,mh.[COMMENTS] ,case when mh.[COMMENTS] like '%opiod%' or mh.[COMMENTS] like '%oxyc%' or mh.[COMMENTS] like '%opiate%' or mh.[COMMENTS] like '%narcotic%' or mh.[COMMENTS] like '%vicodin%' or mh.[COMMENTS] like '%norco%' or mh.[COMMENTS] like '%heroine%' or mh.[COMMENTS] like '%iatrogenic narcotic dependence%' or mh.[COMMENTS] like '%opiate hypopnea%' or mh.[COMMENTS] like '%opiate toxicity%' or mh.[COMMENTS] like '%narcotic seeking%' then 1 else 2 end PMH_comments_OUD ,datediff(dd, (select p.Trial_Start_Dt from #Param p), mh.[CONTACT_DATE]) PMH_contact_dt FROM #RND ed left join [Clarity]..[MEDICAL_HX] mh on mh.PAT_ID=ed.pat_id left join clarity..CLARITY_EDG e on mh.DX_ID=e.DX_ID where cast(mh.CONTACT_DATE as date) <= cast(ed.ADT_ARRIVAL_TIME as date) and ed.phen is not null and ed.Site_ID <> -99999 order by ed.Research_ID -- Data - Past Social history -- select 'Social_Hx (past social history)' select c.Site_ID, c.Research_ID ,c.Arrival_DT, c.Arrival_Hr --,c.PAT_MRN_ID, c.PAT_ENC_CSN_ID ,p.ILL_DRUG_USER_C ILL_DRUG_USER --,case when p.ILLICIT_DRUG_FREQ is null then 0 else cast(p.ILLICIT_DRUG_FREQ as numeric) end ILLICIT_DRUG_FREQ ,case when p.IV_DRUG_USER_YN = 'N' then 2 when p.IV_DRUG_USER_YN = 'Y' then 1 end IV_DRUG_USER ,p.SHx_Contact_Dt from #RND c left join #PS p on c.PAT_ID=p.PAT_ID where (p.SHx_Contact_Dt > -90 -- limit it to entries within 90 days of arrival and p.SHx_Contact_Dt < c.Arrival_DT) order by c.Research_ID -- Data - reasons for visit -- select 'ED_CC (Reasons for visit)' select c.Site_ID, c.Research_ID --,c.PAT_MRN_ID, c.PAT_ENC_CSN_ID ,c.Arrival_DT ,c.Arrival_Hr ,rv.ENC_REASON_ID ,r.REASON_VISIT_NAME ENC_reason_name ,rv.LINE ENC_reason_line from clarity..PAT_ENC_RSN_VISIT rv join #RND c on rv.PAT_ENC_CSN_ID=c.PAT_ENC_CSN_ID left join clarity..CL_RSN_FOR_VISIT r on rv.ENC_REASON_ID=r.REASON_VISIT_ID where rv.LINE is not null order by c.Research_ID -- Data - vitals -- select 'ED_Vitals (vitals)' select c.Site_ID, c.Research_ID --,c.PAT_MRN_ID, c.PAT_ENC_CSN_ID ,c.Arrival_DT ,c.Arrival_Hr ,case when e.BP_SYSTOLIC is not null then e.BP_SYSTOLIC else -99999 end BP_SYSTOLIC ,case when e.BP_DIASTOLIC is not null then e.BP_DIASTOLIC else -99999 end BP_DIASTOLIC ,case when e.PULSE is not null then e.PULSE else -99999 end PULSE ,case when e.RESPIRATIONS is not null then e.RESPIRATIONS else -99999 end RESPIRATIONS ,case when e.TEMPERATURE is not null then e.TEMPERATURE else -99999 end TEMPERATURE from clarity..pat_enc e join #RND c on e.PAT_ENC_CSN_ID=c.PAT_ENC_CSN_ID order by c.Research_ID -- Data - all opiate labs -- select 'Labs (all opiate labs)' select c.Site_ID, c.Research_ID --,c.PAT_MRN_ID, c.PAT_ENC_CSN_ID ,c.Arrival_DT ,c.Arrival_Hr ,cc.COMMON_NAME Lab_COMMON_NAME --,r.ORD_NUM_VALUE ,r.ORD_VALUE Lab_Value ,case when r.COMPONENT_COMMENT is not null then r.COMPONENT_COMMENT else '-99999' end Lab_Comment --,r.RESULT_DATE --,datediff(dd, (select Trial_Start_Dt from #Param), r.RESULT_DATE) Result_Dt from #OpiateResults r left join CLARITY_COMPONENT cc on r.COMPONENT_ID=cc.COMPONENT_ID join #RND c on r.PAT_ENC_CSN_ID=c.PAT_ENC_CSN_ID order by c.Research_ID -- Data - ED meds prescribed -- select 'ED_Meds_Rx (ED meds prescribed)' SELECT --distinct c.Site_ID, c.Research_ID --,med.ORDER_MED_ID --,c.PAT_MRN_ID, c.PAT_ENC_CSN_ID ,c.Arrival_DT ,c.Arrival_Hr ,datediff(dd, (select Trial_Start_Dt from #Param), med.ORDER_INST) ED_Rx_Ordering_dt --,datediff(dd, (select Trial_Start_Dt from #Param), med.DISCON_TIME) Discontinue_Dt ,zc.NAME ED_Rx_order_class_name --,m.GENERIC_NAME ,med.[DESCRIPTION] ED_Rx_Name ----,med.[SIG] ,case when m.STRENGTH is not null then m.STRENGTH else '-99999' end ED_Rx_strength ,case when med.[QUANTITY] is not null then med.[QUANTITY] else '-99999' end ED_Rx_QUANTITY ,case when med.[QUANTITY] is not null then left(med.quantity,charindex(' ',med.quantity)-1) else '-99999' end ED_Rx_Quant_Numb ,case when med.[QUANTITY] is not null then right(med.QUANTITY,len(med.quantity)-CHARINDEX(' ',med.quantity)) else '-99999' end ED_Rx_Quant_type ,case when med.[REFILLS] is not null then med.[REFILLS] else '-99999' end ED_Rx_REFILLS ,case when med.MED_PRESC_PROV_ID is not null then med.MED_PRESC_PROV_ID else '-99999' end ED_Rx_PRESC_PROV_ID ,case when med.ORD_PROV_ID is not null then med.ORD_PROV_ID else '-99999' end ED_Rx_ORD_PROV_ID FROM [Clarity]..[ORDER_MED] med join #RND c on med.PAT_ENC_CSN_ID=c.PAT_ENC_CSN_ID -- ?duplicate left join clarity..CLARITY_MEDICATION m on med.MEDICATION_ID=m.MEDICATION_ID left join Clarity..ZC_ORDER_CLASS zc on med.ORDER_CLASS_C=zc.ORDER_CLASS_C left join clarity..CLARITY_SER s on med.ORD_PROV_ID=s.PROV_ID where (med.[ORDER_STATUS_C] in (2, 5, 11) or med.[ORDER_STATUS_C] is null) --null=historical, 1-pending, 2-sent, 3-resulted, 4-canceled, 5-completed, 8-suspend, 9-discontinued, 11-dispensed and med.ORDERING_MODE_C=1 and m.GENERIC_NAME <> 'MISC%' order by c.Research_ID -- Data - Bup med prescribed in ED -- select 'BUP_Rx (Bup med prescribed in ED)' SELECT --distinct --Case when c.Phen is not null then c.Phen Else 'Neg' end OUD, c.Site_ID, c.Research_ID --,med.ORDER_MED_ID --,c.PAT_MRN_ID, c.PAT_ENC_CSN_ID ,c.Arrival_DT ,c.Arrival_Hr ,datediff(dd, (select Trial_Start_Dt from #Param), med.ORDER_INST) BUP_Rx_Ordering_dt --,datediff(dd, (select Trial_Start_Dt from #Param), med.DISCON_TIME) Discontinue_Dt --,zc.NAME order_class_name --,m.GENERIC_NAME ,med.[DESCRIPTION] BUP_Rx_Description ,case when med.[SIG] is not null then med.[SIG] else '-99999' end BUP_Rx_sig ,case when m.STRENGTH is not null then m.STRENGTH else '-99999' end BUP_Rx_strength ,case when med.[QUANTITY] is not null then med.[QUANTITY] else '-99999' end BUP_Rx_QUANTITY ,case when med.[QUANTITY] is not null then left(med.quantity,charindex(' ',med.quantity)-1) else -99999 end BUP_Rx_QUANTITY_Numb ,case when med.[QUANTITY] is not null then right(med.QUANTITY,len(med.quantity)-CHARINDEX(' ',med.quantity)) else '-99999' end BUP_Rx_QUANTITY_type ,case when med.[REFILLS] is not null then med.[REFILLS] else '-99999' end BUP_Rx_REFILLS ,case when med.MED_PRESC_PROV_ID is not null then med.MED_PRESC_PROV_ID else '-99999' end BUP_Rx_AUTHRZING_PROV_ID ,case when med.ORD_PROV_ID is not null then med.ORD_PROV_ID else '-99999' end BUP_Rx_ORD_PROV_ID --,s.PROV_NAME BUP_Rx_ORD_PROV --,med.ORDER_CLASS_C --,med.ORDER_STATUS_C FROM [Clarity]..[ORDER_MED] med join #RND c on med.PAT_ENC_CSN_ID=c.PAT_ENC_CSN_ID -- ?duplicate left join clarity..CLARITY_MEDICATION m on med.MEDICATION_ID=m.MEDICATION_ID left join Clarity..ZC_ORDER_CLASS zc on med.ORDER_CLASS_C=zc.ORDER_CLASS_C left join clarity..CLARITY_SER s on med.ORD_PROV_ID=s.PROV_ID where (med.[ORDER_STATUS_C] in (2, 5, 11) or med.[ORDER_STATUS_C] is null) --null=historical, 1-pending, 2-sent, 3-resulted, 4-canceled, 5-completed, 8-suspend, 9-discontinued, 11-dispensed and med.ORDERING_MODE_C=1 and (m.GENERIC_NAME like 'suboxone%' or m.GENERIC_NAME like 'buprenorphine%') order by c.Research_ID --**************** Data - PTA meds ***************-- select 'PTA_Meds (PTA meds)' select p.Site_ID, p.Research_ID --,c.PAT_MRN_ID, p.PAT_ENC_CSN_ID ,p.Arrival_DT ,p.Arrival_Hr --,p.GENERIC_NAME ,p.DESCRIPTION PTA_Med_Name --,p.SIG ,case when p.QUANTITY is not null then p.QUANTITY else '-99999' end PTA_med_QUANTITY ,case when p.Quant_Numb is not null then p.Quant_Numb else '-99999' end PTA_med_Quant_Numb ,case when p.Quant_type is not null then p.Quant_type else '-99999' end PTA_med_Quant_type ,case when p.REFILLS is not null then p.REFILLS else '-99999' end PTA_med_REFILLS ,p.ORDER_INST_DT PTA_med_ORDER_INST_DT --,p.order_class_name PTA_med_order_class_name from #PTAMedsMin p join #RND c on p.PAT_ENC_CSN_ID=c.PAT_ENC_CSN_ID order by c.Research_ID -- Data - Meds given in ED -- -- select 'ED_meds (Meds given in ED)' select --Case when c.Phen is not null then c.Phen Else 'Neg' end OUD, c.Site_ID, c.Research_ID --,c.PAT_MRN_ID, c.PAT_ENC_CSN_ID ,c.Arrival_DT ,c.Arrival_Hr ,mar.LINE ED_med_line ,datediff(dd, (select Trial_Start_Dt from #Param), med.ORDER_INST) ED_med_Ordering_Dt ,datediff(HOUR, c.ADT_ARRIVAL_TIME, mar.TAKEN_TIME) ED_Med_time --,m.GENERIC_NAME ,med.[DESCRIPTION] ED_Med_description ,case when m.STRENGTH is not null then m.STRENGTH else '-99999' end ED_med_strength ,case when med.MED_PRESC_PROV_ID is not null then med.MED_PRESC_PROV_ID else '-99999' end ED_MED_PRESC_PROV_ID ,case when med.ORD_PROV_ID is not null then med.ORD_PROV_ID else '-99999' end ED_med_ORD_PROV_ID ,zr.NAME ED_med_MAR_action from clarity.. MAR_ADMIN_INFO mar join #RND c on mar.MAR_ENC_CSN=c.PAT_ENC_CSN_ID join [Clarity]..[ORDER_MED] med on mar.ORDER_MED_ID=med.ORDER_MED_ID left join clarity..CLARITY_MEDICATION m on med.MEDICATION_ID=m.MEDICATION_ID left join ZC_MAR_RSLT zr on mar.MAR_ACTION_C=zr.RESULT_C where mar.MAR_ACTION_C not in (2,3,4,98,99,100) -- 2-missed, 3-refused, 4-canceled entry, 98-pending, 99-automatically held, 100-due order by c.Research_ID -- Data - Bup given in ED -- select 'BUP_in_ED (Bup given in ED)' select --Case when c.Phen is not null then c.Phen Else 'Neg' end OUD, c.Site_ID, c.Research_ID --,c.PAT_MRN_ID, c.PAT_ENC_CSN_ID ,c.Arrival_DT ,c.Arrival_Hr ,mar.LINE ED_BUP_line ,datediff(dd, (select Trial_Start_Dt from #Param), med.ORDER_INST) ED_BUP_Ordering_Dt ,datediff(hh, c.ADT_ARRIVAL_TIME, mar.TAKEN_TIME) ED_BUP_time --,m.GENERIC_NAME ,case when med.[DESCRIPTION] is not null then med.[DESCRIPTION] else '-99999' end ED_BUP_Description ,case when m.STRENGTH is not null then m.STRENGTH else '-99999' end ED_BUP_strength ,case when med.MED_PRESC_PROV_ID is not null then med.MED_PRESC_PROV_ID else '-99999' end ED_BUP_PRESC_PROV_ID --,sp.PROV_NAME ED_Bup_Presc_Prov ,case when med.ORD_PROV_ID is not null then med.ORD_PROV_ID else '-99999' end ED_BUP_ORD_PROV_ID --,so.PROV_NAME ED_Bup_Ord_Prov ,zr.NAME ED_BUP_MAR_action from clarity.. MAR_ADMIN_INFO mar join #RND c on mar.MAR_ENC_CSN=c.PAT_ENC_CSN_ID join [Clarity]..[ORDER_MED] med on mar.ORDER_MED_ID=med.ORDER_MED_ID left join clarity..CLARITY_MEDICATION m on med.MEDICATION_ID=m.MEDICATION_ID left join clarity..clarity_ser sp on med.MED_PRESC_PROV_ID=sp.PROV_ID left join clarity..clarity_ser so on med.ORD_PROV_ID=so.PROV_ID left join ZC_MAR_RSLT zr on mar.MAR_ACTION_C=zr.RESULT_C where mar.MAR_ACTION_C not in (2,3,4,98,99,100) -- 2-missed, 3-refused, 4-canceled entry, 98-pending, 99-automatically held, 100-due and (m.GENERIC_NAME like 'suboxone%' or m.GENERIC_NAME like 'buprenorphine%') order by c.Research_ID -- Data - Naloxone given in ED -- select 'Naloxone_in_ED (Naloxone given in ED)' select --Case when c.Phen is not null then c.Phen Else 'Neg' end OUD, c.Site_ID, c.Research_ID --,c.PAT_MRN_ID, c.PAT_ENC_CSN_ID ,c.Arrival_DT ,c.Arrival_Hr ,mar.LINE ED_Nal_line ,datediff(dd, (select Trial_Start_Dt from #Param), med.ORDER_INST) ED_Nal_Ordering_Dt ,datediff(hh, c.ADT_ARRIVAL_TIME, mar.TAKEN_TIME) ED_Nal_time --,m.GENERIC_NAME --,med.ORDER_MED_ID ,med.[DESCRIPTION] ED_Nal_Description ,case when med.STRENGTH is not null then med.STRENGTH else '-99999' end ED_Nal_strength ,med.MED_PRESC_PROV_ID ED_Nal_PRESC_PROV_ID --,med.ORD_PROV_ID ED_Nal_ORD_PROV_ID ,zr.NAME ED_Nal_MAR_action --,med.PROV_NAME from clarity.. MAR_ADMIN_INFO mar join #RND c on mar.MAR_ENC_CSN=c.PAT_ENC_CSN_ID join #Nx_med med on mar.ORDER_MED_ID=med.ORDER_MED_ID left join ZC_MAR_RSLT zr on mar.MAR_ACTION_C=zr.RESULT_C where (mar.MAR_ACTION_C not in (2,3,4,98,99,100)) -- 2-missed, 3-refused, 4-canceled entry, 98-pending, 99-automatically held, 100-due and (med.GenericName like 'naloxone%') and med.IN_Outpatient = 'inpatient' order by c.Research_ID