G82XmHXq

1----Kelli Ewing made this, 01.13.2017. "THIS PROCESS HAS BEEN AUTOMATED."
2
3-----denominator---
4DROP TABLE IF EXISTS universe;
5CREATE TEMPORARY TABLE universe AS
6SELECT enc.patientid, patients.controlno, pcg.uid, pcg.primaryservicelocation as facilityid
7,ROW_NUMBER() OVER(PARTITION BY patientid ORDER BY date DESC) AS row
8
9  FROM enc
10    INNER JOIN users ON users.uid = enc.patientID
11    INNER JOIN patients on patients.pid = enc.patientid
12    JOIN users pcg on pcg.uid = patients.rendprid
13  --  INNER JOIN doctors ON doctors.doctorid = enc.resourceid
14  WHERE enc.deleteflag = 0
15        AND enc.enctype = 1
16        AND enc.status = 'CHK'
17     and users.status = 0
18AND LTRIM(RTRIM(users.ulname)) NOT IN
19      ('Test', 'Tester', 'Test9', 'Test12', 'Test,New', 'Testing', 'Tester', 'Testest')
20      AND users.ulname != 'AAAA'
21   and patients.deceased = 0
22
23AND DATE {{measurement_period_end_date}} BETWEEN users.ptdob + INTERVAL '18 MONTHS' AND users.ptdob + INTERVAL '24 MONTHS'
24--AND current_date BETWEEN users.ptdob + INTERVAL '18 MONTHS' AND users.ptdob + INTERVAL '24 MONTHS'
25
26AND enc.date BETWEEN DATE {{measurement_period_end_date}} - INTERVAL '2 YEARS' AND {{measurement_period_end_date}};
27--AND enc.date >= (current_date - INTERVAL '2 years');
28     --   AND doctors.speciality IN ('Family Medicine', 'Internal Medicine', 'Obstetrics & Gynecology', 'Pediatrics')
29       -- GROUP BY enc.patientid, pcg.uid, pcg.primaryservicelocation;
30CREATE INDEX index_universe_patientid ON universe(patientid);
31
32-----numerator---
33DROP TABLE IF EXISTS dtap;
34CREATE TEMPORARY TABLE dtap AS
35SELECT
36  iz.patientid
37--,cast(iz.givendate as date) as izdate
38 ,MAX(givendate) OVER (PARTITION BY patientid) as MaxIZDate
39 ,iz.immunizationid
40  FROM
41    immunizations iz
42where iz.vaccinename ILIKE 'DTaP%'
43and iz.deleteflag = 0
44--and iz.patientid = '175206'
45;
46CREATE INDEX index_dtap_patientid ON dtap(patientid);
47
48
49/* {{final query}} */
50SELECT DISTINCT
51  universe.controlno,
52  universe.patientid as patient_id,
53  universe.uid as provider_id,
54  universe.facilityid as location_id,
55  COALESCE('Total:' || count(dtap.immunizationid)  ||  ' ' || ' Last DTaP: ' || to_char(dtap.MaxIZDate, 'YYYY-MM-DD'),
56           'No DTaP on File')
57            as measurement_value -- put date of screening if screened
58 --CASE WHEN dtap.patientid IS NOT NULL THEN 1 ELSE 0 END as numerator, -- compliant if screened in period
59  ,CASE WHEN COUNT(dtap.immunizationid) >= 4 THEN 1 ELSE 0 END AS numerator
60
61  ,0 as exclusion
62 from universe
63left JOIN dtap on dtap.patientid = universe.patientid
64where universe.row = 1
65GROUP BY universe.controlno
66,universe.patientid
67,universe.uid
68,universe.facilityid
69,dtap.MaxIZDate
70ORDER BY universe.patientid