CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_referrals`(IN `start_date` DATE, IN `end_date` DATE)
BEGIN
-- Referrals: the referral letters written in the period — where to, why, by whom.
SELECT DATE_FORMAT(r.date, '%d %b, %Y') AS `Date`, COALESCE(p.name, '') AS `Patient`, COALESCE(p.patient_id, '') AS `Patient ID`,
       COALESCE(r.`to`, '') AS `Referred to`, COALESCE(r.type, '') AS `Type`, COALESCE(r.description, '') AS `Reason`,
       COALESCE(u.name, '') AS `By`
FROM refferel_letters r
LEFT JOIN patients p ON p.id = r.patient_id
LEFT JOIN users u ON u.id = r.created_by
WHERE DATE(COALESCE(r.date, r.created_at)) BETWEEN start_date AND end_date
ORDER BY r.date;
END
