· 10 years ago · Sep 08, 2016, 05:32 PM
1-----denominator---
2DROP TABLE IF EXISTS universe;
3CREATE TEMPORARY TABLE universe AS
4SELECT enc.patientid, cast(enc.date as date)
5 FROM enc
6 INNER JOIN users ON users.uid = enc.patientID
7 INNER JOIN doctors ON doctors.doctorid = enc.resourceid
8 WHERE enc.deleteflag = 0
9 AND enc.enctype = 1
10 AND enc.status = 'CHK'
11 and users.status = 0
12 -- AND extract(YEAR FROM age({{measurement_period_start_date}}, users.ptdob)) >=12
13 AND extract(YEAR FROM age('2015-08-01', users.ptdob)) >= 12
14 -- AND enc.date BETWEEN {{measurement_period_start_date}} AND {{measurement_period_end_date}}
15 AND enc.date BETWEEN '2015-08-01' AND '2016-07-29'
16 AND doctors.speciality IN ('Family Medicine', 'Internal Medicine', 'Obstetrics & Gynecology', 'Pediatrics');
17CREATE INDEX index_universe_patientid ON universe(patientid);
18
19
20---positive PHQ-2----
21DROP TABLE IF EXISTS depression_pos_phq2;
22CREATE TEMPORARY TABLE depression_pos_phq2 AS
23select cast(enc.date as date) as apptdate, hpi.encounterid, enc.patientid,
24 ROW_NUMBER() OVER (PARTITION BY enc.patientid ORDER BY date) as row,
25case when structhpi.detailid in ('4', '5') and structhpi.value = 'Yes' then 'Positive' else 'Negative' end as Score
26from hpi
27 join structhpi on structhpi.encounterid = hpi.encounterid
28 join enc on enc.encounterid = hpi.encounterid
29 where
30 -- hpi.encounterid = '2051361'
31hpi.hpiid = '250273'
32and hpi.propid = '18618'
33/* and ( (structhpi.detailid = '4'
34and structhpi.value = 'Yes')
35OR
36 (structhpi.detailid = '5'
37and structhpi.value = 'Yes'))*/
38and enc.date BETWEEN '2015-08-01' AND '2016-07-29';
39CREATE INDEX index_depression_pos_phq2 on depression_pos_phq2(patientid);
40--------------------
41
42---positive PHQ-9----
43DROP TABLE IF EXISTS dep_pos_phq9;
44CREATE TEMPORARY TABLE dep_pos_phq9 AS
45select cast(enc.date as date) as apptdate, hpi.encounterid, enc.patientid, structhpi.value,
46 ROW_NUMBER() OVER (PARTITION BY enc.patientid ORDER BY date) as row,
47 case WHEN cast(structhpi.value as INT) > 5 THEN 1 ELSE 0 END AS score
48
49from hpi
50 join structhpi on structhpi.encounterid = hpi.encounterid
51 join enc on enc.encounterid = hpi.encounterid
52 where
53-- hpi.encounterid = '2051361' and
54 hpi.hpiid = '250273'
55and structhpi.value != 'NaN'
56and hpi.propid = '18619'
57 AND structhpi.detailid = '15'
58and enc.date BETWEEN '2015-08-01' AND '2016-07-29';
59--and structhpi.value = 'NaN'
60CREATE INDEX index_dep_pos_phq9 on dep_pos_phq9(patientid);
61--------------------
62
63---intervention: mh referral----
64DROP TABLE IF EXISTS dep_intervention_ref;
65CREATE TEMPORARY TABLE dep_intervention_ref AS
66SELECT CAST(enc.date as date) as ApptDate,
67ROW_NUMBER() OVER (PARTITION BY enc.patientid ORDER BY enc.date) as row,
68 enc.patientid, items.itemname
69
70 FROM enc
71 INNER JOIN labdata ON enc.encounterid = labdata.encounterid
72 INNER JOIN items ON items.itemid = labdata.itemid
73
74 WHERE
75 items.itemname LIKE 'MH%'
76-- AND enc.date BETWEEN {{measurement_period_start_date}} AND {{measurement_period_end_date}}
77 and enc.date BETWEEN '2015-08-01' AND '2016-07-29'
78 AND enc.deleteflag = 0
79 AND labdata.deleteflag= 0;
80CREATE INDEX index_dep_intervention_ref on dep_intervention_ref(patientid);
81--------------------
82
83---intervention: rx----
84DROP TABLE IF EXISTS dep_intervention_rx;
85CREATE TEMPORARY TABLE dep_intervention_rx AS
86SELECT CAST(enc.date as date) as ApptDate,
87ROW_NUMBER() OVER (PARTITION BY enc.patientid ORDER BY enc.date) as row,
88 enc.patientid, items.itemname as med
89
90 FROM enc
91 INNER JOIN oldrxmain on oldrxmain.encounterid = enc.encounterid
92 inner join items on items.itemid = oldrxmain.itemid AND items.deleteflag = 0
93 INNER JOIN groupdetails ON groupdetails.memberid = oldrxmain.itemid
94 INNER JOIN groupcodes ON groupcodes.id = groupdetails.groupid
95 WHERE
96 groupcodes.name = 'Antidepressants'
97 -- AND enc.date BETWEEN {{measurement_period_start_date}} AND {{measurement_period_end_date}}
98 and enc.date BETWEEN '2015-08-01' AND '2016-07-29'
99 AND enc.deleteflag = 0;
100 CREATE INDEX index_dep_intervention_rx on dep_intervention_rx(patientid);
101--------------------
102
103
104select distinct patients.pid as patient_id,
105 patients.controlno,
106 pcg.primaryservicelocation as location_id,
107 pcg.uid as provider_id,
108COALESCE('Screened: ' || TO_CHAR(depression_pos_phq2.apptdate, 'YYYY-MM-DD') || 'PHQ-2 Score: ' || coalesce(depression_pos_phq2.Score) || ', ' || 'PHQ-9 Score: ' || coalesce(dep_pos_phq9.value, 'Not done') || ', ' || 'Referral: ' || COALESCE(dep_intervention_ref.itemname, 'None') || ', ' || 'Rx: ' || COALESCE(dep_intervention_rx.med, 'None'), 'not screened') AS measurement_value,
109
110------this part needs help---
111 CASE WHEN depression_pos_phq2.patientid is not NULL THEN --- were they screened?
112 case
113 when depression_pos_phq2.Score = 'Negative' or
114 (depression_pos_phq2.Score = 'Positive' and ---- if they screened positive
115 dep_pos_phq9.patientid is not null) THEN 1 else 0 END --- was a phq-9 done?
116 - - case when dep_pos_phq9.score = 1 AND (dep_intervention_ref.patientid, dep_intervention_rx.patientid) is not NULL THEN -- if phq-9 was positive, was there an intervention?
117 1 else 0 end
118 else 0 end as numerator,
119-----------------------
120 0 as exclusion
121 from patients
122 inner join universe on patients.pid = universe.patientid
123 inner join users pcg on pcg.uid = patients.rendprid
124 left join depression_pos_phq2 on depression_pos_phq2.patientid = patients.pid AND depression_pos_phq2.row = 1
125 Left join dep_pos_phq9 on dep_pos_phq9.patientid = depression_pos_phq2.patientid AND depression_pos_phq2.row = 1
126 left join dep_intervention_ref on dep_intervention_ref.patientid = dep_pos_phq9.patientid and dep_intervention_ref.row = 1
127 left join dep_intervention_rx on dep_intervention_rx.patientid = dep_pos_phq9.patientid and dep_intervention_rx.row = 1