1.ช่องหมายเลข 1
ใช้ scpt ตามนี้
........................................................................................
SELECT person.pid,person.fname,person.lname,person.idcard
,concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) as birth_date,
getAgeYearNum(person.birth,CURDATE()) AS `อายุ`,concat(person.hnomoi,' ','ม.',person.mumoi)as 'ที่อยู่่'
,group_concat(pc.chroniccode) as กลุ่มเป้าหมาย
FROM person
inner join (select pc.pid,pc.chroniccode,pc.cup from personchronic pc WHERE (pc.chroniccode like 'E1%'
or pc.chroniccode like 'I25%' or pc.chroniccode like 'C%'
or pc.chroniccode like 'J44%' or pc.chroniccode like 'J45%'
or pc.chroniccode like 'N18%')) pc on pc.pid=person.pid
where person.dischargetype=9
and person.typelive in (0,1,3) and pc.cup=person.pcucodeperson
GROUP BY person.pid;
.......................................................................................
2.ช่องหมายเลข 2 (ได้รับวัคซีนflu ในเรื้อรัง 7 โรค)
ใช้ scpt ตามนี้
........................................................................................
SELECT
vpi.pcucode,p.pid,vpi.visitno,ct.titlename,p.fname,p.lname,
p.idcard AS 'ปชช.',getAgeYearNum(p.birth,CURDATE()) AS 'อายุปี',
h.hno AS 'เลขที่',vl.villno AS 'หมู่ที่',vpi.dateepi,GROUP_CONCAT(pc.chroniccode) AS 'โรคเรื้อรัง',
vpi.vaccinecode,cdg.files18epi AS 'รหัสส่งออก43 แฟ้ม'
FROM visitepi vpi
INNER JOIN cdrug cdg ON vpi.vaccinecode = cdg.drugcode
INNER JOIN person p ON vpi.pcucodeperson = p.pcucodeperson AND vpi.pid=p.pid
INNER JOIN ctitle ct ON p.prename = ct.titlecode
INNER JOIN house h ON p.pcucodeperson = h.pcucode AND p.hcode = h.hcode
INNER JOIN village vl ON h.pcucode = vl.pcucode AND vl.villcode = h.villcode
INNER JOIN personchronic pc on p.pid=pc.pid
where p.dischargetype=9 and p.typelive in (0,1,3)
and pc.cup=p.pcucodeperson
AND (pc.chroniccode like 'E1%'
or pc.chroniccode like 'I25%' or pc.chroniccode like 'C%'
or pc.chroniccode like 'J44%' or pc.chroniccode like 'J45%'
or pc.chroniccode like 'N18%')
AND vpi.dateepi BETWEEN '2015-10-01' AND CURDATE()
AND cdg.files18epi = '815'
GROUP BY p.pid
ORDER BY h.villcode;
.......................................................................................
3.ช่องหมายเลข 3 (65 ปี ที่ตัดผู้ป่วยเรื้อรังออกแล้ว)
ใช้ scpt ตามนี้
........................................................................................
SELECT person.pid,person.fname,person.lname,person.idcard
,concat(substr(person.birth,9,2),'/',substr(person.birth,6,2),'/',substr(person.birth,1,4)+543) as birth_date,
getAgeYearNum(person.birth,CURDATE()) AS `อายุ`,concat(person.hnomoi,' ','ม.',person.mumoi)as 'ที่อยู่่','ผู้สูงอายุ 65+' as กลุ่มเป้าหมาย
FROM person
where getAgeYearNum(person.birth,'2016-10-01') >=65 and person.dischargetype=9 and person.typelive in (0,1,3)
AND concat(person.pid,person.pcucodeperson)not in(select concat(pc.pid,pc.pcucodeperson)from personchronic pc
WHERE (pc.chroniccode like 'E1%' or pc.chroniccode like 'I25%' or pc.chroniccode like 'C%' or pc.chroniccode like 'J44%' or pc.chroniccode like 'J45%' or pc.chroniccode like 'N18%'))
GROUP BY person.pid
.......................................................................................
4.ช่องหมายเลข 4
ใช้ scpt ตามนี้
........................................................................................
SELECT
vpi.pcucode,
p.pid,
vpi.visitno,
ct.titlename,
p.fname,
p.lname,
p.idcard AS 'ปชช.',
getAgeYearNum(p.birth,CURDATE()) AS 'อายุปี',
vpi.dateepi,
h.hno AS 'เลขที่',
vl.villno AS 'หมู่ที่',
vpi.vaccinecode,
cdg.files18epi AS 'รหัสส่งออก43 แฟ้ม'
FROM visitepi vpi
INNER JOIN cdrug cdg ON vpi.vaccinecode = cdg.drugcode
INNER JOIN person p ON vpi.pcucodeperson = p.pcucodeperson AND vpi.pid=p.pid
INNER JOIN ctitle ct ON p.prename = ct.titlecode
INNER JOIN house h ON p.pcucodeperson = h.pcucode AND p.hcode = h.hcode
INNER JOIN village vl ON h.pcucode = vl.pcucode AND vl.villcode = h.villcode
WHERE vpi.dateepi BETWEEN '2015-10-01' AND CURDATE()
AND cdg.files18epi = '815'
AND getAgeYearNum(p.birth,dateepi)>= '65'
ORDER BY p.birth;
.......................................................................................
แบบฟอร์ม ส่งเข้าไลน์กลุ่ม JHCIS""""" ขอด่วนนะจ๊ะ
ไม่มีความคิดเห็น:
แสดงความคิดเห็น