BMS-HOSxP Community
HOSxP => แจ้งปัญหา / ขอความช่วยเหลือ => ข้อความที่เริ่มโดย: onepiece ที่ ตุลาคม 30, 2016, 13:46:54 PM
-
ผมได้รับงานแรกให้หาข้อมูล คนไข้เบาหวาน ที่ตรวจ HBA1C ที่มีสิทธิ UC ของปีนี้ทั้งหมดครับ 1 ต.ค.58 - 30 ก.ย. 59
สิทธิ UC คือสิทธิประกันสุขภาพถ้วนหน้า หรือ บัตรทองใช่ไหมครับ ผมเอาข้อมูลมาถูกตารางไหมครับ
แล้วเขียน code แบบด้านล่าง ถูกไหมครับ
ขอรบกวนหน่อยครับ ผมพึ่งรู้จัก Hosxp ไม่กี่วันครับ แล้วก็ sql ก็ลืมหมดแล้วครับ
===================================================
แบบที่ 1
select ovst.hn ,concat(patient.pname,patient.fname,' ',patient.lname)as 'ª×èÍ-¹ÒÁÊ¡ØÅ',
pttype.pcode,lab_items.lab_items_code,lab_items.lab_items_name
from ovst
inner join clinicmember on clinicmember.hn = ovst.hn
inner join patient on ovst.hn = patient.hn
inner join lab_head on ovst.vn = lab_head.vn
inner join lab_order ON lab_order.lab_order_number = lab_head.lab_order_number
inner join lab_items ON lab_items.lab_items_code = lab_order.lab_items_code
inner join pttype on pttype.pttype = ovst.pttype
where lab_items.lab_items_code = '294' and ovst.pttype in(86,87,88,93,95,96,97,98,99) and ovst.vstdate between '2015-10-01'and '2016-09-30'
group by ovst.hn
ORDER BY ovst.hn
ผล 413 รายครับ
=======================================================================================================
แบบที่ 2
select ovst.hn ,concat(patient.pname,patient.fname,' ',patient.lname)as 'ชื่อ-นามสกุล',
vn_stat.pcode,lab_items.lab_items_code,lab_items.lab_items_name
from ovst
inner join clinicmember on clinicmember.hn = ovst.hn
inner join vn_stat on ovst.vn = vn_stat.vn
inner join patient on vn_stat.hn = patient.hn
inner join lab_head on vn_stat.vn = lab_head.vn
inner join lab_order on lab_order.lab_order_number = lab_head.lab_order_number
inner join lab_items on lab_items.lab_items_code = lab_order.lab_items_code
where lab_items.lab_items_code = '294' and ovst.pttype in(86,87,88,93,95,96,97,98,99) and (ovst.vstdate between '2015-10-01'and '2016-09-30' )
group by ovst.hn
ORDER BY ovst.hn
ผล 413 รายครับ
===============================================================================================
แบบที่ 3
select ovst.hn ,concat(patient.pname,patient.fname,' ',patient.lname)as 'ª×èÍ-¹ÒÁÊ¡ØÅ',
vn_stat.pcode,lab_items.lab_items_code,lab_items.lab_items_name
from ovst
inner join clinicmember on clinicmember.hn = ovst.hn
inner join vn_stat on ovst.vn = vn_stat.vn
inner join patient on vn_stat.hn = patient.hn
inner join lab_head on vn_stat.vn = lab_head.vn
inner join lab_order on lab_order.lab_order_number = lab_head.lab_order_number
inner join lab_items on lab_items.lab_items_code = lab_order.lab_items_code
where lab_items.lab_items_code = '294' and vn_stat.pcode ='UC' and (ovst.vstdate between '2015-10-01'and '2016-09-30' )
group by ovst.hn
ORDER BY ovst.hn
ผล 413 รายครับ
=====================================================================================
แบบที่ 4
select ovst.hn ,concat(patient.pname,patient.fname,' ',patient.lname)as '????-???????',
vn_stat.pcode,lab_items.lab_items_code,lab_items.lab_items_name
from ovst
left outer join clinicmember on clinicmember.hn = ovst.hn
left outer join vn_stat on ovst.vn = vn_stat.vn
left outer join patient on vn_stat.hn = patient.hn
left outer join lab_head on vn_stat.vn = lab_head.vn
left outer join lab_order on lab_order.lab_order_number = lab_head.lab_order_number
left outer join lab_items on lab_items.lab_items_code = lab_order.lab_items_code
where lab_items.lab_items_code = '294' and vn_stat.pcode ='UC' and (ovst.vstdate between '2015-10-01'and '2016-09-30' )
group by ovst.hn
ORDER BY ovst.hn
ผล 601 รายครับ
==============================================
ovst.pttype in(86,87,88,93,95,96,97,98,99) เป็นรหัสประเภทสิทธิการรักษาครับ
ไม่ทราบว่าอันไหนถูก ครับ
-
สิทธิ น่าจะลองใช้ตาราง pttype_spp จะได้ไม่ต้องมาเลือกรหัสสิทธิครับ
-
แบบนี้หรือป่าวครับ
select ovst.hn ,concat(patient.pname,patient.fname,' ',patient.lname)as '????-???????',
vn_stat.pcode,lab_items.lab_items_code,lab_items.lab_items_name ,lab_order.confirm, ovst.vstdate,
patient.informaddr
from ovst
inner join clinicmember on clinicmember.hn = ovst.hn
inner join vn_stat on ovst.vn = vn_stat.vn
inner join patient on vn_stat.hn = patient.hn
inner join lab_head on vn_stat.vn = lab_head.vn
inner join pttype on vn_stat.pttype =pttype.pttype
inner join pttype_spp on pttype.pttype_spp_id = pttype_spp.pttype_spp_id
inner join lab_order on lab_order.lab_order_number = lab_head.lab_order_number
inner join lab_items on lab_items.lab_items_code = lab_order.lab_items_code
where lab_items.lab_items_code = '294' and pttype_spp.pttype_spp_id = 3 and lab_order.confirm ="Y" and (ovst.vstdate between '2015-10-01'and '2016-09-30' )
group by ovst.hn
ORDER BY ovst.vstdate
-
หรือแบบถูกไหมครับ
select lab_head.vn,clinic_visit.vn,lab_head.hn,CONCAT(person.pname,person.fname," ",person.lname) as FULLNAME,
lab_head.report_date,lab_head.form_name,lab_head.lab_order_number,lab_order.lab_order_number,lab_order.lab_items_code,lab_items.lab_items_name, pttype.pcode,person.pttype
from lab_head
left outer join clinic_visit ON clinic_visit.vn=lab_head.vn
left outer join lab_order on lab_order.lab_order_number = lab_head.lab_order_number
left outer join lab_items on lab_items.lab_items_code = lab_order.lab_items_code
left outer join person on person.patient_hn = lab_head.hn
left outer join pttype on pttype.pttype=person.pttype
where lab_head.report_date between "2015-10-01" and "2016-09-30"
and clinic_visit.clinic = 001 and person.pttype in(86,87,88,93,95,96,97,98,99)
and lab_order.lab_items_code = 294
group by lab_head.vn
order by lab_head.report_date
-
หรือแบบนี้ครับ
SELECT ovst.hn, concat(patient.pname,patient.fname," ",patient.lname) AS FULLNAME,
lab_items.lab_items_name, lab_order.confirm,vn_stat.pcode,vn_stat.pdx,c.clinic, ovst.vstdate,
patient.informaddr
FROM ovst ovst
inner join clinicmember c on c.hn=ovst.hn
inner JOIN vn_stat ON ovst.vn = vn_stat.vn
INNER JOIN patient ON (vn_stat.hn = patient.hn)
INNER JOIN lab_head ON lab_head.vn = vn_stat.vn
INNER JOIN lab_order ON (lab_order.lab_order_number = lab_head.lab_order_number)
INNER JOIN lab_items ON lab_items.lab_items_code = lab_order.lab_items_code
INNER JOIN thaiaddress a on (a.chwpart=patient.chwpart and a.amppart=patient.amppart and a.tmbpart=patient.tmbpart)
WHERE lab_order.lab_items_code = '294' AND ovst.vstdate BETWEEN '2015-10-01' AND
'2016-09-30' AND ovst.vn = vn_stat.vn and c.clinic="001" and vn_stat.pcode ='UC'
and lab_order.confirm ="Y"
group by ovst.hn
ORDER BY ovst.vstdate