 |
|
ได้แล้ว ครับ ผม คิวรี่ใหม่ ครับ ขอบคุณครับ ครับ
SELECT med_items_id,namelab,nameunit,unitprice,AT1,AT11,AT111,BC1,BC11,BC111,JA11,JA111,KO11,KO111,(IFNULL(AT11,0)+IFNULL(BC11,0))as ABC11,(IFNULL(AT111,0)+IFNULL(BC111,0))as ABC111
from(SELECT mi.med_items_id,mi.namelab,mu.name nameunit,mi.unitprice
,mc.remain AT1
,SUM(DISTINCT IF(mc.date_update >='2017-05-01' AND mc.date_update <='2017-05-30' ,mc.remain,0)) AT11
,SUM(DISTINCT IF(mc.date_update >='2017-05-01' AND mc.date_update <='2017-05-30' ,(mc.remain*mi.unitprice),0)) AT111
,mro.med_amount BC1
,SUM(DISTINCT IF(mr.med_date_receive >='2017-06-01' AND mr.med_date_receive <='2017-06-30' ,mro.med_amount,0)) BC11
,SUM(DISTINCT IF(mr.med_date_receive >='2017-06-01' AND mr.med_date_receive <='2017-06-30' ,(mro.med_amount*mi.unitprice),0)) BC111
,KK.JA11
,(KK.JA11)*mi.unitprice JA111
,SUM(DISTINCT IF(mc.date_update >='2017-06-01' AND mc.date_update <='2017-06-30' ,mc.remain,0)) KO11
,SUM(DISTINCT IF(mc.date_update >='2017-06-01' AND mc.date_update <='2017-06-30' ,(mc.remain*mi.unitprice),0)) KO111
from med_items mi
LEFT OUTER JOIN med_cal mc ON mc.med_idlab = mi.med_items_id
LEFT OUTER JOIN med_receive_order mro ON mro.med_idlab = mi.med_items_id
LEFT OUTER JOIN med_receive mr ON mr.med_id = mro.codeorder
left OUTER join (select mi1.idlab,SUM(mi1.confirm) JA11
from med_inorder mi1
left join med_order mo1 on mo1.codeorder = mi1.codeorder
where mo1.dateconfirm between '2017-06-01'and'2017-06-30'
group by mi1.idlab
) KK on KK.idlab = mi.med_items_id
LEFT OUTER JOIN med_remain mr1 ON mr1.med_idlab = mi.med_items_id
LEFT OUTER JOIN med_unit mu ON mu.med_unit_id = mi.idunit
GROUP BY mi.med_items_id,mro.med_idlab
ORDER BY mi.namelab) ABC
|
 |
 |
 |
 |
| Date :
2017-07-25 09:35:24 |
By :
baby137 |
|
 |
 |
 |
 |
|
|
 |
Load balance : Server 03
