CREATE DEFINER=CURRENT_USER PROCEDURE `rpt_ipd_summary`(IN `start_date` DATE, IN `end_date` DATE)
BEGIN
-- In-patients per room type: admissions and discharges in the period,
-- patient-days (nights in bed), beds, bed occupancy rate and average length
-- of stay of the patients discharged.
SELECT r.room_type AS `Room type`, r.beds AS `Beds`, r.admitted AS `Admissions`, r.discharged AS `Discharges`,
       r.in_now AS `In now`, r.patient_days AS `Patient-days`,
       CONCAT(FORMAT(100 * r.patient_days / NULLIF(r.beds * (DATEDIFF(end_date, start_date) + 1), 0), 1), ' %') AS `Occupancy`,
       IFNULL(FORMAT(r.alos, 1), '-') AS `Avg stay (days)`
FROM (
    SELECT COALESCE(rt.name, '(no type)') room_type,
           (SELECT IFNULL(SUM(GREATEST(IFNULL(r2.no_beds, 1), 1)), 0) FROM inp_rooms r2 WHERE r2.room_type = rt.id AND IFNULL(r2.status, 'enabled') = 'enabled') beds,
           SUM(COALESCE(ad.date_in, DATE(ad.date)) BETWEEN start_date AND end_date) admitted,
           SUM(ad.status = 'Discharged' AND ad.date_out BETWEEN start_date AND end_date) discharged,
           SUM(ad.status <> 'Discharged' AND COALESCE(ad.date_in, DATE(ad.date)) <= end_date) in_now,
           SUM(GREATEST(DATEDIFF(LEAST(COALESCE(IF(ad.status = 'Discharged', ad.date_out, NULL), DATE_ADD(end_date, INTERVAL 1 DAY)), DATE_ADD(end_date, INTERVAL 1 DAY)),
                                 GREATEST(COALESCE(ad.date_in, DATE(ad.date)), start_date)), 0)) patient_days,
           AVG(IF(ad.status = 'Discharged' AND ad.date_out BETWEEN start_date AND end_date, GREATEST(DATEDIFF(ad.date_out, COALESCE(ad.date_in, DATE(ad.date))), 1), NULL)) alos
    FROM admissions ad
    LEFT JOIN inp_rooms rm ON rm.id = ad.room
    LEFT JOIN inp_room_types rt ON rt.id = rm.room_type
    WHERE IFNULL(ad.status, '') NOT IN ('Cancel', 'Processing')
      AND COALESCE(ad.date_in, DATE(ad.date)) <= end_date
      AND (ad.status <> 'Discharged' OR ad.date_out >= start_date)
    GROUP BY rt.id, rt.name
) r
ORDER BY r.patient_days DESC;
END
