工单工序报表

select a.Workorder,a.Processid,a.ProductName,a.qty_plan,b.qty_wo,
(case when ISNULL( c.qty_wip,0)-ISNULL( d.qty_bad,0)<0 then null else ISNULL( c.qty_wip,0)-ISNULL( d.qty_bad,0) end) qty_wip ,
a.Iterm,(b.qty_wo+isnull( c.qty_wip,0)) qty_touru,
d.qty_bad,e.Processname,g.qty_wo2 from
(select b.WorkOrder,a.ProcessID,b.Product ProductName,b.Qty qty_plan,a.Iterm from Routing a 
left join  Workorder_Job b on  a.Routid=b.Workorder
--where b.WorkOrder='SC17-200908018'
)a--
left join
(select WorkOrder, ProcessID,ProductName, COUNT (1) qty_wo from PCBBarCode_History  
--where WorkOrder='SC17-200908018' 
group by WorkOrder, ProcessID,ProductName)b--his 
on  a.WorkOrder=b.WorkOrder and a.ProcessID=b.ProcessID and a.ProductName=b.ProductName
left join
(select WorkOrder, ProcessID,ProductName, COUNT (1) qty_wip from PCBBarCode 
 where InputDateTime>'2020-09-15' 
 and Workorder='SC17-200908018'
  group by WorkOrder,ProcessID,ProductName)c--wip 
  on a.WorkOrder=c.WorkOrder and a.ProcessID=c.ProcessID and a.ProductName=c.ProductName
left join (
    select b.WorkOrder, a.ProcessID,b.ProductName,COUNT(1) qty_bad from V_AOI_SPI_Check a left join PCBBarCode b on a.BoardBarcode=b.BoardBarcode where a.BoardStatus='NG'
    --and Workorder='SC17-200908018'
    group by b.WorkOrder, a.ProcessID,b.ProductName
) d on d.WorkOrder=a.WorkOrder and d.ProcessID=a.ProcessID and d.ProductName=a.ProductName
left join Process e on e.Processid=a.ProcessID
left join (
    select b.WorkOrder, a.ProcessID,b.ProductName,COUNT(1) qty_wo2 from V_AOI_SPI_Check a left join PCBBarCode b on a.BoardBarcode=b.BoardBarcode where a.BoardStatus!='NG' 
    and b.Workorder='SC17-200908018'
    group by b.WorkOrder, a.ProcessID,b.ProductName
) g on g.WorkOrder=a.WorkOrder and g.ProcessID=a.ProcessID and g.ProductName=a.ProductName
where a.Workorder='SC17-200908018' order by a.Iterm