ทะเบียน DM , HT

(
SELECT
'DM' as 'Dx',COUNT(DISTINCT(c.hn)) as 'count'
FROM f43_chronic c
INNER JOIN hos_pt p ON c.PID = p.hn
-- LEFT JOIN hos_code_thaiaddress a ON concat(p.province,p.amphur,p.tambon) = a.addressid
WHERE c.CHRONIC LIKE 'E%'
AND c.TYPEDISCH = '03'
-- AND TRUNCATE((SELECT DATEDIFF(date_format(now(),'%Y-%m-%d'), STR_TO_DATE(p.birthdate, '%Y-%m-%d')) / 365),0) >= 60
AND c.PID NOT IN
(
SELECT
c.PID
FROM f43_chronic c
INNER JOIN hos_pt p ON c.PID = p.hn
-- LEFT JOIN hos_code_thaiaddress a ON concat(p.province,p.amphur,p.tambon) = a.addressid
WHERE c.CHRONIC LIKE 'I%'
AND c.TYPEDISCH = '03'
-- AND TRUNCATE((SELECT DATEDIFF(date_format(now(),'%Y-%m-%d'), STR_TO_DATE(p.birthdate, '%Y-%m-%d')) / 365),0) >= 60
)
)
 
UNION
 
(
SELECT
'HT' as 'Dx',COUNT(DISTINCT(c.hn)) as 'count'
FROM f43_chronic c
INNER JOIN hos_pt p ON c.PID = p.hn
-- LEFT JOIN hos_code_thaiaddress a ON concat(p.province,p.amphur,p.tambon) = a.addressid
WHERE c.CHRONIC LIKE 'I%'
AND c.TYPEDISCH = '03'
-- AND TRUNCATE((SELECT DATEDIFF(date_format(now(),'%Y-%m-%d'), STR_TO_DATE(p.birthdate, '%Y-%m-%d')) / 365),0) >= 60
AND c.PID NOT IN
(
SELECT
c.PID
FROM f43_chronic c
INNER JOIN hos_pt p ON c.PID = p.hn
-- LEFT JOIN hos_code_thaiaddress a ON concat(p.province,p.amphur,p.tambon) = a.addressid
WHERE c.CHRONIC LIKE 'E%'
AND c.TYPEDISCH = '03'
-- AND TRUNCATE((SELECT DATEDIFF(date_format(now(),'%Y-%m-%d'), STR_TO_DATE(p.birthdate, '%Y-%m-%d')) / 365),0) >= 60
)
)
 
UNION
 
(
SELECT
'DM,HT' as 'Dx',COUNT(DISTINCT(c.hn)) as 'count'
FROM f43_chronic c
INNER JOIN hos_pt p ON c.PID = p.hn
-- LEFT JOIN hos_code_thaiaddress a ON concat(p.province,p.amphur,p.tambon) = a.addressid
WHERE c.CHRONIC LIKE 'I%'
AND c.TYPEDISCH = '03'
-- AND TRUNCATE((SELECT DATEDIFF(date_format(now(),'%Y-%m-%d'), STR_TO_DATE(p.birthdate, '%Y-%m-%d')) / 365),0) >= 60
AND c.PID IN
(
SELECT
c.PID
FROM f43_chronic c
INNER JOIN hos_pt p ON c.PID = p.hn
-- LEFT JOIN hos_code_thaiaddress a ON concat(p.province,p.amphur,p.tambon) = a.addressid
WHERE c.CHRONIC LIKE 'E%'
AND c.TYPEDISCH = '03'
-- AND TRUNCATE((SELECT DATEDIFF(date_format(now(),'%Y-%m-%d'), STR_TO_DATE(p.birthdate, '%Y-%m-%d')) / 365),0) >= 60
)
)

SELECT
COUNT(DISTINCT v.hn) AS 'จน.คน'
FROM hos_pt_visit v
INNER JOIN hos_doctor_diagnosis d ON v.visit_code = d.visit_code
WHERE v.date_visit_sql BETWEEN '20211001' AND '20220930'
AND d.icd10 = 'N185'
AND d.dxtype = '1'
AND v.hn IN (
SELECT
DISTINCT(c.hn)
FROM f43_chronic c
INNER JOIN hos_pt p ON c.PID = p.hn
WHERE c.CHRONIC LIKE 'E%'
AND c.TYPEDISCH = '03'
AND c.PID NOT IN
(
SELECT
c.PID
FROM f43_chronic c
INNER JOIN hos_pt p ON c.PID = p.hn
-- LEFT JOIN hos_code_thaiaddress a ON concat(p.province,p.amphur,p.tambon) = a.addressid
WHERE c.CHRONIC LIKE 'I%'
AND c.TYPEDISCH = '03'
)
)