22,206
社区成员
发帖
与我相关
我的任务
分享
select w1.*,
w2.GID,w2.Name,w2.Model,w2.Unit,w2.MatlGID,w2.MatlName,w2.MatlModel,w2.QtyMatl,w2.CreateDate,
w3.Name as SName
from
(
select a.DeptName,a.SourceBillNo,a.EquipmentNumber,a.SID,sum(Number) as QtyPrtOut,
sum(case when a.GState='D20150312004' then a.Number else 0 end) as QtyQuad,
sum(case when a.GState='D20150312005' then a.Number else 0 end) as QtyNoQuad
from (
select C.Name as DeptName, a.* from Product_MadeTeInStockDetail a
inner join Product_MadeTeInStock b on a.PTIS=b.PTIS
inner join Hr_Dept c on b.DeptID=c.DeptID
where c.PrtSort='数控车床部'
) a group by a.DeptName,a.SourceBillNo,a.EquipmentNumber,a.SID
) w1 left join
(
select a.MOID,a.GID,a.CreateDate,
b.GID as MatlGID,b.QtyMatl,
c.Name as MatlName,c.Model as MatlModel,
d.Name,d.Model,d.Unit
from Product_MadeOrder a left join
(
select SourceBillNo,GID,sum(Number) as QtyMatl from Product_MadeSendMaterialDetail
group by SourceBillNo,GID
) b on a.MOID=b.SourceBillNo
left join Storage_Goods c on b.GID=c.GID
left join Storage_Goods d on a.GID=d.GID
)
w2 on w1.SourceBillNo=w2.MOID
left join Hr_Staff w3 on w1.SID=w3.SID
create index ix_Product_MadeSendMaterialDetail_SourceBillNo on dbo.Product_MadeSendMaterialDetail ( SourceBillNo )