มือใหม่ครับ ขอให้ช่วยตรวจสอบ code ว่าถูกต้องไหมครับ

[onepiece]

ผมได้รับงานแรกให้หาข้อมูล คนไข้เบาหวาน ที่ตรวจ 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) เป็นรหัสประเภทสิทธิการรักษาครับ

ไม่ทราบว่าอันไหนถูก  ครับ

[golf_win]

สิทธิ น่าจะลองใช้ตาราง pttype_spp จะได้ไม่ต้องมาเลือกรหัสสิทธิครับ

[onepiece]

แบบนี้หรือป่าวครับ

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

[onepiece]

หรือแบบถูกไหมครับ

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

[onepiece]

หรือแบบนี้ครับ

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