--人工
create proc wfg_sp_S_Artificial(@iYear int ,@iPeriod int)
as
set nocount on
set ansi_warnings off
--查出劳动保险费,按一级部门进行分类
--select sum(FAmt) FInsuranceAmt ,FCategory from wfg_t_B_Insurance_Basic group by FCategory
--查出各车间产量
--select FDepart,FYield from wfg_t_B_Yield_Basic
select a.FCategory '一级部门',
sum(a.FNumber) '车间人数',
sum(a.Fshiji) '总工时',
sum(a.FWagesSholdbe) '工资',
b.FInsuranceAmt '劳动保险费',
sum(a.FWagesSholdbe)+b.FInsuranceAmt '总人工成本',
case when sum(a.FNumber)=0 then 0 else convert(decimal(18,2),((sum(a.FWagesSholdbe)+b.FInsuranceAmt)/sum(a.FNumber))) end '平均人工成本',
c.FYield '实际产量',
case when sum(a.FNumber)=0 then 0 else convert(decimal(18,2),(c.FYield/sum(a.FNumber))) end '人均产量',
case when sum(a.FNumber)=0 then 0 else convert(decimal(18,2),(sum(a.Fshiji)/sum(a.FNumber))) end '小时产量',
case when c.FYield=0 then 0 else convert(decimal(18,2),((sum(a.FWagesSholdbe)+b.FInsuranceAmt)/c.FYield)) '单位人工成本' from wfg_t_B_Performance_Basic a
inner join (select sum(FAmt) FInsuranceAmt,FCategory from wfg_t_B_Insurance_Basic group by FCategory) b on a.FCategory=b.FCategory inner join (select FDepart,FYield from wfg_t_B_Yield_Basic) c on a.FCategory=c.FCategory报错----------------
消息 102,级别 15,状态 1,过程 wfg_sp_S_Artificial,第 20 行
'单位人工成本' 附近有语法错误。
消息 102,级别 15,状态 1,过程 wfg_sp_S_Artificial,第 21 行
'b' 附近有语法错误。
消息 102,级别 15,状态 1,过程 wfg_sp_S_Artificial,第 21 行
'c' 附近有语法错误。为什么啊
create proc wfg_sp_S_Artificial(@iYear int ,@iPeriod int)
as
set nocount on
set ansi_warnings off
--查出劳动保险费,按一级部门进行分类
--select sum(FAmt) FInsuranceAmt ,FCategory from wfg_t_B_Insurance_Basic group by FCategory
--查出各车间产量
--select FDepart,FYield from wfg_t_B_Yield_Basic
select a.FCategory '一级部门',
sum(a.FNumber) '车间人数',
sum(a.Fshiji) '总工时',
sum(a.FWagesSholdbe) '工资',
b.FInsuranceAmt '劳动保险费',
sum(a.FWagesSholdbe)+b.FInsuranceAmt '总人工成本',
case when sum(a.FNumber)=0 then 0 else convert(decimal(18,2),((sum(a.FWagesSholdbe)+b.FInsuranceAmt)/sum(a.FNumber))) end '平均人工成本',
c.FYield '实际产量',
case when sum(a.FNumber)=0 then 0 else convert(decimal(18,2),(c.FYield/sum(a.FNumber))) end '人均产量',
case when sum(a.FNumber)=0 then 0 else convert(decimal(18,2),(sum(a.Fshiji)/sum(a.FNumber))) end '小时产量',
case when c.FYield=0 then 0 else convert(decimal(18,2),((sum(a.FWagesSholdbe)+b.FInsuranceAmt)/c.FYield)) end '单位人工成本' from wfg_t_B_Performance_Basic a
inner join (select sum(FAmt) FInsuranceAmt,FCategory from wfg_t_B_Insurance_Basic group by FCategory) b on a.FCategory=b.FCategory inner join (select FDepart,FYield from wfg_t_B_Yield_Basic) c on a.FCategory=c.FCategory