NINS_CODE/bahmni_config/openmrs/apps/reports/sql/testCount.sql
travelershot 70dda814aa codepush
2024-12-12 22:37:39 +06:00

14 lines
881 B
SQL

SELECT DISTINCT
ts.name AS DDepartment,
t.name AS test,
count(r.id) AS total_count,
CASE WHEN t.id IN (SELECT test_id FROM test_result WHERE tst_rslt_type = 'D') THEN count(r1.id) ELSE NULL END AS positive,
CASE WHEN t.id IN (SELECT test_id FROM test_result WHERE tst_rslt_type = 'D') THEN count(r2.id) ELSE NULL END AS negative
FROM test_section ts
INNER JOIN test t ON ts.id = t.test_section_id AND t.is_active = 'Y'
LEFT OUTER JOIN analysis a ON t.id = a.test_id
LEFT OUTER JOIN result r ON a.id = r.analysis_id and cast(r.lastupdated as date) BETWEEN '%s' and '%s' and r.value != ''
LEFT OUTER JOIN result r1 ON r1.result_type = 'D' and r1.value != '' and r.id=r1.id and r1.abnormal=false
LEFT OUTER JOIN result r2 on r2.result_type = 'D' and r2.value != '' and r.id=r2.id and r2.abnormal=true
GROUP BY ts.name, t.name, t.id
order by ts.name;