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

select v.vstdate,v.hn,o.*,v.*
from ovst_vaccine o
left join vn_stat v on v.vn=o.vn
left join person_vaccine p on p.person_vaccine_id=o.person_vaccine_id
where o.person_vaccine_id=’64’

select distinct v.vstdate,v.hn,v.cid,concat(p.pname,p.fname,’ ‘,p.lname)as name,
v.age_y,v.pttype,t.name,v.pcode,s.spclty,s.name,v.pdx,v.income,v.paid_money,
v.rcpt_money,v.remain_money,uc_money,v.discount_money,(case when remain_money=’0.00’ then (income-rcpt_money) else (income-paid_money) end )as ‘¤§¤éÒ§’,
(case when rcpt_money=’0.00′ then (paid_money-rcpt_money) else (income-income) end)as lost
from vn_stat v
left outer join patient p on p.hn=v.hn
left outer join pttype t on t.pttype=v.pttype
left outer join spclty s on s.spclty=v.spclty
left outer join rcpt_debt r on r.hn = v.hn
where (v.vstdate between ‘2014-10-16’ and ‘2014-10-16′) and (v.paid_money > 0 or v.rcpt_money >0) and (paid_money not in (’30’)) and (v.pttype<>’20’)
order by s.name,v.vn,v.spclty

คำสั่งนับ FTE ของงานกายภาพ สำหรับรพ.ที่ใช้ HOSxP  ( last update 11:45 10/10/2557 )
######################################

Musculoskeletal 
ผู้ป่วยนอก
SELECT COUNT(p.vn)
FROM physic_main p, vn_stat v
WHERE p.vn = v.vn AND p.vstdate BETWEEN'2012-10-01'AND'2013-09-30' 
AND v.pdx IN (select code from icd101 where (code between "M00" and "M9999" or code between "S00" and "S9999" or code between "T00" and "T9999") 
and code not in (select code from icd101 where (code between'M471'and'M472' or code in ('M494','S04','S14','S24','SS34','S44','S54','S64','S74','S84','S94','T903','T913','T924','T934'))))

ผู้ป่วยใน
SELECT COUNT(p.an)
FROM physic_main_ipd p, an_stat a
WHERE p.an = a.an AND p.vstdate BETWEEN'2012-10-01'AND'2013-09-30' 
AND a.pdx IN (select code from icd101 where (code between "M00" and "M9999" or code between "S00" and "S9999" or code between "T00" and "T9999") 
and code not in (select code from icd101 where (code between'M471'and'M472' or code in ('M494','S04','S14','S24','SS34','S44','S54','S64','S74','S84','S94','T903','T913','T924','T934'))))
/************************

Neurological 
ผู้ป่วยนอก
SELECT COUNT(p.vn)
FROM physic_main p, vn_stat v
WHERE p.vn = v.vn AND p.vstdate BETWEEN'2012-10-01'AND'2013-09-30' 
AND v.pdx IN (select code from icd101 where (code between "G00" and "G9999" or code between "H00" and "H9999" or code between "F01" and "F9999"))

ผู้ป่วยใน
SELECT COUNT(p.an)
FROM physic_main_ipd p, an_stat a
WHERE p.an = a.an AND p.vstdate BETWEEN'2012-10-01'AND'2013-09-30' 
AND a.pdx IN (select code from icd101 where (code between "G00" and "G9999" or code between "H00" and "H9999" or code between "F01" and "F9999"))
/************************

Cardiopulmonary 
ผู้ป่วยนอก
SELECT COUNT(p.vn)
FROM physic_main p, vn_stat v
WHERE p.vn = v.vn AND p.vstdate BETWEEN'2012-10-01'AND'2013-09-30' 
AND v.pdx IN (select code from icd101 where (code between "I00" and "I9999" or code between "J00" and "J9999") 
and code not in (select code from icd101 where (code between'P10'and'P1599' or code between'Q16'and'Q1999')))

ผู้ป่วยใน
SELECT COUNT(p.an)
FROM physic_main_ipd p, an_stat a
WHERE p.an = a.an AND p.vstdate BETWEEN'2012-10-01'AND'2013-09-30' 
AND a.pdx IN (select code from icd101 where (code between "I00" and "I9999" or code between "J00" and "J9999") 
and code not in (select code from icd101 where (code between'P10'and'P1599' or code between'Q16'and'Q1999')))
/************************

Miscellaneous 
ผู้ป่วยนอก
SELECT COUNT(p.vn)
FROM physic_main p, vn_stat v
WHERE p.vn = v.vn AND p.vstdate BETWEEN'2012-10-01'AND'2013-09-30' 
AND v.pdx IN (select code from icd101 where (code between "A00" and "B9999" or code between "E00" and "E8999" or code between "C00" and "D8999" or code between "P00" and "P9699"
or code between "Q00" and "Q9999" or code between "R00" and "R9999" or code between "Z00" and "Z9999") 
and code not in (select code from icd101 where (code between'A40'and'A9899' or code between'B909'and'B9689' or code between'C780'and'C7879' or code between'C793'and'C7999' 
or code between'R25'and'R2999' or code in ('R09','R56','R62'))))

ผู้ป่วยใน
SELECT COUNT(p.an)
FROM physic_main_ipd p, an_stat a
WHERE p.an = a.an AND p.vstdate BETWEEN'2012-10-01'AND'2013-09-30' 
AND a.pdx IN (select code from icd101 where (code between "A00" and "B9999" or code between "E00" and "E8999" or code between "C00" and "D8999" or code between "P00" and "P9699"
or code between "Q00" and "Q9999" or code between "R00" and "R9999" or code between "Z00" and "Z9999") 
and code not in (select code from icd101 where (code between'A40'and'A9899' or code between'B909'and'B9689' or code between'C780'and'C7879' or code between'C793'and'C7999' 
or code between'R25'and'R2999' or code in ('R09','R56','R62'))))

select lig.lab_items_group_name,li.lab_items_name,count(li.lab_items_name)
from lab_order lo
left join lab_head lh on lh.lab_order_number=lo.lab_order_number
left join lab_items li on li.lab_items_code=lo.lab_items_code
left join lab_items_group lig on lig.lab_items_group_code=li.lab_items_group
where lh.order_date between ‘2013-10-01’ and ‘2014-09-31’
group by lig.lab_items_group_name,li.lab_items_name