คะแนนความสำเร็จการส่งเสริมสุขภาพผู้สูงอายุไม่มีภาวะพึ่งพิง

------ ความจำ
 
SET @start67 = '20231001';
SET @end67 = '20240930';
SET @start68 = '20241001';
SET @end68 = '20250930';
 
-- Count unique patients (hn) for specific PPSPECIAL values within fiscal year 67
(
SELECT
    DISTINCT v.hn
FROM
    hos_pt_visit v
INNER JOIN
    f43_specialpp s ON s.SEQ = v.visit_code
INNER JOIN
    c_ppspecial c ON c.code = s.PPSPECIAL
INNER JOIN
    hos_pt p ON p.hn = v.hn
WHERE
    v.date_visit_sql BETWEEN @start67 AND @end67
    AND s.PPSPECIAL IN ('1B1221', '1B1223')
    AND TRUNCATE(
        (
            SELECT
                DATEDIFF(
                    v.date_visit,
                    STR_TO_DATE(p.birthdate, '%Y-%m-%d')
                ) / 365
        ), 0
    ) >= 60
)
 
UNION
 
(
SELECT
    DISTINCT v.hn
 
FROM
    hos_pt_visit v
INNER JOIN
    f43_specialpp s ON s.SEQ = v.visit_code
INNER JOIN
    c_ppspecial c ON c.code = s.PPSPECIAL
INNER JOIN
    hos_pt p ON p.hn = v.hn
WHERE
    v.date_visit_sql BETWEEN @start68 AND @end68
    AND s.PPSPECIAL IN ('1B1220', '1B1221', '1B1223', '1B1224', '1B1225', '1B1226', '1B1229')
    AND TRUNCATE(
        (
            SELECT
                DATEDIFF(
                    v.date_visit,
                    STR_TO_DATE(p.birthdate, '%Y-%m-%d')
                ) / 365
        ), 0
    ) >= 60
)
UNION
 
(
SELECT
    distinct(v.hn)
FROM
    hos_pt_visit v
INNER JOIN
    hos_pt p ON p.hn = v.hn
INNER JOIN
    hos_doctor_diagnosis d ON d.visit_code = v.visit_code
WHERE
    v.date_visit_sql BETWEEN @start68 AND @end68
    AND d.icd10 IN (
        'F00', 'F000', 'F001', 'F002', 'F009',
        'F01', 'F010', 'F011', 'F012', 'F013', 'F018', 'F019',
        'F02', 'F020', 'F021', 'F022', 'F023', 'F024', 'F028',
        'F03'
    )
    AND d.dxtype = '1'
    AND TRUNCATE(
        (
            SELECT
                DATEDIFF(
                    v.date_visit,
                    STR_TO_DATE(p.birthdate, '%Y-%m-%d')
                ) / 365
        ), 0
    ) >= 60
)
 
UNION
 
(
SELECT
    DISTINCT v.hn
FROM
    hos_pt_visit v
INNER JOIN
    hos_pt p ON p.hn = v.hn
INNER JOIN
    hos_doctor_diagnosis d ON d.visit_code = v.visit_code
WHERE
    v.date_visit_sql BETWEEN @start68 AND @end68
    AND d.icd10 IN ('F067')
    AND d.dxtype = '1'
    AND TRUNCATE(
        (
            SELECT
                DATEDIFF(
                    v.date_visit,
                    STR_TO_DATE(p.birthdate, '%Y-%m-%d')
                ) / 365
        ), 0
    ) >= 60
)
 
UNION
(
 
SELECT
    DISTINCT v.hn
 
FROM
    hos_pt_visit v
INNER JOIN
    f43_community_service s ON s.SEQ = v.visit_code
 
INNER JOIN
    hos_pt p ON p.hn = v.hn
WHERE
    v.date_visit_sql BETWEEN @start68 AND @end68
    AND s.COMSERVICE IN ('1A004')
    AND TRUNCATE(
        (
            SELECT
                DATEDIFF(
                    v.date_visit,
                    STR_TO_DATE(p.birthdate, '%Y-%m-%d')
                ) / 365
        ), 0
    ) >= 60
)
 
 
 
 
---- การเคลื่อนไหว
 
SET @start67 = '20231001';
SET @end67 = '20240930';
SET @start68 = '20241001';
SET @end68 = '20250930';
 
(
SELECT
    DISTINCT v.hn
 
FROM
    hos_pt_visit v
INNER JOIN
    f43_specialpp s ON s.SEQ = v.visit_code
INNER JOIN
    c_ppspecial c ON c.CODE = s.PPSPECIAL
INNER JOIN
    hos_pt p ON p.hn = v.hn
WHERE
    v.date_visit_sql BETWEEN @start67 AND @end67
    AND s.PPSPECIAL IN ('1B1201', '1B1202', '1B1203', '1B1209')
    AND TRUNCATE(
        (
            DATEDIFF(
                v.date_visit,
                STR_TO_DATE(p.birthdate, '%Y-%m-%d')
            ) / 365
        ),
        0
    ) >= 60
)
UNION
(
SELECT
    DISTINCT (v.hn)
FROM
    hos_pt_visit v
INNER JOIN
    f43_specialpp s ON s.SEQ = v.visit_code
INNER JOIN
    c_ppspecial c ON c.CODE = s.PPSPECIAL
INNER JOIN
    hos_pt p ON p.hn = v.hn
WHERE
    v.date_visit_sql BETWEEN @start68 AND @end68
    AND s.PPSPECIAL IN (
        '1B1200',
        '1B1201',
        '1B1202',
        '1B1203',
        '1B1209'
    )
    AND TRUNCATE(
        (
            DATEDIFF(
                v.date_visit,
                STR_TO_DATE(p.birthdate, '%Y-%m-%d')
            ) / 365
        ),
        0
    ) >= 60
)
 
UNION
(
SELECT
    distinct(v.hn)
 
FROM
    hos_pt_visit v
INNER JOIN
    hos_pt p ON p.hn = v.hn
INNER JOIN
    hos_doctor_diagnosis d ON d.visit_code = v.visit_code
WHERE
    v.date_visit_sql BETWEEN @start68 AND @end68
    AND d.icd10 BETWEEN 'W00' AND 'W019'
    AND d.dxtype IN ('1', '5')
    AND TRUNCATE(
        (
            DATEDIFF(
                v.date_visit,
                STR_TO_DATE(p.birthdate, '%Y-%m-%d')
            ) / 365
        ),
        0
    ) >= 60
)
UNION
(
 
SELECT
    DISTINCT v.hn
 
FROM
    hos_pt_visit v
INNER JOIN
    f43_community_service s ON s.SEQ = v.visit_code
 
INNER JOIN
    hos_pt p ON p.hn = v.hn
WHERE
    v.date_visit_sql BETWEEN @start68 AND @end68
    AND s.COMSERVICE IN ('1J27')
    AND TRUNCATE(
        (
            SELECT
                DATEDIFF(
                    v.date_visit,
                    STR_TO_DATE(p.birthdate, '%Y-%m-%d')
                ) / 365
        ), 0
    ) >= 60
)
 
---------ตัวหาร
 
SET @start67 = '20231001';
SET @end67 = '20240930';
 
 
SELECT
    DISTINCT(v.hn)
FROM
    hos_pt_visit v
INNER JOIN
    f43_specialpp s ON s.SEQ = v.visit_code
INNER JOIN
    c_ppspecial c ON c.CODE = s.PPSPECIAL
INNER JOIN
    hos_pt p ON p.hn = v.hn
WHERE
    v.date_visit_sql BETWEEN @start67 AND @end67
    AND s.PPSPECIAL IN ('1B1201', '1B1202', '1B1203', '1B1209')
    AND TRUNCATE(
        (
            DATEDIFF(
                v.date_visit,
                STR_TO_DATE(p.birthdate, '%Y-%m-%d')
            ) / 365
        ),
        0
    ) >= 60