SAP ME 捉取料號各工站在途數統計數量 SQL

 sql

select site, item, step_sequence, opt, sum(in_work) as in_work

from (

select so.site, i.item, ss.step_sequence, concat(o.operation ,ot.description) as opt, (ss.qty_in_queue + ss.qty_in_work) as in_work

from wip.wip_sfc sfc1, wip.SFC_ROUTING srg, wip.SFC_ROUTER srr

   ,wip.SFC_STEP ss

   ,wip.SHOP_ORDER_SFC sos, wip.SHOP_ORDER so

   ,wip.STATUS S

   ,wip.STATUS S2

   ,wip.ITEM i

   , wip.OPERATION o

   , wip.OPERATION_T ot

   where 1=1

   and sfc1.status_bo = s2.handle and s2.status_description in ('Active','New','InQueue')

   and sfc1.handle = srg.sfc_bo

   and srg.handle = srr.sfc_routing_bo

   and srr.handle = ss.sfc_router_bo   

   and sos.sfc_bo = sfc1.handle

   and sos.shop_order_bo = so.handle

   and so.item_bo = i.handle

   and so.STATUS_BO = s.handle and s.STATUS_DESCRIPTION like 'Releas%'

   and LEFT (ss.operation_bo,22) = LEFT (o.handle,22) 

   and o.handle = ot.operation_bo and locale = 'zh_tw'

   and so.Shop_Order_Type_BO like '%PRODUCTION%' 

   and i.item = '02' 

   and (ss.done <> true and  (ss.qty_in_queue <> 0 or ss.qty_in_work <> 0))

  ) as q1 

group by   site, item,  step_sequence, opt