code LAB


select
concat(ctitle.titlename,person.fname,'    ',person.lname)as pname,
person.idcard as pidcard,
GetAgeYearNum(person.birth,visit.visitdate)as age,
concat(person.hnomoi,' ','หมู่','  ',person.mumoi) as hmu,
DATE_FORMAT(visitlabchcyhembmsse.datecheck,'%Y-%m-%d') as datecheck,
max(case when personchronic.chroniccode = 'I10' then "/" else null end) as HT,
max(case when personchronic.chroniccode between 'E10' and 'E15' then "/" else null end) as DM,
max(case when visitlabchcyhembmsse.labcode  in ('CH30','CH99') then visitlabchcyhembmsse.labresultdigit else ''end) as 'HbA1C',
max(case when visitlabchcyhembmsse.labcode = 'CH04' then visitlabchcyhembmsse.labresultdigit else '' end) as 'BUN',
max(case when visitlabchcyhembmsse.labcode = 'CH09' then visitlabchcyhembmsse.labresultdigit else '' end) as 'Cre',
max(case when visitlabchcyhembmsse.labcode = 'CH27' then visitlabchcyhembmsse.labresultdigit else '' end) as 'Uric acid',
max(case when visitlabchcyhembmsse.labcode = 'CH07' then visitlabchcyhembmsse.labresultdigit else '' end) as 'Chol',
max(case when visitlabchcyhembmsse.labcode = 'CH14' then visitlabchcyhembmsse.labresultdigit else '' end) as 'HDL',
max(case when visitlabchcyhembmsse.labcode = 'CH17' then visitlabchcyhembmsse.labresultdigit else '' end) as 'LDL',
max(case when visitlabchcyhembmsse.labcode = 'CH25' then visitlabchcyhembmsse.labresultdigit else '' end) as 'Tri',
max(case when visitlabchcyhembmsse.labcode = 'CHeGFR' then visitlabchcyhembmsse.labresultdigit else '' end) as 'eGFR',
max(case when visitlabchcyhembmsse.labcode = 'HE01010000' then visitlabchcyhembmsse.labresultdigit else '' end) as 'Hct',
max(case when visitlabchcyhembmsse.labcode = 'HE01020000' then visitlabchcyhembmsse.labresultdigit else '' end) as 'Hb',
max(case when visitlabsugarblood.typetesting = '2' then visitlabsugarblood.sugarnumdigit else '' end) as 'FBS',
max(case when visitlabchcyhembmsse.labcode = 'MS01020201' then visitlabchcyhembmsse.labresultdigit else '' end) as 'Micro alb',
max(case when visitlabchcyhembmsse.labcode = 'MS01000000' then visitlabchcyhembmsse.labresultdigit else '' end) as 'Urineanalysis'
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
    left outer join visitlabsugarblood ON visit.pcucode = visitlabsugarblood.pcucode
    and visit.visitno = visitlabsugarblood.visitno
    left outer join visitlabchcyhembmsse ON visit.pcucode = visitlabchcyhembmsse.pcucode
    and visit.visitno = visitlabchcyhembmsse.visitno
    inner join village on house.villcode = village.villcode and house.pcucode = village.pcucode
WHERE ((person.dischargetype is null) OR (person.dischargetype != '1')) and visitlabchcyhembmsse.datecheck between '2014-10-01' and '2015-09-30'
and SUBSTRING(house.villcode,7,2)<> '00'  and (personchronic.chroniccode = 'I10' or personchronic.chroniccode between 'E10' and 'E15' )
GROUP BY person.pid,visitlabchcyhembmsse.datecheck
order by village.villno