ตารางใน hosxp เกี่ยวกับ Risk Management
หน่วยงาน = select * from hospital_department
ความรุนแรง = select * from risk_level
เชื่อมโยงโปรแกรม = select * from risk_relation_program
รายงาน = select * from risk_report
GFR แยก 5 state
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
คนไข้ความดัน ควบคุมระดับ ได้ดี 2 ครั้งหลังสุด คัดกรองจากคลิคนิค
set @d1 = ‘2015-05-31’;
set @d2 = ‘2016-10-01’;
SELECT * FROM
(select p.patient_hn as hn, p.cid, concat(p.pname, p.fname ,” “, p.lname) as pt_name
, TIMESTAMPDIFF(year,p.birthdate,now()) as age,
p.nationality,p.person_discharge_id,p.death,p.death_date,p.house_regist_type_id,m.clinic_member_status_id
,if(p.sex =1,”ชาย”,”หญิง”) as sex ,h.address, v1.village_moo, v1.village_name
, “NOT PASS” as group1,p.patient_hn,ps2.icd10
,(select if(bps between ‘1’ and ‘140’ and bpd between ‘1’ and ’90’,1,0) as poin
from opdscreen o1 WHERE o1.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 0,1) as dtx1
,(select vstdate
from opdscreen o1d WHERE o1d.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 0,1) as dtx1d
,(select bps
from opdscreen o1a WHERE o1a.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 0,1) as dtx1a
,(select bpd
from opdscreen o1b WHERE o1b.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 0,1) as dtx1b
,(select if(bps between ‘1’ and ‘140’ and bpd between ‘1’ and ’90’,1,0) as poin
from opdscreen o2 WHERE o2.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 1,1) as dtx2
,(select vstdate
from opdscreen o2d WHERE o2d.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 1,1) as dtx2d
,(select bps
from opdscreen o2a WHERE o2a.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 1,1) as dtx2a
,(select bpd
from opdscreen o2b WHERE o2b.hn = v.hn and vstdate between @d1 and @d2
ORDER BY vstdate DESC limit 1,1) as dtx2b
from vn_stat v
left join person p on p.patient_hn = v.hn
LEFT JOIN house h on h.house_id = p.house_id
LEFT JOIN village v1 on v1.village_id = p.village_id
left join clinic_persist_icd ps on ps.hn=v.hn
left join clinic_persist_icd ps2 on ps2.hn=ps.hn and ps2.icd10 between ‘e10′ and’e149’
left join clinicmember m on m.hn=ps.hn
where (v.vstdate between @d1 and @d2)
and /* v.hn in (select hn from clinicmember where clinic=”002″) */
((ps.icd10 between ‘i10’ and ‘i159′ or ps.icd10=’i674′ or ps.icd10=’H350′)
and (p.death=’N’) and (p.house_regist_type_id in (‘1′,’3′))and (m.clinic_member_status_id =’3’))
and (ps2.icd10 is null)
ORDER BY v.hn ) t1
WHERE t1.dtx1 =1 and t1.dtx2= 1
GROUP BY t1.hn
ดึงรายงาน REFER
**************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)
ความเห็นล่าสุด