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

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

select /* p.cid,o.pt_subtype,v.pdx,o.vn,o.hn,concat(p.pname,p.fname,’ ‘,p.lname)as Uname,
o.an,o.vstdate,o.doctor,d.name as dr,
r.send_from_depcode,
r.send_to_depcode,k1.department,
r.send_to_spclty,
o.spclty,s.name,o.last_dep,k.department,os.hpi,os.symptom,os.cc,o.rcpt_disease
*/ count(*),k1.department,s.name
from ovst o
left join spclty s on s.spclty=o.spclty
left join vn_stat v on v.vn=o.vn
left join opdscreen os on os.vn=o.vn
left join kskdepartment k on k.depcode=o.last_dep
left join patient p on p.hn=o.hn
left join doctor d on d.code=o.doctor
left join opd_regist_sendlist r on r.vn=o.vn
left join kskdepartment k1 on k1.depcode=r.send_to_depcode
where (o.vstdate between ‘2016-01-01’ and ‘2016-01-16′) and (o.spclty not in (’03’))
and (v.pdx is null or v.pdx=”)
group by s.name,k1.department
order by r.send_to_depcode

—————————————————————-
select k1.department,s.name,group_concat(distinct o.hn),o.vstdate,count(distinct o.hn)as ‘ÃÒÂ’,os.cc,group_concat(k2.department)as a
from ovst o
left join spclty s on s.spclty=o.spclty
left join vn_stat v on v.vn=o.vn
left join opdscreen os on os.vn=o.vn
left join kskdepartment k on k.depcode=o.last_dep
left join patient p on p.hn=o.hn
left join doctor d on d.code=o.doctor
left join opd_regist_sendlist r on r.vn=o.vn
left join kskdepartment k1 on k1.depcode=r.send_to_depcode
left join ptdepart de on de.vn=o.vn
left join kskdepartment k2 on k2.depcode=de.depcode
where (o.vstdate between ‘2016-02-01’ and ‘2016-02-29′) and (o.spclty not in (’03’))
and (v.pdx is null or v.pdx=”)
group by o.vn,s.name,k.department
order by r.send_to_depcode

select * from opitemrece
where hn=000101213
and vstdate=’2016-01-10′

ปรับ  paidst =02

select m.doctor,d.name,t.code,t.name,m.vn,m.hn,count(m.vn) as vn_count ,sum(m.tcount) as t_count , sum(m.scount) as s_count
from dttm t
left outer join dtmain m on m.tmcode=t.code and m.vstdate between ‘2015-10-01’ and ‘2015-10-01’
left join doctor d on d.code=m.doctor
group by m.doctor,m.vn,m.hn,t.code,t.name