query ตรวจแล็ป(LAB) OPD

select
count(distinct lh.vn)
from lab_head lh
JOIN patient p ON p.hn = lh.hn
left outer join lab_order lo on lo.lab_order_number=lh.lab_order_number
left outer join lab_items l on l.lab_items_code=lo.lab_items_code
-- left outer join doctor d on d.code=lh.doctor_code
where lh.order_date between"2024-10-01" and "2025-09-30"  and lo.lab_items_code in (1174,1175)
AND (DATEDIFF(lh.order_date, p.birthday) / 365.25) BETWEEN 0.5 AND 15
AND lo.lab_order_result IN ( ' Positive', 'Positive','positive','+','weak positive')
-- and d.position_id="1"
-- group by year(lh.order_date),month(lh.order_date),lh.doctor_code,lo.lab_items_code
-- order by year(lh.order_date) desc

select
-- aa.an,lh.hn,concat(pt.pname,pt.fname,' ',pt.lname) ptname
-- ,lh.department,lh.order_date,lh.lab_order_number,aa.bedno
-- lo.*
count(distinct aa.an)
from lab_head lh
left outer join lab_order lo on lo.lab_order_number=lh.lab_order_number
left join iptadm aa on lh.vn=aa.an
left join patient pt on pt.hn=lh.hn
where lh.order_date between "2024-10-01" and "2025-09-30"
and lo.lab_items_code in (1174,1175)
AND lo.lab_order_result IN ('Positive')
and lh.department='IPD'

SELECT
COUNT(DISTINCT v.visit_code) AS 'visit'
-- result.*
FROM hos_lab_result result
INNER JOIN hos_lab lab ON result.labnumber = lab.labnumber
INNER JOIN hos_pt_visit v ON lab.visit_code = v.visit_code
INNER    JOIN hos_pt p ON p.hn = v.hn
 
WHERE result.lab_code IN ('0019900','0019901','0019902')
AND v.date_visit BETWEEN '2023-10-01' AND '2024-09-30'
AND v.ageyyymmdd BETWEEN '0000600' and '0150000'
AND result.lab_result = 'Positive'

SELECT
    COUNT(DISTINCT lab.an) AS 'visit'
FROM
    hos_lab_result result
INNER JOIN hos_lab lab ON
    result.labnumber = lab.labnumber
INNER JOIN ipd v ON
    lab.an = v.an
INNER JOIN hos_pt p ON
    v.hn = p.hn
WHERE
    lab.date_lab BETWEEN '2024-10-01' AND '2025-09-30'
    AND result.lab_result IS NOT NULL
    AND result.lab_code IN('0019900', '0019901', '0019902')
    AND result.lab_result NOT IN('Not Found', 'Negative')
    AND lab.an IS NOT NULL
    AND TIMESTAMPDIFF(
        MONTH,
        STR_TO_DATE(p.birthdate, '%Y-%m-%d'),
        CONCAT(
            DATE_FORMAT(v.datedsc, '%Y') - 543,
            '-',
            DATE_FORMAT(v.datedsc, '%m-%d')
        )
    ) BETWEEN 6 AND (15 * 12)