Query รายงานการใช้ยา HosXP + PCU

เปลี่ยนรหัสสถานพยาบาลให้ตรงตามแต่ละที่ด้วยนะครับ และตาราง stock_bdg_year ตรงตามปีงบ


select s.name as drugname
,(select sum(qty)as cc from opitemrece  o
left outer join stock_bdg_year s on (o.vstdate >= s.begin_date and o.vstdate <= s.end_date )
where s.bdg_year="2559" and month(o.vstdate)=10 and icode=s.icode )as "oct"
,(select sum(qty)as cc from opitemrece  o
left outer join stock_bdg_year s on (o.vstdate >= s.begin_date and o.vstdate <= s.end_date )
where s.bdg_year="2559" and month(o.vstdate)=11 and icode=s.icode )as "nov"
,(select sum(qty)as cc from opitemrece  o
left outer join stock_bdg_year s on (o.vstdate >= s.begin_date and o.vstdate <= s.end_date )
where s.bdg_year="2559" and month(o.vstdate)=12 and icode=s.icode )as "dece"
,(select sum(qty)as cc from opitemrece  o
left outer join stock_bdg_year s on (o.vstdate >= s.begin_date and o.vstdate <= s.end_date )
where s.bdg_year="2559" and month(o.vstdate)=1 and icode=s.icode )as "jan"
,(select sum(qty)as cc from opitemrece  o
left outer join stock_bdg_year s on (o.vstdate >= s.begin_date and o.vstdate <= s.end_date )
where s.bdg_year="2559" and month(o.vstdate)=2 and icode=s.icode )as "feb"
,(select sum(qty)as cc from opitemrece  o
left outer join stock_bdg_year s on (o.vstdate >= s.begin_date and o.vstdate <= s.end_date )
where s.bdg_year="2559" and month(o.vstdate)=3 and icode=s.icode )as "mar"
,(select sum(qty)as cc from opitemrece  o
left outer join stock_bdg_year s on (o.vstdate >= s.begin_date and o.vstdate <= s.end_date )
where s.bdg_year="2559" and month(o.vstdate)=4 and icode=s.icode )as "apr"
,(select sum(qty)as cc from opitemrece  o
left outer join stock_bdg_year s on (o.vstdate >= s.begin_date and o.vstdate <= s.end_date )
where s.bdg_year="2559" and month(o.vstdate)=5 and icode=s.icode )as "may"
,(select sum(qty)as cc from opitemrece  o
left outer join stock_bdg_year s on (o.vstdate >= s.begin_date and o.vstdate <= s.end_date )
where s.bdg_year="2559" and month(o.vstdate)=6 and icode=s.icode )as "june"
,(select sum(qty)as cc from opitemrece  o
left outer join stock_bdg_year s on (o.vstdate >= s.begin_date and o.vstdate <= s.end_date )
where s.bdg_year="2559" and month(o.vstdate)=7 and icode=s.icode )as "july"
,(select sum(qty)as cc from opitemrece  o
left outer join stock_bdg_year s on (o.vstdate >= s.begin_date and o.vstdate <= s.end_date )
where s.bdg_year="2559" and month(o.vstdate)=8 and icode=s.icode )as "aug"
,(select sum(qty)as cc from opitemrece  o
left outer join stock_bdg_year s on (o.vstdate >= s.begin_date and o.vstdate <= s.end_date )
where s.bdg_year="2559" and month(o.vstdate)=9 and icode=s.icode )as "sep"

from drugitems  s
where  istatus ="Y"