query RSV หมอเด็ก

SELECT
p.hn,
CONCAT(p.pname, p.fname, ' ', p.lname) AS ptname,
v.vstdate,
'OPD' AS pt_type,
dx.icd10 AS dx_code,
c.lab_items_code,
i.lab_items_name,
c.lab_order_result
FROM ovst v
JOIN patient p ON p.hn = v.hn
JOIN lab_head b ON b.vn = v.vn
JOIN lab_order c ON c.lab_order_number = b.lab_order_number
JOIN lab_items i ON i.lab_items_code = c.lab_items_code
LEFT JOIN ovstdiag dx ON dx.vn = v.vn AND dx.diagtype IN ('1','2')
WHERE
TIMESTAMPDIFF(YEAR, p.birthday, v.vstdate) BETWEEN 0 AND 10
AND YEAR(v.vstdate) + IF(MONTH(v.vstdate) >= 10, 544, 543) BETWEEN 2566 AND 2568
AND c.lab_order_result = 'Positive'
AND i.lab_items_name LIKE '%RSV%'
AND dx.icd10 IN ('B974','J205','J121')
GROUP BY v.vn, dx.icd10
ORDER BY v.vstdate

SELECT
p.hn,
CONCAT(p.pname, p.fname, ' ', p.lname) AS ptname,
ip.regdate AS vstdate,
'IPD' AS pt_type,
dx.icd10 AS dx_code,
c.lab_items_code,
i.lab_items_name,
c.lab_order_result
FROM ipt ip
JOIN patient p ON p.hn = ip.hn
JOIN lab_head b ON b.hn = ip.hn AND b.order_date BETWEEN ip.regdate AND IFNULL(ip.dchdate, CURRENT_DATE())
JOIN lab_order c ON c.lab_order_number = b.lab_order_number
JOIN lab_items i ON i.lab_items_code = c.lab_items_code
LEFT JOIN iptdiag dx ON dx.an = ip.an AND dx.diagtype IN ('1','2')
WHERE
TIMESTAMPDIFF(YEAR, p.birthday, ip.regdate) BETWEEN 0 AND 10
AND YEAR(ip.regdate) + IF(MONTH(ip.regdate) >= 10, 544, 543) BETWEEN 2566 AND 2568
AND c.lab_order_result = 'Positive'
AND i.lab_items_name LIKE '%RSV%'
AND dx.icd10 IN ('B974','J205','J121')
GROUP BY ip.an, dx.icd10
ORDER BY ip.regdate