Query Telemed และ Home ward (แยกตำบล)

SELECT
t.full_name AS 'address'
,COUNT(DISTINCT a.visit_code) as 'visit'
FROM hos_pt_visit a
INNER JOIN hos_pt c ON c.hn = a.hn
INNER JOIN hos_code_thaiaddress t ON CONCAT(
c.province,
c.amphur,
c.tambon
) = t.addressid
 
WHERE
a.doctor_room = '53'
and c.province = '40'
and c.amphur = '07'
-- AND a.date_visit BETWEEN '2024-10-01' AND '2025-09-30'
AND a.date_visit BETWEEN '2024-10-01' AND '2025-02-30'
 
GROUP BY t.addressid

SELECT
    t1.name AS 'จังหวัด',
    t2.name AS 'อำเภอ',
    t3.name AS 'ตำบล',
    COUNT(DISTINCT a.hn) AS 'จำนวนผู้ป่วย'
,COUNT(DISTINCT a.vn) AS 'จำนวน visit'
FROM
    ovst a
-- INNER JOIN ovstdiag dx ON dx.vn = a.vn
INNER JOIN patient pt ON pt.hn = a.hn
LEFT JOIN thaiaddress t1 ON t1.chwpart = pt.chwpart AND t1.amppart = '00' AND t1.tmbpart = '00'
LEFT JOIN thaiaddress t2 ON t2.chwpart = pt.chwpart AND t2.amppart = pt.amppart AND t2.tmbpart = '00'
LEFT JOIN thaiaddress t3 ON t3.chwpart = pt.chwpart AND t3.amppart = pt.amppart AND t3.tmbpart = pt.tmbpart
WHERE
    a.vstdate BETWEEN '2025-02-01' AND '2025-09-30'
    AND a.main_dep = '053'
        AND t1.name = 'ขอนแก่น'
        AND t2.name = 'น้ำพอง'
    AND t1.name IS NOT NULL
GROUP BY
    t1.name,
    t2.name,
    t3.name
ORDER BY
    จำนวนผู้ป่วย DESC

(
SELECT
t.full_name AS 'ที่อยู่'
,COUNT(*) AS 'จำนวน'
FROM
ipd a
INNER JOIN pat b ON b.an = a.an
INNER JOIN hos_pt c ON c.hn = b.hn
INNER JOIN hos_code_thaiaddress t ON CONCAT(
c.province,
c.amphur,
c.tambon
) = t.addressid
WHERE
b.ward = '20'
AND t.addressid = '400701'
)
UNION
(
SELECT
t.full_name AS 'ที่อยู่'
,COUNT(*) AS 'จำนวน'
FROM
ipd a
INNER JOIN pat b ON b.an = a.an
INNER JOIN hos_pt c ON c.hn = b.hn
INNER JOIN hos_code_thaiaddress t ON CONCAT(
c.province,
c.amphur,
c.tambon
) = t.addressid
WHERE
b.ward = '20'
AND t.addressid = '400702'
)
UNION
(
SELECT
t.full_name AS 'ที่อยู่'
,COUNT(*) AS 'จำนวน'
FROM
ipd a
INNER JOIN pat b ON b.an = a.an
INNER JOIN hos_pt c ON c.hn = b.hn
INNER JOIN hos_code_thaiaddress t ON CONCAT(
c.province,
c.amphur,
c.tambon
) = t.addressid
WHERE
b.ward = '20'
AND t.addressid = '400703'
)
UNION
(
SELECT
t.full_name AS 'ที่อยู่'
,COUNT(*) AS 'จำนวน'
FROM
ipd a
INNER JOIN pat b ON b.an = a.an
INNER JOIN hos_pt c ON c.hn = b.hn
INNER JOIN hos_code_thaiaddress t ON CONCAT(
c.province,
c.amphur,
c.tambon
) = t.addressid
WHERE
b.ward = '20'
AND t.addressid = '400704'
)
UNION
(
SELECT
t.full_name AS 'ที่อยู่'
,COUNT(*) AS 'จำนวน'
FROM
ipd a
INNER JOIN pat b ON b.an = a.an
INNER JOIN hos_pt c ON c.hn = b.hn
INNER JOIN hos_code_thaiaddress t ON CONCAT(
c.province,
c.amphur,
c.tambon
) = t.addressid
WHERE
b.ward = '20'
AND t.addressid = '400705'
)
UNION
(
SELECT
t.full_name AS 'ที่อยู่'
,COUNT(*) AS 'จำนวน'
FROM
ipd a
INNER JOIN pat b ON b.an = a.an
INNER JOIN hos_pt c ON c.hn = b.hn
INNER JOIN hos_code_thaiaddress t ON CONCAT(
c.province,
c.amphur,
c.tambon
) = t.addressid
WHERE
b.ward = '20'
AND t.addressid = '400706'
)
UNION
(
SELECT
t.full_name AS 'ที่อยู่'
,COUNT(*) AS 'จำนวน'
FROM
ipd a
INNER JOIN pat b ON b.an = a.an
INNER JOIN hos_pt c ON c.hn = b.hn
INNER JOIN hos_code_thaiaddress t ON CONCAT(
c.province,
c.amphur,
c.tambon
) = t.addressid
WHERE
b.ward = '20'
AND t.addressid = '400707'
)
UNION
(
SELECT
t.full_name AS 'ที่อยู่'
,COUNT(*) AS 'จำนวน'
FROM
ipd a
INNER JOIN pat b ON b.an = a.an
INNER JOIN hos_pt c ON c.hn = b.hn
INNER JOIN hos_code_thaiaddress t ON CONCAT(
c.province,
c.amphur,
c.tambon
) = t.addressid
WHERE
b.ward = '20'
AND t.addressid = '400708'
)
UNION
(
SELECT
t.full_name AS 'ที่อยู่'
,COUNT(*) AS 'จำนวน'
FROM
ipd a
INNER JOIN pat b ON b.an = a.an
INNER JOIN hos_pt c ON c.hn = b.hn
INNER JOIN hos_code_thaiaddress t ON CONCAT(
c.province,
c.amphur,
c.tambon
) = t.addressid
WHERE
b.ward = '20'
AND t.addressid = '400709'
)
UNION
(
SELECT
t.full_name AS 'ที่อยู่'
,COUNT(*) AS 'จำนวน'
FROM
ipd a
INNER JOIN pat b ON b.an = a.an
INNER JOIN hos_pt c ON c.hn = b.hn
INNER JOIN hos_code_thaiaddress t ON CONCAT(
c.province,
c.amphur,
c.tambon
) = t.addressid
WHERE
b.ward = '20'
AND t.addressid = '400710'
)
UNION
(
SELECT
t.full_name AS 'ที่อยู่'
,COUNT(*) AS 'จำนวน'
FROM
ipd a
INNER JOIN pat b ON b.an = a.an
INNER JOIN hos_pt c ON c.hn = b.hn
INNER JOIN hos_code_thaiaddress t ON CONCAT(
c.province,
c.amphur,
c.tambon
) = t.addressid
WHERE
b.ward = '20'
AND t.addressid = '400711'
)
UNION
(
SELECT
t.full_name AS 'ที่อยู่'
,COUNT(*) AS 'จำนวน'
FROM
ipd a
INNER JOIN pat b ON b.an = a.an
INNER JOIN hos_pt c ON c.hn = b.hn
INNER JOIN hos_code_thaiaddress t ON CONCAT(
c.province,
c.amphur,
c.tambon
) = t.addressid
WHERE
b.ward = '20'
AND t.addressid = '400712'
)