...
SELECT DISTINCT ON(r.id) r.id, r.created_date, r.modified_date, r.emergency_status, r.facility_name, r.district_name, r.region_name, r.country_name, r.facility_type_name, r.facility_operator_name, r.program_name, r.program_id, r.program_active_status, r.processing_period_name, r.processing_period_startdate, r.processing_period_enddate, r.processing_schedule_name,
sh.requisition_id, sh.status as statushistory_status, sh.created_date,
rd.due_days, rd.late_days, rd.country,
f.id, f.status as facility_status,
sp.programid, sp.startdate,
rgm.requisitiongroupid,
rgps.processingscheduleid,
saf.test_facility_name, saf.test_username, saf.test_program_id,
CASE
WHEN sh.created_date <= '2016-01-01' (r.processing_period_enddate::DATE + rd.due_days::INT) AND sh.status = 'SUBMITTEDAUTHORIZED' THEN 'On time'
WHEN sh.created_date > '2017-01-01' (r.processing_period_enddate::DATE + rd.due_days::INT + rd.late_days::INT) AND sh.status = 'SUBMITTEDAUTHORIZED' THEN 'Unscheduled'
WHEN sh.created_date < '2017-01-01' (r.processing_period_enddate::DATE + rd.due_days::INT + rd.late_days::INT) AND sh.created_date >= '2016-01-01' (r.processing_period_enddate::DATE + rd.due_days::INT) AND sh.status = 'SUBMITTEDAUTHORIZED' THEN 'Late'
WHEN sh.status != 'SUBMITTED' THEN ELSE 'Did not report'
ELSE NULL END as reporting_timeliness,
COUNT(DISTINCT r.facility_id) as total_facilities,
CASE WHEN r.program_active_status = True THEN 1 ELSE 0 END as expected_facilities
FROM requisitions r
JOIN requisitions_status_history sh ON r.id::VARCHAR = sh.requisition_id
JOIN reporting_dates rd ON r.country_name = rd.country
JOIN facilities f ON r.facility_id::VARCHAR = f.id::VARCHAR
JOIN supported_programs sp ON sp.facilityid = f.id
JOIN requisition_group_members rgm ON rgm.facilityid = f.id
JOIN requisition_group_program_schedules rgps ON rgps.requisitionGroupId = rgm.requisitionGroupId AND rgps.programId = sp.programId
WHERE sh.status NOT IN ('AUTHORIZEDRELEASED', 'RELEASED', 'APPROVED', 'SKIPPED')'APPROVED') AND saf.test_username = '{{ current_username() }}' AND saf.test_username IS NOT 'admin' AND saf.test_username IS NOT 'administrator'
GROUP BY r.id, r.created_date, r.modified_date, r.emergency_status, r.facility_name, r.district_name, r.region_name, r.country_name, r.facility_type_name, r.facility_operator_name, r.program_name, r.program_id, r.program_active_status, r.processing_period_name, r.processing_period_startdate, r.processing_period_enddate, r.processing_schedule_name,
sh.requisition_id, sh.statushistory_status, sh.created_date,
rd.due_days, rd.late_days, rd.country,
f.id, facility_status,
sp.programid, sp.startdate,
rgm.requisitiongroupid,
rgps.processingscheduleid,
saf.test_facility_name, saf.test_username, saf.test_program_id
ORDER BY r.id, sh.created_date DESC NULLS LAST
;
...