รายงาน NCD (ลำปาง)





select
case when (pcht.chroniccode is not null and pcdm.chroniccode is null )then 'HT'
when (pcdm.chroniccode is not null and pcht.chroniccode is null ) then 'DM'
when pcdm.chroniccode is not null and pcht.chroniccode is not null then 'DM&HT' else '' end as 'Chronic',
count(distinct case when (pcht.chroniccode is not null and pcdm.chroniccode is null )and visit.pressure is not null then visit.visitno else null end) as 'HTวัดBP',
count(distinct case when (pcht.chroniccode is not null and pcdm.chroniccode is null )and visit.pressure < '140/90' then visit.visitno else null end) as 'BP<140/90',
count(distinct case when (pcdm.chroniccode is not null and pcht.chroniccode is null ) or (pcdm.chroniccode is not null and pcht.chroniccode is not null)and visit.pressure is not null then visit.visitno else null end) as 'HTDMวัดBP',
count(distinct case when (pcdm.chroniccode is not null and pcht.chroniccode is null ) or (pcdm.chroniccode is not null and pcht.chroniccode is not null)and visit.pressure < '140/80' then visit.visitno else null end) as 'BP<140/80',
count(distinct case when visitlabchcyhembmsse.labcode in ('CH30','CH99') then visitlabchcyhembmsse.pid else null end) as 'DMตรวจHbA1c',
count(distinct case when visitlabchcyhembmsse.labcode in ('CH30','CH99') and visitlabchcyhembmsse.labresultdigit <7 then visitlabchcyhembmsse.pid else null end) as 'HbA1C<7',
count(distinct case when visitlabchcyhembmsse.labcode in ('CH04','CH09') then visitlabchcyhembmsse.pid else null end) as 'HTตรวจBUNCr',
count(distinct case when visitlabchcyhembmsse.labcode = 'CHeGFR'and visitlabchcyhembmsse.labresultdigit is not null then visitlabchcyhembmsse.pid else null end) as 'ตรวจEGFR',
count(distinct case when visitlabchcyhembmsse.labcode = 'CHeGFR'and visitlabchcyhembmsse.labresultdigit >= 90 then visitlabchcyhembmsse.pid else null end) as 'EGFR1',
count(distinct case when visitlabchcyhembmsse.labcode = 'CHeGFR'and visitlabchcyhembmsse.labresultdigit between 60 and 89 then visitlabchcyhembmsse.pid else null end) as 'EGFR2',
count(distinct case when visitlabchcyhembmsse.labcode = 'CHeGFR'and visitlabchcyhembmsse.labresultdigit between 30 and 59 then visitlabchcyhembmsse.pid else null end) as 'EGFR3',
count(distinct case when visitlabchcyhembmsse.labcode = 'CHeGFR'and visitlabchcyhembmsse.labresultdigit between 15 and 29 then visitlabchcyhembmsse.pid else null end) as 'EGFR4',
count(distinct case when visitlabchcyhembmsse.labcode = 'CHeGFR'and visitlabchcyhembmsse.labresultdigit < 15 then visitlabchcyhembmsse.pid else null end) as 'EGFR5',
count(distinct case when visitlabchcyhembmsse.labcode = 'MS01020201' then visitlabchcyhembmsse.pid else null end) as 'DMตรวจMicro',
count(distinct case when visitlabchcyhembmsse.labcode = 'MS01020201'and visitlabchcyhembmsse.labresultdigit >300 then visitlabchcyhembmsse.pid else null end) as 'Micro>300',
count(distinct case when visitlabchcyhembmsse.labcode = 'MS01000000' then visitlabchcyhembmsse.pid else null end) as 'Urine Anlysis',
count(distinct case when visitlabchcyhembmsse.labcode = 'CH17'then visitlabchcyhembmsse.pid else null end) as 'ตรวจLDL',
count(distinct case when visitlabchcyhembmsse.labcode = 'CH17'and visitlabchcyhembmsse.labresultdigit <100 then visitlabchcyhembmsse.pid else null end) as 'LDL<100',
count(distinct case when visitlabsugarblood.typetesting = '2'then visitlabsugarblood.visitno else null end) as 'ตรวจFBS',
count(distinct case when visitlabsugarblood.typetesting = '2'and visitlabsugarblood.sugarnumdigit <130 then visitlabsugarblood.visitno else null end) as 'FBS<130'
from person
LEFT JOIN personchronic pcht on person.pid = pcht.pid and person.pcucodeperson = pcht.pcucodeperson and trim(pcht.chroniccode) = 'I10'
LEFT JOIN personchronic pcdm on person.pid = pcdm.pid and person.pcucodeperson = pcdm.pcucodeperson and trim(pcdm.chroniccode) between 'E10' and 'E15.9'
LEFT JOIN personchronic pc on person.pid = pc.pid and person.pcucodeperson = pc.pcucodeperson
inner join cdisease on pc.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 (pc.chroniccode = 'I10' or pc.chroniccode between 'E10' and 'E15' )
group by chronic