... ผมใช้คำสั่งแบบนี้ครับ ...
select concat(day(cm.regdate),"/",month(cm.regdate),"/",year(cm.regdate)+543) as DateRec,
pt.cid as IDCard,cm.hn as HN, pt.pname as Title, pt.fname as NPatient, pt.lname as SPatient,
concat(day(pt.birthday),"/",month(pt.birthday),"/",year(pt.birthday)+543) as Birthday,
timestampdiff(year,pt.birthday,cm.regdate) as Age, pt.sex as Sex, os1.bw as Weight, os2.height as High,
concat(left(os3.bps,instr(os3.bps,".")-1),"/",left(os3.bpd,instr(os3.bpd,".")-1)) as Effect,
pt.addrpart as "No",pt.moopart as Moo,"-" as Lane,if((pt.road is null or pt.road = ""),"-",pt.road) as Road,
concat(pt.chwpart,pt.amppart,pt.tmbpart) as Address,
case
when pt.nationality in (99) then 1
when pt.nationality in (44) then 2
when pt.nationality in (48) then 3
when pt.nationality in (50) then 4
when pt.nationality in (57) then 5
when pt.nationality in (56) then 6
when pt.nationality in (46) then 7
else 8
end as Nation,
case
when pt.occupation in (001,501,503,504,505,506,509) then 1
when pt.occupation in (004,116,117,206,208,210) then 2
when pt.occupation in (002,137,209,216,402,403,404,405,406,702,703,704,705,706,707,708,709,711,712,713,714,715,716,
717,718,719,720,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824) then 3
when pt.occupation in (003,601,602,603,604,605) then 4
when pt.occupation in (014) then 5
when pt.occupation in (015) then 6
when pt.occupation in (007,202,203,204,205) then 7
when pt.occupation in (507) then 8
when pt.occupation in (009,110,111) then 9
when pt.occupation in (000,902) then 11
when pt.occupation in (012) then 12
when pt.occupation in (013,133) then 13
when pt.occupation in (090) then 14
when pt.occupation in (106,107,108,109,112,113,114,115) then 15
else 10
end as Occupation,
concat(day(d.death_date),"/",month(d.death_date),"/",year(d.death_date)+543) as Death,
if((d.death_date is not null or d.death_date <> ""),9,"") as Cause,
concat(day(cm.regdate),"/",month(cm.regdate),"/",year(cm.regdate)+543) as DateDiag,
case
when cm.clinic = 001 then 1
when cm.clinic = 002 then 2
when cm.clinic = 012 then 3
when cm.clinic = 011 then 4
when cm.clinic = 013 then 5
end as Chronic,
case
when cm.clinic = 001 then if((ccl.cormobidity is null or ccl.cormobidity = ""),1,mid(ccb.name,instr(ccb.name,"-")+1))
when cm.clinic = 002 then if((ccl.cormobidity is null or ccl.cormobidity = ""),9,mid(ccb.name,instr(ccb.name,"-")+1))
when cm.clinic = 012 then if((ccl.cormobidity is null or ccl.cormobidity = ""),18,mid(ccb.name,instr(ccb.name,"-")+1))
when cm.clinic = 011 then if((ccl.cormobidity is null or ccl.cormobidity = ""),19,mid(ccb.name,instr(ccb.name,"-")+1))
when cm.clinic = 013 then if((ccl.cormobidity is null or ccl.cormobidity = ""),27,mid(ccb.name,instr(ccb.name,"-")+1))
end as Code,
cl.icd10, i.name as Diagnosis, "จากระบบคลีนิคพิเศษ HOSxP ผ่าน Excel" as Remark
from clinicmember cm
left outer join patient pt on pt.hn = cm.hn
left outer join death d on d.hn = cm.hn
left outer join clinic_cormobidity_list ccl on ccl.hn = cm.hn and ccl.clinic = cm.clinic
left outer join clinic cl on cl.clinic = cm.clinic
left outer join opdscreen os1 on os1.hn = cm.hn
and os1.vn = (select max(vn) from opdscreen where hn = cm.hn and bw > 20)
left outer join opdscreen os2 on os2.hn = cm.hn
and os2.vn = (select max(vn) from opdscreen where hn = cm.hn and height > 20)
left outer join opdscreen os3 on os3.hn = cm.hn
and os3.vn = (select max(vn) from opdscreen where hn = cm.hn and vstdate <= cm.regdate and (bps > 20 or bpd > 20))
left outer join icd101 i on i.code = cl.icd10
left outer join clinic_cormobidity ccb on ccb.clinic = ccl.clinic and ccb.cormobidity = ccl.cormobidity
where cm.clinic in (001,002,011,012,013)
... แล้วส่งออกเป็น Excel เอาไปนำเข้าใน Surveillance อีกทีครับ ...
....
ปล. แก้ไขใหม่ให้เป็นภาษาไทย
..
หัวข้อ: มาใหม่ Surveillance V3.0 (Full 17082553) มาให้ทดสอบกันครับ (อ่าน 44543 ครั้ง)