(SELECT 'Note: min value is 0.1%' AS '', COUNT(fin.demographic_no) AS "Total {PREVENTION}",
IF ( COUNT(fin.demographic_no) > 0, ROUND( SUM( CASE WHEN fin.val = "abnormal" THEN 1 ELSE 0 END ) * 100 / COUNT(fin.demographic_no), 1 ), "0" ) AS "Abnormal %"
FROM (
SELECT d.demographic_no, ABN.val
FROM demographic d
INNER JOIN preventions p
ON ( d.demographic_no = p.demographic_no )
INNER JOIN preventionsExt pe
ON ( p.id = pe.prevention_id )
LEFT JOIN (
SELECT prevention_id, val
FROM preventionsExt
WHERE keyval LIKE "result"
AND val LIKE "abnormal"
) ABN
ON (ABN.prevention_id = p.id )
WHERE p.prevention_type LIKE "{PREVENTION}"
AND d.patient_status LIKE "AC"
AND p.deleted = 0
AND DATE( p.prevention_date ) BETWEEN DATE("{FROM}") AND DATE("{TO}")
GROUP BY pe.prevention_id HAVING COUNT( pe.prevention_id ) > -1
) fin )
UNION (
SELECT
p.prevention_type,
pe.val,
CONCAT( '', d.last_name, ', ', d.first_name, '' )
FROM demographic d
INNER JOIN preventions p
ON ( d.demographic_no = p.demographic_no )
INNER JOIN preventionsExt pe
ON ( p.id = pe.prevention_id )
WHERE p.prevention_type LIKE '{PREVENTION}'
AND d.patient_status LIKE "AC"
AND pe.keyval LIKE "result"
AND pe.val LIKE "{RESULT}"
AND p.deleted = 0
AND DATE( p.prevention_date ) BETWEEN DATE("{FROM}") AND DATE("{TO}")
ORDER BY pe.val);
All
SELECT p.prevention_type, p.prevention_type
FROM preventions p
GROUP BY p.prevention_type HAVING COUNT(p.prevention_type) > -1;
- All -
Abnormal
Normal
Pending