query Hypoglycemia

-- hyper hypo IPD
SELECT
    ip.hn,
    CONCAT(p.pname, p.fname, ' ', p.lname) AS ptname,
    GROUP_CONCAT(DISTINCT ip.an ORDER BY ip.an ASC SEPARATOR ', ') AS associated_ans,
    MIN(ip.regdate) AS first_admit_date,
    MAX(ip.dchdate) AS last_discharge_date,
 
    GROUP_CONCAT(
        CONCAT(
            li.lab_items_name, ': ', lo.lab_order_result, ' (',
            CASE
                WHEN CAST(lo.lab_order_result AS DECIMAL) >= 116 THEN 'Hyperglycemia'
                WHEN CAST(lo.lab_order_result AS DECIMAL) <= 70 THEN 'Hypoglycemia'
                ELSE 'Normal'
            END, ')'
        )
        ORDER BY lh.order_date ASC, li.lab_items_name ASC
        SEPARATOR ' | '
    ) AS all_glucose_results_summary
FROM
    lab_head lh
JOIN
    lab_order lo ON lo.lab_order_number = lh.lab_order_number
JOIN
    lab_items li ON li.lab_items_code = lo.lab_items_code
JOIN
    ipt ip ON ip.hn = lh.hn AND lh.order_date BETWEEN ip.regdate AND IFNULL(ip.dchdate, CURRENT_DATE)
JOIN
    patient p ON p.hn = ip.hn
WHERE
    li.lab_items_code IN ('1', '777', '1293', '136', '229', '1024', '434')
    AND lo.lab_order_result NOT IN ('', '-', 'Negative')
GROUP BY
    ip.hn, p.pname, p.fname, p.lname
ORDER BY
    MIN(ip.regdate) DESC;