query ผู้ป่วยเบาหวาน รายใหม่ ต.น้ำพอง หมู่ 3

ตารางที่เกี่ยวข้อง

clinicmember
clinic

SET @start_date = '2021-10-01';
SET @end_date = '2020-09-30';
SET @pdx = 'E110';
 
SELECT
    @pdx AS 'PDX',
    YEAR(@end_date) + 543 AS 'Year',
    pt.hn,
    pt.pname,
    pt.fname,
    pt.lname,
    CASE pt.sex WHEN '1' THEN 'ชาย' WHEN '2' THEN 'หญิง' ELSE '' END AS 'เพศ',
    TIMESTAMPDIFF(YEAR, pt.birthday, cm.regdate) AS 'อายุ',
    IFNULL(pt.addrpart, '') AS 'บ้านเลขที่',
    IFNULL(pt.moopart, '') AS 'หมู่ที่',
    IFNULL(t3.name, '') AS 'ตำบล',
    IFNULL(t2.name, '') AS 'อำเภอ',
    IFNULL(t1.name, '') AS 'จังหวัด'
 
FROM clinicmember cm
JOIN patient pt ON pt.hn = cm.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 cm.regdate BETWEEN @start_date AND @end_date
AND cm.clinic = '001'
AND t3.name = 'น้ำพอง'
AND cm.hn IN
(
    -- === ส่วนที่ 1: ค้นหา HN จาก OPD ===
    SELECT
      v.hn
    FROM ovst v
    JOIN ovstdiag dx ON dx.vn = v.vn AND dx.diagtype IN ('1')
    WHERE
      dx.icd10 = @pdx
      AND v.vstdate BETWEEN @start_date AND @end_date
    GROUP BY v.hn
 
    UNION
 
    -- === ส่วนที่ 2: ค้นหา HN จาก IPD ===
    SELECT
      i.hn
    FROM ipt i
    JOIN iptdiag d ON i.an = d.an AND d.diagtype IN ('1')
    WHERE
      d.icd10 = @pdx
      AND i.regdate BETWEEN @start_date AND @end_date
    GROUP BY i.hn
)
 
ORDER BY CAST(pt.moopart AS UNSIGNED)

เบาหวาน สะสม ที่ diag ckd และ Stroke ร่วมด้วย

SELECT
    pt.hn AS 'HN',
    pt.pname AS 'คำนำหน้า',
    pt.fname AS 'ชื่อ',
    pt.lname AS 'นามสกุล',
    IFNULL(pt.addrpart, '') AS 'บ้านเลขที่',
    IFNULL(pt.moopart, '') AS 'หมู่ที่',
    t3.name AS 'ตำบล',
 
    -- วันที่ลงทะเบียน DM และ HT
    IF(
        MAX(CASE WHEN cm.clinic = '001' THEN cm.regdate END) IS NOT NULL,
        CONCAT(YEAR(MAX(CASE WHEN cm.clinic = '001' THEN cm.regdate END)) + 543, '-', DATE_FORMAT(MAX(CASE WHEN cm.clinic = '001' THEN cm.regdate END), '%m-%d')),
        NULL
    ) AS 'วันที่ลงทะเบียน DM',
    IF(
        MAX(CASE WHEN cm.clinic = '002' THEN cm.regdate END) IS NOT NULL,
        CONCAT(YEAR(MAX(CASE WHEN cm.clinic = '002' THEN cm.regdate END)) + 543, '-', DATE_FORMAT(MAX(CASE WHEN cm.clinic = '002' THEN cm.regdate END), '%m-%d')),
        NULL
    ) AS 'วันที่ลงทะเบียน HT',
 
    -- วันที่ Diag โรคไต (N18x)
    IF(n181_diag.first_date IS NOT NULL, CONCAT(YEAR(n181_diag.first_date) + 543, '-', DATE_FORMAT(n181_diag.first_date, '%m-%d')), NULL) AS 'N181',
    IF(n182_diag.first_date IS NOT NULL, CONCAT(YEAR(n182_diag.first_date) + 543, '-', DATE_FORMAT(n182_diag.first_date, '%m-%d')), NULL) AS 'N182',
    IF(n183_diag.first_date IS NOT NULL, CONCAT(YEAR(n183_diag.first_date) + 543, '-', DATE_FORMAT(n183_diag.first_date, '%m-%d')), NULL) AS 'N183',
    IF(n184_diag.first_date IS NOT NULL, CONCAT(YEAR(n184_diag.first_date) + 543, '-', DATE_FORMAT(n184_diag.first_date, '%m-%d')), NULL) AS 'N184',
    IF(n185_diag.first_date IS NOT NULL, CONCAT(YEAR(n185_diag.first_date) + 543, '-', DATE_FORMAT(n185_diag.first_date, '%m-%d')), NULL) AS 'N185',
    IF(n189_diag.first_date IS NOT NULL, CONCAT(YEAR(n189_diag.first_date) + 543, '-', DATE_FORMAT(n189_diag.first_date, '%m-%d')), NULL) AS 'N189',
 
    -- วันที่ Diag Stroke (I6xx)
    IF(i610_diag.first_date IS NOT NULL, CONCAT(YEAR(i610_diag.first_date) + 543, '-', DATE_FORMAT(i610_diag.first_date, '%m-%d')), NULL) AS 'I610',
    IF(i613_diag.first_date IS NOT NULL, CONCAT(YEAR(i613_diag.first_date) + 543, '-', DATE_FORMAT(i613_diag.first_date, '%m-%d')), NULL) AS 'I613',
    IF(i614_diag.first_date IS NOT NULL, CONCAT(YEAR(i614_diag.first_date) + 543, '-', DATE_FORMAT(i614_diag.first_date, '%m-%d')), NULL) AS 'I614',
    IF(i633_diag.first_date IS NOT NULL, CONCAT(YEAR(i633_diag.first_date) + 543, '-', DATE_FORMAT(i633_diag.first_date, '%m-%d')), NULL) AS 'I633',
 
    -- >> คอลัมน์ I64 ที่เพิ่มเข้ามาใหม่ <<
    IF(i64_diag.first_date IS NOT NULL, CONCAT(YEAR(i64_diag.first_date) + 543, '-', DATE_FORMAT(i64_diag.first_date, '%m-%d')), NULL) AS 'I64'
 
FROM
    clinicmember cm
JOIN patient pt ON pt.hn = cm.hn
JOIN thaiaddress t3 ON t3.chwpart = pt.chwpart AND t3.amppart = pt.amppart AND t3.tmbpart = pt.tmbpart
 
-- LEFT JOIN ของกลุ่มโรคไต (N18x)
LEFT JOIN (SELECT v.hn, MIN(v.vstdate) AS first_date FROM ovstdiag od JOIN ovst v ON v.vn = od.vn WHERE od.icd10 = 'N181' GROUP BY v.hn) AS n181_diag ON pt.hn = n181_diag.hn
LEFT JOIN (SELECT v.hn, MIN(v.vstdate) AS first_date FROM ovstdiag od JOIN ovst v ON v.vn = od.vn WHERE od.icd10 = 'N182' GROUP BY v.hn) AS n182_diag ON pt.hn = n182_diag.hn
LEFT JOIN (SELECT v.hn, MIN(v.vstdate) AS first_date FROM ovstdiag od JOIN ovst v ON v.vn = od.vn WHERE od.icd10 = 'N183' GROUP BY v.hn) AS n183_diag ON pt.hn = n183_diag.hn
LEFT JOIN (SELECT v.hn, MIN(v.vstdate) AS first_date FROM ovstdiag od JOIN ovst v ON v.vn = od.vn WHERE od.icd10 = 'N184' GROUP BY v.hn) AS n184_diag ON pt.hn = n184_diag.hn
LEFT JOIN (SELECT v.hn, MIN(v.vstdate) AS first_date FROM ovstdiag od JOIN ovst v ON v.vn = od.vn WHERE od.icd10 = 'N185' GROUP BY v.hn) AS n185_diag ON pt.hn = n185_diag.hn
LEFT JOIN (SELECT v.hn, MIN(v.vstdate) AS first_date FROM ovstdiag od JOIN ovst v ON v.vn = od.vn WHERE od.icd10 = 'N189' GROUP BY v.hn) AS n189_diag ON pt.hn = n189_diag.hn
 
-- LEFT JOIN ของกลุ่มโรค Stroke (I6xx)
LEFT JOIN (SELECT v.hn, MIN(v.vstdate) AS first_date FROM ovstdiag od JOIN ovst v ON v.vn = od.vn WHERE od.icd10 = 'I610' GROUP BY v.hn) AS i610_diag ON pt.hn = i610_diag.hn
LEFT JOIN (SELECT v.hn, MIN(v.vstdate) AS first_date FROM ovstdiag od JOIN ovst v ON v.vn = od.vn WHERE od.icd10 = 'I613' GROUP BY v.hn) AS i613_diag ON pt.hn = i613_diag.hn
LEFT JOIN (SELECT v.hn, MIN(v.vstdate) AS first_date FROM ovstdiag od JOIN ovst v ON v.vn = od.vn WHERE od.icd10 = 'I614' GROUP BY v.hn) AS i614_diag ON pt.hn = i614_diag.hn
LEFT JOIN (SELECT v.hn, MIN(v.vstdate) AS first_date FROM ovstdiag od JOIN ovst v ON v.vn = od.vn WHERE od.icd10 = 'I633' GROUP BY v.hn) AS i633_diag ON pt.hn = i633_diag.hn
 
-- >> LEFT JOIN ของ I64 ที่เพิ่มเข้ามาใหม่ <<
LEFT JOIN (SELECT v.hn, MIN(v.vstdate) AS first_date FROM ovstdiag od JOIN ovst v ON v.vn = od.vn WHERE od.icd10 = 'I64' GROUP BY v.hn) AS i64_diag ON pt.hn = i64_diag.hn
 
WHERE
    cm.clinic IN ('001', '002')
    AND t3.name = 'น้ำพอง'
 
GROUP BY
    pt.hn, pt.pname, pt.fname, pt.lname, pt.addrpart, pt.moopart, t3.name,
    n181_diag.first_date, n182_diag.first_date, n183_diag.first_date, n184_diag.first_date, n185_diag.first_date, n189_diag.first_date,
    i610_diag.first_date, i613_diag.first_date, i614_diag.first_date, i633_diag.first_date,
    -- >> เพิ่ม Field ใหม่ใน GROUP BY <<
    i64_diag.first_date
 
ORDER BY
    CAST(pt.moopart AS UNSIGNED), pt.fname;