โรงพยาบาลจอมพระ

ตารางที่เกี่ยวข้อง select * from nutrition_items

select o.hn,c.clinic,(group_concat(DISTINCT ‘(‘,o.bmi,’/’,o.vstdate,’)’))as AA,

(select concat(‘(‘,o2.bmi,’)(‘,o2.vstdate,’)’) from opdscreen o2
where o2.hn=o.hn and o2.vstdate between min(o.vstdate) and max(o.vstdate)
order by concat(year(o2.vstdate),month(o2.vstdate),day(o2.vstdate)) desc limit 0,1)as “หลังสุด”,

(select concat(‘(‘,o3.bmi,’)(‘,o3.vstdate,’)’) from opdscreen o3
where o3.hn=o.hn and o3.vstdate between min(o.vstdate) and max(o.vstdate)
order by concat(year(o3.vstdate),month(o3.vstdate),day(o3.vstdate)) desc limit 1,1)as “ก่อนหลังสุด”,

(select concat(‘(‘,o4.bmi,’)(‘,o4.vstdate,’)’) from opdscreen o4
where o4.hn=o.hn and o4.vstdate between min(o.vstdate) and max(o.vstdate)
order by concat(year(o4.vstdate),month(o4.vstdate),day(o4.vstdate)) desc limit 2,1)as “ก่อนก่อนครั้งหลังสุด”

from opdscreen o
left outer join clinicmember c on c.hn=o.hn
where o.vstdate between “2015-10-01” and “2016-09-31” and c.clinic in (“001″,”002”)
and c.clinic is not null
group by o.hn
order by year(o.vstdate),month(o.vstdate),date(o.vstdate) desc

CREATE table f43_repo_list (table_name VARCHAR(200) not null PRIMARY key);

select p.sex,p.age_y,o.hba1c,o.bw,o.bmi,round(o.creatinine_kidney_percent,2),o.creatinine,
round(
(case when p.sex=1 then (((140-p.age_y)*o.bw)/(72*o.creatinine))
else (((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) end),2)as GFR_CKD_EPI
,
round(if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),(((140-p.age_y)*o.bw*0.85)/(72*o.creatinine))),2)as K,

(case when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine))>89 then ‘ STATE 1’
when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) between ‘60.00’ and ‘89.999’ then ‘ STATE 2’
when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) between ‘30.00’ and ‘59.999’ then ‘ STATE 3’
when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)) ,
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) between ‘15.00’ and ‘29.99’ then ‘ STATE 4’
when if(p.sex=1,(((140-p.age_y)*o.bw)/(72*o.creatinine)),
((140-p.age_y)*o.bw*0.85)/(72*o.creatinine)) between ‘0.01’ and ‘14.999’ then ‘ STATE 5’
else ‘error massage’ end)as KK,

c.clinic,concat(v.pdx,’ ‘,v.dx0,’ ‘,v.dx1,’ ‘,v.dx2,’ ‘,v.dx3,’ ‘,v.dx4,’ ‘,v.dx5)as diag,ROUND(o.bmi,2)as BMI,
(case when o.creatinine_kidney_percent>=90 then ‘1’
when o.creatinine_kidney_percent between ‘60.00’ and ‘89.999’ then ‘2’
when o.creatinine_kidney_percent between ‘30.00’ and ‘59.999’ then ‘3’
when o.creatinine_kidney_percent between ‘15.00’ and ‘29.999’ then ‘4’
when o.creatinine_kidney_percent<=15 then ‘5’
else ‘error massage’ end)as GFR,

o.creatinine_kidney_percent,o.vn,o.hn,o.vstdate,o.vsttime,o.cc,o.symptom,
p.cid,concat(p.pname,p.fname,’ ‘,p.lname)as name,(case when p.sex=’1’ then ‘ชาย’ else ‘หญิง’ end) as sex,
p.age_y
from opdscreen o
left join person p on p.patient_hn=o.hn
left join vn_stat v on v.vn=o.vn
left join clinicmember c on c.hn=o.hn
where (((o.vstdate between ‘2015-10-01’ and ‘2015-12-31’)
and (o.creatinine_kidney_percent is not null and o.creatinine_kidney_percent<>”0.00″) and o.creatinine<>0.00)
and ((v.pdx between”e100″ and “e149″
or v.dx0 between”e100” and “e149″
or v.dx1 between”e100” and “e149″
or v.dx2 between”e100” and “e149″
or v.dx3 between”e100” and “e149″
or v.dx4 between”e100” and “e149″
or v.dx5 between”e100” and “e149″) or
(v.pdx between”i10” and “i152″
or v.dx0 between”i10” and “i152″
or v.dx1 between”i10” and “i152″
or v.dx2 between”i10” and “i152″
or v.dx3 between”i10” and “i152″
or v.dx4 between”i10” and “i152″
or v.dx5 between”i10” and “i152”)))
and o.hba1c is not null

group by o.hn desc
order by KK,o.hba1c asc

**************REFER*******************
————–OUT———–ส่งออกจาก รพ.—————-
++++HOSXP++++++++++++
select month(refer_date),YEAR(refer_date),count(*) from referout
where refer_date between ‘2015-12-01’ and ‘2017-09-31’
group by month(refer_date),YEAR(refer_date)
ORDER BY year(refer_date),month(refer_date)
++++THAI REFER+++++++++
select month(refer_date),YEAR(refer_date),count(*) from referout
where refer_date between ‘2015-12-01’ and ‘2017-09-31’
group by month(refer_date),YEAR(refer_date)
ORDER BY year(refer_date),month(refer_date)

————–IN————–รับเข้าจาก รพ.สต.——————
++++HOSXP++++++++++++
select month(refer_date),YEAR(refer_date),count(*) from referin
where (refer_date between ‘2015-12-01’ and ‘2017-09-31’)
and (refer_hospcode in (‘00277′,’03139′,’03140′,’03141′,’03142′,’03144′,’03145′,’03146′,’03147′,’03148′,’13852’) )
group by month(refer_date),YEAR(refer_date)
ORDER BY year(refer_date),month(refer_date)
+++++++++THAI REFER+++++++++
select month(refer_date),YEAR(refer_date),count(*) from referback
where refer_hospcode in (‘00277′,’03139′,’03140′,’03141′,’03142′,’03144′,’03145′,’03146′,’03147′,’03148′,’13852’)
and (refer_date between ‘2015-12-01’ and ‘2017-09-31’)
group by month(refer_date),YEAR(refer_date)
ORDER BY year(refer_date),month(refer_date)

————-BACK———–รับเข้าจาก รพ.อื่นๆ ที่ไม่ใช่ รพ.สต.——–
++++HOSXP++++++++++++
select month(refer_date),YEAR(refer_date),count(*) from referin
where (refer_date between ‘2015-12-01’ and ‘2017-09-31’)
and (refer_hospcode not in (‘00277′,’03139′,’03140′,’03141′,’03142′,’03144′,’03145′,’03146′,’03147′,’03148′,’13852’) )
group by month(refer_date),YEAR(refer_date)
ORDER BY year(refer_date),month(refer_date)
++++THAI REFER+++++++++
select month(refer_date),YEAR(refer_date),count(*) from referback
where refer_hospcode not in (‘00277′,’03139′,’03140′,’03141′,’03142′,’03144′,’03145′,’03146′,’03147′,’03148′,’13852’)
and (refer_date between ‘2015-12-01’ and ‘2017-09-31’)
group by month(refer_date),YEAR(refer_date)
ORDER BY year(refer_date),month(refer_date)

select group_concat(i.icode)as drugitems,o.hn,o.bps,o.bpd,o.hba1c,v.vstdate,
(case when o.bps<=130 and o.bpd<=80 then ‘bp น้อยกว่า130/80’ else null end)as bpStat,
(case when o.hba1c<7 then ‘hba1c น้อยกว่า 7’ else null end)as hba1cStat,
(case when (v.pdx between ‘n083’ and ‘n189’)
and (((v.dx0 not between ‘e109’ and ‘e119’) and (v.dx0 not between ‘i10’ and ‘i152’))
and ((v.dx1 not between ‘e109’ and ‘e119’) and (v.dx1 not between ‘i10’ and ‘i152’))
and ((v.dx2 not between ‘e109’ and ‘e119’) and (v.dx2 not between ‘i10’ and ‘i152’))
and ((v.dx3 not between ‘e109’ and ‘e119’) and (v.dx3 not between ‘i10’ and ‘i152’))
and ((v.dx4 not between ‘e109’ and ‘e119’) and (v.dx4 not between ‘i10’ and ‘i152’))
and ((v.dx5 not between ‘e109’ and ‘e119’) and (v.dx5 not between ‘i10’ and ‘i152’)))
then ‘diag_N’ end)as diag,
(case when (v.pdx between ‘n083’ and ‘n189’) and
((v.dx0 between ‘e109’ and ‘e119’)
and ((v.dx1 not between ‘i10’ and ‘i152’)
and (v.dx2 not between ‘i10’ and ‘i152’)
and (v.dx3 not between ‘i10’ and ‘i152’)
and (v.dx4 not between ‘i10’ and ‘i152’)
and (v.dx5 not between ‘i10’ and ‘i152’))
) or (v.pdx between ‘n083’ and ‘n189’) and
((v.dx1 between ‘e109’ and ‘e119’)
and ((v.dx0 not between ‘i10’ and ‘i152’)
and (v.dx2 not between ‘i10’ and ‘i152’)
and (v.dx3 not between ‘i10’ and ‘i152’)
and (v.dx4 not between ‘i10’ and ‘i152’)
and (v.dx5 not between ‘i10’ and ‘i152’))
) or (v.pdx between ‘n083’ and ‘n189’) and
((v.dx2 between ‘e109’ and ‘e119’)
and ((v.dx0 not between ‘i10’ and ‘i152’)
and (v.dx1 not between ‘i10’ and ‘i152’)
and (v.dx3 not between ‘i10’ and ‘i152’)
and (v.dx4 not between ‘i10’ and ‘i152’)
and (v.dx5 not between ‘i10’ and ‘i152’))
) or (v.pdx between ‘n083’ and ‘n189’) and
((v.dx3 between ‘e109’ and ‘e119’)
and ((v.dx0 not between ‘i10’ and ‘i152’)
and (v.dx2 not between ‘i10’ and ‘i152’)
and (v.dx1 not between ‘i10’ and ‘i152’)
and (v.dx4 not between ‘i10’ and ‘i152’)
and (v.dx5 not between ‘i10’ and ‘i152’))
) or (v.pdx between ‘n083’ and ‘n189’) and
((v.dx4 between ‘e109’ and ‘e119’)
and ((v.dx0 not between ‘i10’ and ‘i152’)
and (v.dx2 not between ‘i10’ and ‘i152’)
and (v.dx3 not between ‘i10’ and ‘i152’)
and (v.dx1 not between ‘i10’ and ‘i152’)
and (v.dx5 not between ‘i10’ and ‘i152’))
) or (v.pdx between ‘n083’ and ‘n189’) and
((v.dx5 between ‘e109’ and ‘e119’)
and ((v.dx0 not between ‘i10’ and ‘i152’)
and (v.dx2 not between ‘i10’ and ‘i152’)
and (v.dx3 not between ‘i10’ and ‘i152’)
and (v.dx4 not between ‘i10’ and ‘i152’)
and (v.dx1 not between ‘i10’ and ‘i152’))
)
then ‘N+E’ end)as pdxNE,

(case when (v.pdx between ‘n083’ and ‘n189’) and
((v.dx0 between ‘i10’ and ‘i152’)
and ((v.dx1 not between ‘e109’ and ‘e119’)
and (v.dx2 not between ‘e109’ and ‘e119’)
and (v.dx3 not between ‘e109’ and ‘e119’)
and (v.dx4 not between ‘e109’ and ‘e119’)
and (v.dx5 not between ‘e109’ and ‘e119’))
) or (v.pdx between ‘n083’ and ‘n189’) and
((v.dx1 between ‘i10’ and ‘i152’)
and ((v.dx0 not between ‘e109’ and ‘e119’)
and (v.dx2 not between ‘e109’ and ‘e119’)
and (v.dx3 not between ‘e109’ and ‘e119’)
and (v.dx4 not between ‘e109’ and ‘e119’)
and (v.dx5 not between ‘e109’ and ‘e119’))
) or (v.pdx between ‘n083’ and ‘n189’) and
((v.dx2 between ‘i10’ and ‘i152’)
and ((v.dx0 not between ‘e109’ and ‘e119’)
and (v.dx1 not between ‘e109’ and ‘e119’)
and (v.dx3 not between ‘e109’ and ‘e119’)
and (v.dx4 not between ‘e109’ and ‘e119’)
and (v.dx5 not between ‘e109’ and ‘e119’))
) or (v.pdx between ‘n083’ and ‘n189’) and
((v.dx3 between ‘i10’ and ‘i152’)
and ((v.dx0 not between ‘e109’ and ‘e119’)
and (v.dx2 not between ‘e109’ and ‘e119’)
and (v.dx1 not between ‘e109’ and ‘e119’)
and (v.dx4 not between ‘e109’ and ‘e119’)
and (v.dx5 not between ‘e109’ and ‘e119’))
) or (v.pdx between ‘n083’ and ‘n189’) and
((v.dx4 between ‘i10’ and ‘i152’)
and ((v.dx0 not between ‘e109’ and ‘e119’)
and (v.dx2 not between ‘e109’ and ‘e119’)
and (v.dx3 not between ‘e109’ and ‘e119’)
and (v.dx1 not between ‘e109’ and ‘e119’)
and (v.dx5 not between ‘e109’ and ‘e119’))
) or (v.pdx between ‘n083’ and ‘n189’) and
((v.dx5 between ‘i10’ and ‘i152’)
and ((v.dx0 not between ‘e109’ and ‘e119’)
and (v.dx2 not between ‘e109’ and ‘e119’)
and (v.dx3 not between ‘e109’ and ‘e119’)
and (v.dx4 not between ‘e109’ and ‘e119’)
and (v.dx1 not between ‘e109’ and ‘e119’))
)
then ‘N+i’ end)as pdxNi,

v.pdx,v.dx0,v.dx1,v.dx2,v.dx3,v.dx4,v.dx5
from vn_stat v
left join opdscreen o on o.vn=v.vn
left join opitemrece i on i.vn=o.vn and i.icode in (‘1550040′,’1550128’)
where (v.vstdate between ‘2012-10-01’ and ‘2016-12-31’)
and ((v.pdx between ‘n083’ and ‘n189’)
or (v.dx0 between ‘n083’ and ‘n189’) or (v.dx1 between ‘n083’ and ‘n189’)
or (v.dx2 between ‘n083’ and ‘n189’) or (v.dx3 between ‘n083’ and ‘n189’)
or (v.dx4 between ‘n083’ and ‘n189’) or (v.dx5 between ‘n083’ and ‘n189’))
group by v.vn