1โค้ดการวัดความดันโลหิตแก่ผู้ป่วย เดือน กรกฏาคม-กันยายน ให้ copy เฉพาะตัวหนังสือสีแดง
select person.pid,
group_concat(distinct personchronic.chroniccode)as c,
max(case when month(visit.visitdate)=7 then visit.pressure else '' end) as 'ก.ค.',
max(case when month(visit.visitdate)=8 then visit.pressure else '' end) as 'ส.ค.',
max(case when month(visit.visitdate)=9 then visit.pressure else '' end) as 'ก.ย.'
from person
inner join ctitle on person.prename = ctitle.titlecode
inner join personchronic on person.pid=personchronic.pid
inner join cdisease on personchronic.chroniccode=cdisease.diseasecode
inner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
inner join visit ON person.pcucodeperson = visit.pcucodeperson
and person.pid = visit.pid
inner join village on house.villcode = village.villcode and house.pcucode = village.pcucode
WHERE visit.visitdate between '2013-10-01' and '2014-09-30'
and SUBSTRING(house.villcode,7,2)<> '00'and personchronic.chroniccode = 'I10'
and personchronic.pcucodeperson = personchronic.cup
GROUP BY personchronic.pid
2.ผลการตรวจ HbA1c เดือน กรกฏาคม-กันยายน ให้ copy เฉพาะตัวหนังสือสีแดง
SELECT v.pcucodeperson,v.pid,
max(case when month(v.datecheck)=7 then v.labresultdigit else '' end) as 'ก.ค.',
max(case when month(v.datecheck)=8 then v.labresultdigit else '' end) as 'ส.ค.',
max(case when month(v.datecheck)=9 then v.labresultdigit else '' end) as 'ก.ย.'
FROM visitlabchcyhembmsse v
where v.datecheck between '2013-10-01' and '2014-09-30' and v.labcode = 'CH99'
group by v.pid