Script ส่งออกข้อมูลโรคเรื้อรัง(Chronic) สำหรับ HOSxP

SELECT
(SELECT hospitalcode FROM opdconfig) AS HOSPCODE,
ps.person_id AS PID,
IF(c.regdate IS NULL OR TRIM(c.regdate)='' OR c.regdate LIKE '0000-00-00%','',DATE_FORMAT(c.regdate,'%Y%m%d')) AS DATE_DIAG,
'' AS CHRONIC,
'' AS HOSP_DX,
'' AS HOSP_RX,
IF(c.dchdate IS NULL OR TRIM(c.dchdate)='' OR c.dchdate LIKE '0000-00-00%','',DATE_FORMAT(c.dchdate,'%Y%m%d')) AS DATE_DISCH,
c.clinic_member_status_id AS TYPEDISCH,
IF(c.lastupdate IS NULL OR TRIM(c.lastupdate)='' OR c.lastupdate LIKE '0000-00-00%','',DATE_FORMAT(c.lastupdate,'%Y%m%d%H%i%s') ) AS D_UPDATE,
p.cid AS CID
FROM clinicmember c
LEFT OUTER JOIN clinic n ON c.clinic = n.clinic AND n.chronic = 'Y'
LEFT OUTER JOIN clinic_persist_icd cc ON c.hn=c.hn AND cc.dxtype = 1
LEFT OUTER JOIN clinic_member_status cm ON cm.clinic_member_status_id = c.clinic_member_status_id
LEFT OUTER JOIN patient p ON p.hn = c.hn
LEFT OUTER JOIN person ps ON c.hn=ps.patient_hn AND p.cid = ps.cid
LEFT OUTER JOIN opduser u ON u.loginname = c.modify_staff
LEFT OUTER JOIN ovst ov1 ON ov1.vn = c.last_cormobidity_screen_vn
ORDER BY c.pt_number,c.regdate

By : สุชาติ เกตุทอง