金蝶实际成本报表

 
 select t_ICItem.FNumber as  物料代码, t_ICItem.FName as 物料名称, CbcostObj.FNumber as 成本对象,  cbCompleteQty.FQty 完工数量, cbCostItem.FName as 成本项目,  cbCalInfo.FOverAmount 金额,cbCalInfo.FOverAmount / cbCompleteQty.FQty  as  单价  
   into #temp
 from   cbCalInfo   
 inner join  cbCostObj      on cbCalInfo.FCostObjID=cbCostobj.FItemID
 left join  cbCompleteQty   on cbCompleteQty.FCostObjID=cbCostobj.FItemID
 left join t_ICItem  on CbcostObj.FStdProductID=t_ICItem.FItemID 
  left join t_SubMessage on t_ICItem.FTypeID =t_SubMessage.FInterID and t_SubMessage.FTypeID =504
 inner join cbCostItem  on cbCostItem.FItemID=cbCalInfo.FCostItemID and cbCostItem.FDeleted=0 
  where  cbCalInfo.FYear=2023 and  cbCalInfo.FPeriod=3  and  cbCompleteQty.FYear=2023  and  cbCompleteQty.FPeriod=3  
  
  
  select  物料代码,物料名称,成本对象,完工数量 ,sum(直接材料) as 直接材料,sum(直接人工) as 直接人工,sum(制造费用) as 制造费用,sum(委外加工费) as 委外加工费,
  sum(直接材料)+sum(直接人工)+sum(制造费用)+sum(委外加工费) as  成本合计,sum(直接材料)+sum(直接人工)+sum(制造费用)+sum(委外加工费) /完工数量  as 成本单价
   from (
  select 物料代码,物料名称,成本对象,完工数量,
  ISNULL(直接材料,0)as 直接材料, ISNULL(直接人工,0)as 直接人工, ISNULL(制造费用,0)as 制造费用, ISNULL(委外加工费,0)as 委外加工费 from (  
  select  * from #temp as s
  pivot(max (金额)for 成本项目 in(直接材料,直接人工,制造费用,委外加工费)) as ss
  ) as  t ) as  tt
  group  by   物料代码,物料名称,成本对象,完工数量