-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathconfig_dawg.env
225 lines (225 loc) · 22 KB
/
config_dawg.env
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
PAT_SQL="
/* CPR patients who have/had a scheduled visit in -2 to +7 days */
select distinct pd.PatientEpicId pat_id, case when pd.PatientMrnUwmc is not NULL then pd.PatientMrnUwmc when pd.PatientMrnHmc is not NULL then pd.PatientMrnHmc when pd.PatientMrnUwpn is not NULL then pd.PatientMrnUwpn end mrn,
pd.FirstName first_name, pd.LastName last_name, pd.BirthDateDurableKey birth_date, case when pd.SexAbbreviation = 'M' then 'male' when pd.SexAbbreviation = 'F' then 'female' when pd.SexAbbreviation in ('X', 'NB', 'I', 'ANL') then 'other' else 'unknown' end sex,
case when appt.pat_id is not NULL then concat(format(appt.min_dt, 'yyyy-MM-ddTHH:mm:ss'), right(left(current_timezone(), 10), 6)) else concat(format(dateadd(year, 20, getdate()), 'yyyy-MM-ddTHH:mm:ss'), right(left(current_timezone(), 10), 6)) end appt_dt
from MDW_DEEP.Dimensional.PatientDim pd
join MDW_DEEP.Dimensional.VisitFact aef on pd.PatientDurableKey = aef.PatientDurableKey
join MDW_DEEP.Dimensional.DepartmentDim dd on dd.DepartmentDurableKey = aef.DepartmentDurableKey
left join (select pd.PatientEpicId pat_id, min(aef.AppointmentInstant) min_dt
from MDW_DEEP.Dimensional.PatientDim pd
join MDW_DEEP.Dimensional.VisitFact aef on pd.PatientDurableKey = aef.PatientDurableKey
join MDW_DEEP.Dimensional.DepartmentDim dd on dd.DepartmentDurableKey = aef.DepartmentDurableKey
where aef.AppointmentInstant between getdate() and dateadd(day, 7, convert(date, getdate()))
and dd.DepartmentDurableKey = '894933337'
and aef.AppointmentStatus in ('Completed', 'Arrived', 'Scheduled')
group by pd.PatientEpicId) appt on appt.pat_id = pd.PatientEpicId
where dd.DepartmentDurableKey = '894933337'
and convert(date, aef.AppointmentDateDurableKey) between dateadd(day, -2, convert(date, getdate())) and dateadd(day, 7, convert(date, getdate()))
"
PROC_SQL="
/* List of the procedures of interest (since the beginning of PainTracker) for all CPR patients in appointment window */
select distinct pd.PatientEpicId pat_id, case when cpef.OrderStatusCode in (1, 2) then 'in-progress'
when cpef.OrderStatusCode in (3, 5) then 'completed'
else 'unknown'
end status_code, concat(cpef.ProcedureCode, case when cpef.Modifiers is not NULL then concat('-', cpef.Modifiers) else '' end) cpt_code, cpef.Description descr, format(cpef.ProcedureStartInstant, 'yyyy-MM-dd') proc_date,
concat(convert(nvarchar, cpef.SourceSystemId), '-', cpef.ProcedureCode, case when cpef.Modifiers is not NULL then concat('-', cpef.Modifiers) else '' end) uniq_id,
case when x.VisitDepartmentName is not NULL then
case when left(x.VisitDepartmentName, 2) = 'ZZ' then substring(x.VisitDepartmentName, 3, len(x.VisitDepartmentName) - 1) else x.VisitDepartmentName end
else
z.DEPARTMENT_NAME
end visit_dept,
case when x.DepartmentDurableKey is not NULL then x.DepartmentDurableKey else z.DEPARTMENT_ID end visit_dept_id,
case when x.EncounterEpicCsn is not NULL then x.EncounterEpicCsn else z.PAT_ENC_CSN_ID end enc_id,
case when x.EncounterType is not NULL then
case when x.EncounterType in ('Appointment', 'Office Visit', 'Clinical Support Visit', 'Occupational Health', 'Prenatal', 'Rehab Therapy', 'Tech Visit', 'Hospital Encounter') then '453701000124103'
when x.EncounterType in ('Telemedicine', 'Telephone', 'Phone Visit', 'E-Visit') then '185316007'
when x.EncounterType = 'No Show' then '410543007'
when x.EncounterType = 'Orders Only' then '270430005'
else '261665006'
end
else
case when z.NAME in ('Appointment', 'Office Visit', 'Clinical Support Visit', 'Occupational Health', 'Prenatal', 'Rehab Therapy', 'Tech Visit', 'Hospital Encounter') then '453701000124103'
when z.NAME in ('Telemedicine', 'Telephone', 'Phone Visit', 'E-Visit') then '185316007'
when z.NAME = 'No Show' then '410543007'
when z.NAME = 'Orders Only' then '270430005'
else '261665006'
end
end enc_type_code,
case when x.EncounterType is not NULL then
case when x.EncounterType in ('Appointment', 'Office Visit', 'Clinical Support Visit', 'Occupational Health', 'Prenatal', 'Rehab Therapy', 'Tech Visit', 'Hospital Encounter') then 'In-person encounter (procedure)'
when x.EncounterType in ('Telemedicine', 'Telephone', 'Phone Visit', 'E-Visit') then 'Indirect encounter (procedure)'
when x.EncounterType = 'No Show' then 'Did not attend (qualifier value)'
when x.EncounterType = 'Orders Only' then 'Provider-initiated encounter (procedure)'
else 'Unknown (qualifier value)'
end
else
case when z.NAME in ('Appointment', 'Office Visit', 'Clinical Support Visit', 'Occupational Health', 'Prenatal', 'Rehab Therapy', 'Tech Visit', 'Hospital Encounter') then 'In-person encounter (procedure)'
when z.NAME in ('Telemedicine', 'Telephone', 'Phone Visit', 'E-Visit') then 'Indirect encounter (procedure)'
when z.NAME = 'No Show' then 'Did not attend (qualifier value)'
when z.NAME = 'Orders Only' then 'Provider-initiated encounter (procedure)'
else 'Unknown (qualifier value)'
end
end enc_type_descr,
case when x.PatientClass is not NULL then
case when x.PatientClass = 'Inpatient' then 'IMP'
when x.PatientClass = 'Outpatient' then 'AMB'
when x.PatientClass = 'Observation' then 'OBSENC'
when x.PatientClass = 'Emergency' then 'EMER'
else 'UNK'
end
else 'UNK'
end enc_class_code,
case when x.PatientClass is not NULL then
case when x.PatientClass = 'Inpatient' then 'Inpatient encounter'
when x.PatientClass = 'Outpatient' then 'Ambulatory'
when x.PatientClass = 'Observation' then 'Observation encounter'
when x.PatientClass = 'Emergency' then 'Emergency'
else 'Unknown'
end
else 'Unknown'
end enc_class_descr
from MDW_DEEP.Dimensional.CombinedProcedureEventFact cpef
join MDW_DEEP.Dimensional.PatientDim pd on cpef.PatientDurableKey = pd.PatientDurableKey
left join (select case when v.VisitDurableKey is not NULL then v.VisitDurableKey else q.HospitalEncounterDurableKey end VisitDurableKey,
case when v.VisitDurableKey is not NULL then v.VisitDepartmentName else q.DepartmentName end VisitDepartmentName,
case when v.VisitDurableKey is not NULL then v.DepartmentDurableKey else q.DepartmentDurableKey end DepartmentDurableKey,
case when v.VisitDurableKey is not NULL then v.EncounterEpicCsn else q.EncounterEpicCsn end EncounterEpicCsn,
case when v.VisitDurableKey is not NULL then v.SourceEncounterType else q.SourceEncounterType end EncounterType,
case when v.VisitDurableKey is not NULL then v.PatientClass else q.PatientClass end PatientClass
from MDW_DEEP.Dimensional.CombinedEncounterFact cef
left join MDW_DEEP.Dimensional.VisitFact v on v.VisitDurableKey = cef.CombinedEncounterDurableKey
left join (select hef.*, dd.DepartmentName
from MDW_DEEP.Dimensional.HospitalEncounterFact hef
join MDW_DEEP.Dimensional.DepartmentDim dd on dd.DepartmentDurableKey = hef.DepartmentDurableKey
) q on q.HospitalEncounterDurableKey = cef.CombinedEncounterDurableKey
where cef.PatientDurableKey in (select distinct pd.PatientDurableKey
from MDW_DEEP.Dimensional.PatientDim pd
join MDW_DEEP.Dimensional.VisitFact aef on pd.PatientDurableKey = aef.PatientDurableKey
join MDW_DEEP.Dimensional.DepartmentDim dd on dd.DepartmentDurableKey = aef.DepartmentDurableKey
where dd.DepartmentDurableKey = '894933337'
and convert(date, aef.AppointmentDateDurableKey) between dateadd(day, -2, convert(date, getdate())) and dateadd(day, 7, convert(date, getdate()))
)
) x on x.VisitDurableKey = cpef.CombinedEncounterDurableKey
left join (select distinct op.ORDER_PROC_ID, d.DEPARTMENT_NAME, d.DEPARTMENT_ID, pe.PAT_ENC_CSN_ID, et.NAME
from uwDAL.clarity.ORDER_PROC op
join uwDAL.clarity.PAT_ENC pe on op.PAT_ENC_CSN_ID = pe.PAT_ENC_CSN_ID
join uwDAL_Clarity.dbo.CLARITY_DEP d on d.DEPARTMENT_ID = pe.DEPARTMENT_ID
join uwDAL_Clarity.dbo.ZC_DISP_ENC_TYPE et on et.DISP_ENC_TYPE_C = pe.ENC_TYPE_C
where pe.PAT_ID in (select distinct pd.PatientEpicId
from MDW_DEEP.Dimensional.PatientDim pd
join MDW_DEEP.Dimensional.VisitFact aef on pd.PatientDurableKey = aef.PatientDurableKey
join MDW_DEEP.Dimensional.DepartmentDim dd on dd.DepartmentDurableKey = aef.DepartmentDurableKey
where dd.DepartmentDurableKey = '894933337'
and convert(date, aef.AppointmentDateDurableKey) between dateadd(day, -2, convert(date, getdate())) and dateadd(day, 7, convert(date, getdate()))
)
) z on z.ORDER_PROC_ID = cpef.SourceSystemId
where cpef.ProcedureCodeSet in ('cp', 'cpt4')
and cpef.OrderStatusCode in (1, 2, 3, 5)
and cpef.ProcedureCode in ('1009020', '1009077', '1009065', '1009019', '1009032', '20552', '20553', '20526', '20550', '20551', '20560', '20561', '64490', '64491', '64492', '0213T', '0214T', '0215T', '64493', '64494', '64495', '0216T', '0217T', '0218T', '20600', '20604', '20605', '20606', '20610', '20611', '27096', '20552', '64451', '62321', '62320', '62325', '62324', '62323', '62322', '62327', '62326', '64479', '64480', '64483', '64484', '62323', '62322', '62318', '62319', '62273', '64400', '64999X', '64405', '64615', '64408', '64999Y', '64999Z', '64415', '64417', '64418', '64461', '64462', '64999V', '64420', '64421', '64486', '64488', '64425', '64425', '64430', '64445', '64447', '64449', '64454', '64455', '64450', '64505', '64999U', '64510', '64517', '64520', '64520', '64530', '96372', '96369', '96365', '96366', '64633', '64634', '64635', '64636', '64600', '64620', '64630', '64624', '64625', '64640', '64680', '64681', '63650', '63655', '63661', '63662', '63663', '63664', '63685', '63688', '95970', '95971', '95972', '95974', '95975', '64555', '64585', '95970', '95971', '95972', 'XFLNP', '77003', '77012', '76942', '76882', '76536', '99156', '99157')
and cpef.ProcedureStartInstant is not NULL
and convert(date, cpef.ProcedureStartInstant) between '2014-01-01' and convert(date, getdate())
and pd.PatientEpicId in (
select distinct pd.PatientEpicId
from MDW_DEEP.Dimensional.PatientDim pd
join MDW_DEEP.Dimensional.VisitFact aef on pd.PatientDurableKey = aef.PatientDurableKey
join MDW_DEEP.Dimensional.DepartmentDim dd on dd.DepartmentDurableKey = aef.DepartmentDurableKey
where dd.DepartmentDurableKey = '894933337'
and convert(date, aef.AppointmentDateDurableKey) between dateadd(day, -2, convert(date, getdate())) and dateadd(day, 7, convert(date, getdate()))
)
"
MEDS_SQL="
/* List of the medications of interest (for outpatient visits only within the last 2 years) for all CPR patients in appointment window */
select distinct pd.PatientEpicId pat_id, case when m.OrderStatus in ('Discontinued') or m.DiscontinuedInstant is not NULL then 'stopped'
when m.OrderStatus in ('Ordered', 'Sent') then 'active'
when m.OrderStatus in ('Suspend', 'Suspended') then 'on-hold'
when m.OrderStatus in ('Completed', 'Dispensed', 'Verified') then 'completed'
when m.OrderStatus in ('Deleted', 'Voided', 'Voided with Results') then 'entered-in-error'
when m.OrderStatus in ('Canceled', 'Canceled with Results') then 'cancelled'
when m.OrderStatus in ('Future', 'Incomplete', 'Pending Complete', 'Pending Verify') then 'draft'
else 'unknown'
end status_code, rc.RXNORM_CODE rxnorm_code, md.MedicationGenericName descr, format(m.OrderedInstant, 'yyyy-MM-dd') med_date, md.Strength strength, md.Route route, md.Form form,
case when m.Frequency is not NULL and m.Frequency <> '*Unspecified' then m.Frequency else '' end freq,
concat(md.Strength, ' ', md.Route, ' ', md.Form, ' ', case when m.Frequency is not NULL and m.Frequency <> '*Unspecified' then m.Frequency else '' end) dosage,
case when m.Quantity is not NULL then convert(nvarchar, convert(int, m.Quantity)) else 0 end quantity,
case when m.QuantityUnit is not NULL and m.QuantityUnit <> '*Unspecified' then m.QuantityUnit else '' end quantity_unit,
concat(convert(nvarchar, m.SourceSystemId), '-', rc.RXNORM_CODE) uniq_id, concat(pd2.ExternalName, case when pd2.ClinicianTitle is not NULL and pd2.ClinicianTitle <> '*Unspecified' then concat(', ', pd2.ClinicianTitle) else '' end) provider_full_name,
pd2.FirstName provider_first_name, pd2.LastName provider_last_name, pd2.Npi npi, pd2.ProviderEpicId provider_id,
case when left(x.VisitDepartmentName, 2) = 'ZZ' then substring(x.VisitDepartmentName, 3, len(x.VisitDepartmentName) - 1) else x.VisitDepartmentName end visit_dept,
x.DepartmentDurableKey visit_dept_id,
x.EncounterEpicCsn enc_id,
case when x.EncounterType in ('Appointment', 'Office Visit', 'Clinical Support Visit', 'Occupational Health', 'Prenatal', 'Rehab Therapy', 'Tech Visit', 'Hospital Encounter') then '453701000124103'
when x.EncounterType in ('Telemedicine', 'Telephone', 'Phone Visit', 'E-Visit') then '185316007'
when x.EncounterType = 'No Show' then '410543007'
when x.EncounterType = 'Orders Only' then '270430005'
else '261665006'
end enc_type_code,
case when x.EncounterType in ('Appointment', 'Office Visit', 'Clinical Support Visit', 'Occupational Health', 'Prenatal', 'Rehab Therapy', 'Tech Visit', 'Hospital Encounter') then 'In-person encounter (procedure)'
when x.EncounterType in ('Telemedicine', 'Telephone', 'Phone Visit', 'E-Visit') then 'Indirect encounter (procedure)'
when x.EncounterType = 'No Show' then 'Did not attend (qualifier value)'
when x.EncounterType = 'Orders Only' then 'Provider-initiated encounter (procedure)'
else 'Unknown (qualifier value)'
end enc_type_descr,
case when x.PatientClass = 'Inpatient' then 'IMP'
when x.PatientClass = 'Outpatient' then 'AMB'
when x.PatientClass = 'Observation' then 'OBSENC'
when x.PatientClass = 'Emergency' then 'EMER'
else 'UNK'
end enc_class_code,
case when x.PatientClass = 'Inpatient' then 'Inpatient encounter'
when x.PatientClass = 'Outpatient' then 'Ambulatory'
when x.PatientClass = 'Observation' then 'Observation encounter'
when x.PatientClass = 'Emergency' then 'Emergency'
else 'Unknown'
end enc_class_descr
from MDW_DEEP.Dimensional.CombinedMedicationOrderFact m
join MDW_DEEP.Dimensional.MedicationDim md on m.MedicationDurableKey = md.MedicationDurableKey
join uwDAL.clarity.CLARITY_MEDICATION cm on cm.MEDICATION_ID = md.MedicationEpicId
join uwDAL_Clarity.dbo.RXNORM_CODES rc on rc.MEDICATION_ID = cm.MEDICATION_ID
join MDW_DEEP.Dimensional.PatientDim pd on pd.PatientDurableKey = m.PatientDurableKey
left join MDW_DEEP.Dimensional.ProviderDim pd2 on pd2.ProviderDurableKey = m.OrderedByProviderDurableKey
left join (select case when v.VisitDurableKey is not NULL then v.VisitDurableKey else q.HospitalEncounterDurableKey end VisitDurableKey,
case when v.VisitDurableKey is not NULL then v.VisitDepartmentName else q.DepartmentName end VisitDepartmentName,
case when v.VisitDurableKey is not NULL then v.DepartmentDurableKey else q.DepartmentDurableKey end DepartmentDurableKey,
case when v.VisitDurableKey is not NULL then v.EncounterEpicCsn else q.EncounterEpicCsn end EncounterEpicCsn,
case when v.VisitDurableKey is not NULL then v.SourceEncounterType else q.SourceEncounterType end EncounterType,
case when v.VisitDurableKey is not NULL then v.PatientClass else q.PatientClass end PatientClass
from MDW_DEEP.Dimensional.CombinedEncounterFact cef
left join MDW_DEEP.Dimensional.VisitFact v on v.VisitDurableKey = cef.CombinedEncounterDurableKey
left join (select hef.*, dd.DepartmentName
from MDW_DEEP.Dimensional.HospitalEncounterFact hef
join MDW_DEEP.Dimensional.DepartmentDim dd on dd.DepartmentDurableKey = hef.DepartmentDurableKey
) q on q.HospitalEncounterDurableKey = cef.CombinedEncounterDurableKey
where cef.PatientDurableKey in (select distinct pd.PatientDurableKey
from MDW_DEEP.Dimensional.PatientDim pd
join MDW_DEEP.Dimensional.VisitFact aef on pd.PatientDurableKey = aef.PatientDurableKey
join MDW_DEEP.Dimensional.DepartmentDim dd on dd.DepartmentDurableKey = aef.DepartmentDurableKey
where dd.DepartmentDurableKey = '894933337'
and convert(date, aef.AppointmentDateDurableKey) between dateadd(day, -2, convert(date, getdate())) and dateadd(day, 7, convert(date, getdate()))
)
) x on x.VisitDurableKey = m.CombinedEncounterDurableKey
where pd.PatientEpicId in (
select distinct pd.PatientEpicId
from MDW_DEEP.Dimensional.PatientDim pd
join MDW_DEEP.Dimensional.VisitFact aef on pd.PatientDurableKey = aef.PatientDurableKey
join MDW_DEEP.Dimensional.DepartmentDim dd on dd.DepartmentDurableKey = aef.DepartmentDurableKey
where dd.DepartmentDurableKey = '894933337'
and convert(date, aef.AppointmentDateDurableKey) between dateadd(day, -2, convert(date, getdate())) and dateadd(day, 7, convert(date, getdate()))
)
and m.OrderedInstant is not NULL
and cm.MED_IS_CONFIGURED_C in (1, 1003)
and cm.RECORD_STATE is NULL
and rc.RXNORM_CODE in ('1191','140587','161','187832','25480','3264','332984','3355','35827','39786','41493','48937','5640','5781','588250','6130','6585','7258','72625',
'10689','1292','135775','141366','1653781','1712','1819','1841','1992','2101','21949','228783','23088','231049','238153','2598','2599','2670','279645','3322','3423','35827','3638','37418','4337','480','5489','56795','57528','596',
'6378','6470','6719','6754','6813','68503','7052','7238','7502','7531','7804','7814','73032','787390','88014')
and md.MedicationGenericName not in (
'Aspirin-Dipyridamole',
'dexAMETHasone Sodium Phosphate',
'Neomycin-Polymyxin-Dexameth',
'Tobramycin-dexAMETHasone',
'OnabotulinumtoxinA (Cosmetic)',
'Capsaicin-Cleansing Gel'
)
and convert(date, m.OrderedInstant) between dateadd(year, -2, convert(date, getdate())) and convert(date, getdate())
and (x.PatientClass in ('Outpatient') or x.EncounterType in ('Appointment', 'E-Visit', 'Office Visit', 'Orders Only', 'Phone Visit', 'Refill', 'Telemedicine', 'Telephone'))
"