webx

mhosp.gan@gmail.com
okp=k9iu
*****อันดับแรกปิดไมค์****

Revisit 180วัน F320

SELECT a.HN,CONCAT(trim(d.FNAME),' ',d.LNAME) AS FULLNAME,FLOOR(DATEDIFF(NOW(),d.BIRTHDATE)/365.25) as AGE,
a.VISIT_ID , b.VISIT_ID, a.REG_DATETIME , b.REG_DATETIME, DATEDIFF(b.REG_DATETIME, a.REG_DATETIME) as DURATION, i.ICD10_TM
FROM opd_visits a
INNER JOIN opd_visits b ON a.HN = b.HN AND b.IS_CANCEL = 0
INNER JOIN cid_hn c ON a.HN = c.HN
INNER JOIN population d ON c.CID = d.CID
RIGHT  JOIN f32 f ON  f.cid = d.CID
LEFT JOIN opd_diagnosis e ON a.VISIT_ID = e.VISIT_ID AND e.IS_CANCEL = 0
LEFT JOIN icd10new i ON e.ICD10 = i.ICD10
WHERE a.REG_DATETIME BETWEEN '2018.04.30 00:00' AND '2018.11.01 23:59'
AND a.IS_CANCEL = 0
AND trim(a.HN) <> ' '
AND DATEDIFF(b.REG_DATETIME, a.REG_DATETIME) > 180
AND b.REG_DATETIME > a.REG_DATETIME
AND i.ICD10_TM BETWEEN 'F320' AND 'F339'
GROUP BY a.HN

sql F320 Revisit

SELECT  '10953'AS HOSPCODE,'รพ.ม่วงสามสิบ 'AS HOSPNAME,a.HN ,CONCAT(trim(c.FNAME),'  ',c.LNAME) AS FULLNAME,
FLOOR(DATEDIFF(NOW(),c.BIRTHDATE)/326.25) AS AGE,
GROUP_CONCAT(date(a.REG_DATETIME)SEPARATOR '|') AS REGDATE
FROM opd_visits a
INNER JOIN cid_hn b ON a.hn = b.hn
INNER JOIN population c ON b.cid = c.cid
LEFT JOIN opd_diagnosis e ON a.visit_id = e.visit_id AND e.IS_CANCEL = 0
LEFT JOIN icd10new d ON e.ICD10 = d.ICD10
LEFT JOIN service_units s ON a.unit_reg = s.unit_id
WHERE a.REG_DATETIME BETWEEN '2018.04.30 00:00' AND '2019.04.30 23:59'
AND a.IS_CANCEL = 0
AND FLOOR(DATEDIFF(NOW(),c.BIRTHDATE)/326.25) >= 60
AND b.CID in (SELECT ff.cid FROM f32 ff)
AND d.ICD10_TM BETWEEN 'F32' AND 'F33'
GROUP BY a.HN  HAVING COUNT(date(a.REG_DATETIME)) >= 2
ORDER BY a.HN