594
社区成员
发帖
与我相关
我的任务
分享
declare @date date set @date='2015-11-01'
declare @date1 date set @date1=null
declare @ddh varchar(255) set @ddh=null
declare @kh varchar(255) set @kh=null
declare @lb varchar(255) set @lb=null
declare @cj varchar(255) set @cj=null
select dense_rank()over(order by b.field0037 desc)xh,b.field0037,b.field0032,b.field0034,h.field0002 as field0035,isnull(h.field0005,0) jdzjg,a.field0042
,case when k.gxs=0 or k.gxs is null then sum(isnull(b.field0030,0))
else sum(isnull(b.field0030,0))/k.gxs end as cl
,case when k.gxs=0 then '已完成'
when k.gxs<>0 and(sum(isnull(b.field0030,0))/k.gxs )/h.field0002=1 then '已完成'
when k.gxs<>0 and(sum(isnull(b.field0030,0))/k.gxs )/h.field0002>1 then '生产超标'
else cast(cast(((sum(isnull(b.field0030,0))/k.gxs )/h.field0002)*100 as numeric(8,2)) as varchar)+'%' --'未完成'
end as bz --备注(当月是否完工)
,(sum(ISNULL(b.field0029,0))+sum(ISNULL(b.field0036,0)))/8 as zcyg --正常用工
,isnull(j.lsggs,0) lsggs --临时工工时
,isnull(j.wosi,0) wosi
,isnull(j.waifa,0) waifa
,replace(replace(replace(replace((select d.wosiyy from
(select distinct b3.field0037,b3.field0032,b3.field0034,a3.field0042
,case when b3.field0028>0 or b3.field0038>0 then c3.showValue else null end AS wosiyy
,case when b3.field0040>0 or b3.field0039>0 then c3.showValue else null end as waifayy
from formmain_0439 a3
left join formson_0440 b3 on a3.id=b3.formmain_0439Id
left join (select a3.enumname,b3.enumvalue,b3.showValue
from form_enumlist a3,form_enumvalue b3
where a3.id=b3.ref_enumid and a3.enumname='异常用工原因说明'
and b3.enumvalue<>4
)c3 on c3.enumvalue=a3.field0003
where b3.field0037 is not null and a3.field0003 is not null
and(b3.field0039 is not null or b3.field0028 is not null or b3.field0038 is not null or b3.field0040 is not null)
and(a3.field0027>=@date or @date is null)
and(a3.field0027<=@date1 or @date1 is null)
and(b3.field0037 like '%'+@ddh+'%' or @ddh is null)
and(b3.field0032 like '%'+@kh+'%' or @kh is null)
and(a3.field0042 like '%'+@cj+'%' or @cj is null)
) d
where d.field0032=b.field0032 and d.field0034=b.field0034 and d.field0042=a.field0042
and d.field0037=b.field0037 for xml auto),'<d/>',''),'"/><d wosiyy="','、'),'<d wosiyy="',''),'"/>',''
)as wosiyy
,replace(replace(replace(replace((select d.waifayy from
(select distinct b3.field0037,b3.field0032,b3.field0034,a3.field0042
,case when b3.field0028>0 or b3.field0038>0 then c3.showValue else null end AS wosiyy
,case when b3.field0040>0 or b3.field0039>0 then c3.showValue else null end as waifayy
from formmain_0439 a3
left join formson_0440 b3 on a3.id=b3.formmain_0439Id
left join (select a3.enumname,b3.enumvalue,b3.showValue
from form_enumlist a3,form_enumvalue b3
where a3.id=b3.ref_enumid and a3.enumname='异常用工原因说明'
and b3.enumvalue<>4
) c3 on c3.enumvalue=a3.field0003
where b3.field0037 is not null and a3.field0003 is not null
and (b3.field0039 is not null or b3.field0028 is not null or b3.field0038 is not null or b3.field0040 is not null)
and(a3.field0027>=@date or @date is null)
and(a3.field0027<=@date1 or @date1 is null)
and(b3.field0037 like '%'+@ddh+'%' or @ddh is null)
and(b3.field0032 like '%'+@kh+'%' or @kh is null)
and(a3.field0042 like '%'+@cj+'%' or @cj is null)
) d
where d.field0032=b.field0032 and d.field0034=b.field0034 and d.field0042=a.field0042
and d.field0037=b.field0037 for xml auto),'<d/>',''),'"/><d waifayy="','、'),'<d waifayy="',''),'"/>',''
)as waifayy
,(ISNULL(sum(b.field0029),0)+isnull(sum(b.field0036),0))/8+isnull(j.wosi,0)+isnull(j.waifa,0) as zsg --合计用工 正式工
,case when isnull(sum(b.field0029),0)+isnull(sum(b.field0036),0)=0 then 0
else (isnull(sum(b.field0030),0)/k.gxs)/((isnull(sum(b.field0029),0)+isnull(sum(b.field0036),0))/8)
end as rjcn --人均产能(打/人工)
,sum((isnull(a.field0006,0)+isnull(a.field0007,0))*(isnull(b.field0029,0)+isnull(b.field0036,0))/(case a.field0009 when 0
then 1 else a.field0009 end)) as ddgz --订单工资
,isnull(g1.btgz,0) btgz --订单补贴工资
, sum((isnull(b.field0028,0)+isnull(b.field0038,0)+isnull(b.field0039,0)+isnull(b.field0040,0))*a.field0004/(case a.field0005
when 0 then 1 else a.field0005 end)) as yycggz --异常用工工资
,isnull(j.ycyggz,0)ycyggz
,isnull(j.lsggz,0)lsggz
,isnull(g1.bgf,0) bgf --办公费
,isnull(g1.sf,0) sf --水费
,isnull(g1.df,0) df --电费
,isnull(g1.jwlf,0) jwlf --机物料费
,isnull(g1.dypf,0) dypf --低易品费
,isnull(g1.clf,0) clf --材料费
,isnull(g1.cljl,0) cljl --超料节料
,replace(replace(replace(replace(replace((select a1.yysm from
(select bb.field0008 nf,bb.field0009 yf,cl.field0018,cl.field0017,aa.showValue as field0042,cast(cl.field0014 as varchar)as yysm
from formson_0502 cl
left join (select a.id,a.enumname,b.showValue,b.enumvalue from form_enumlist a, form_enumvalue b where a.id=b.ref_enumid and a.enumname='车间') aa on cl.field0011=aa.enumvalue
left join formmain_0501 bb on bb.id=cl.formmain_0501Id
--where cl.field0018 ='SE1505-50'
)a1 where a1.field0018=b.field0037 and a1.field0017=b.field0032 and a1.field0042=a.field0042
and (a1.yf>=month(@date) or @date is null)
and (a1.yf<=month(@date1)or @date1 is null)
and (a1.nf>=YEAR(@date)or @date is null)
and (a1.nf<=YEAR(@date1)or @date is null)
and (a1.field0018 like '%'+@kh+'%' or @kh is null)
and (a1.field0042 like '%'+@cj+'%' or @cj is null)
for xml auto),'<a1/>',''),'"/><a1 yysm="','、'),'',''),'<a1 yysm="',''),'"/>',''
)as yysm
, sum((isnull(a.field0006,0)+isnull(a.field0007,0))*(isnull(b.field0029,0)+isnull(b.field0036,0))/(case a.field0009 when 0
then 1 else a.field0009 end))+isnull(g1.btgz,0)+sum((isnull(b.field0028,0)+isnull(b.field0038,0)+isnull(b.field0039,0)+isnull(b.field0040,0))*a.field0004/(case a.field0005
when 0 then 1 else a.field0005 end))+isnull(g1.bgf,0)+isnull(g1.sf,0)+isnull(g1.df,0)+isnull(g1.jwlf,0)+isnull(g1.dypf,0)+isnull(g1.clf,0)+isnull(g1.cljl,0) as hj --合计
--缝纫车间产量及费用分析表
ALTER PROCEDURE [dbo].[YKFXB]
@ddh varchar(255),
@kh varchar(255),
@lb varchar(255),
@cj varchar(255),
@date date,@date1 date
AS
begin
declare @vardate date set @vardate=@date
declare @vardate1 date set @vardate1=@date1
declare @varddh varchar(255) set @varddh=@ddh
declare @varkh varchar(255) set @varkh=@kh
declare @varlb varchar(255) set @varlb=@lb
declare @varcj varchar(255) set @varcj=@cj
declare @Ndate date
set @Ndate=@date
select * from tb where date>@Ndate
exec YKFXB NULL,NULL,NULL,NULL,'2015-11-1',NULL
(20 行受影响)
SQL Server Execution Times:
CPU time = 350721 ms, elapsed time = 351574 ms.
SQL Server Execution Times:
CPU time = 350721 ms, elapsed time = 351575 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
from formmain_0439 a
left join formson_0440 b on a.id=b.formmain_0439Id
--订单车间材料费用工时比例明细表
left join (select a.field0037,a.field0032,a.field0034,a.field0042,a.clf,a.cljl,a.btgz,sum(a.bgf) bgf,sum(a.df)df,sum(a.dypf)dypf,sum(a.jwlf)jwlf,sum(a.sf)sf from
(select a.nf,a.mf,a.field0037,a.field0032,a.field0034,a.field0042,b.clf,b.cljl,b.btgz,c.bgf*a.bl as bgf,c.df*a.bl as df,c.dypf*a.bl as dypf,c.jwlf*a.bl as jwlf,c.sf*a.bl as sf
--车间订单工时比例
from(select a.nf,a.mf,a.field0037,a.field0032,a.field0034,a.field0042,a.zsg as ddzgs,b.zsg as cjzgs,a.zsg/b.zsg as bl
from(select YEAR(a3.field0027)as nf,MONTH(a3.field0027)as mf,b3.field0037,b3.field0032,b3.field0034,a3.field0042,ISNULL(sum(b3.field0029),0)+isnull(sum(b3.field0036),0)+ ISNULL(SUM(b3.field0028),0)+ISNULL(SUM(b3.field0038),0)+ISNULL(SUM(b3.field0039),0)+ISNULL(SUM(b3.field0040),0)as zsg --合计用工 正式工
from formmain_0439 a3, formson_0440 b3
where a3.id=b3.formmain_0439Id and b3.field0037 is not null
and (b3.field0037 like '%'+@ddh+'%' or @ddh is null)
and (b3.field0032 like '%'+@kh+'%' or @kh is null)
and (b3.field0034 like '%'+@lb+'%' or @lb is null)
and (a3.field0042 like '%'+@cj+'%' or @cj is null)
and (a3.field0027 >= @date or @date is null ) --起始日期
and (a3.field0027 <= @date1 or @date1 is null ) --截止日期
group by YEAR(a3.field0027),MONTH(a3.field0027),b3.field0037,a3.field0042,b3.field0032,b3.field0034
)a left join(select YEAR(a4.field0027)as nf,MONTH(a4.field0027) as mf,a4.field0042,ISNULL(sum(b4.field0029),0)+isnull(sum(b4.field0036),0)+ ISNULL(SUM(b4.field0028),0)+ISNULL(SUM(b4.field0038),0)+ISNULL(SUM(b4.field0039),0)+ISNULL(SUM(b4.field0040),0) as zsg --合计用工 正式工
from formmain_0439 a4, formson_0440 b4
where a4.id=b4.formmain_0439Id
group by YEAR(a4.field0027),MONTH(a4.field0027),a4.field0042
)b on a.mf=b.mf and a.nf=b.nf and a.field0042=b.field0042
)a
--材料费用\超料节料\补贴工资
left join(select cl.field0018,cl.field0017,aa.showValue as field0042,cl.field0012 as clf,cl.field0015 as cljl,cl.field0016 as btgz,cl.field0013 as fyhj
from formson_0502 cl
left join(select a.id,a.enumname,b.showValue,b.enumvalue
from form_enumlist a, form_enumvalue b where a.id=b.ref_enumid and a.enumname='车间'
)aa on cl.field0011=aa.enumvalue
left join formmain_0501 bb on bb.id=cl.formmain_0501Id
--where cl.field0018 ='SE1509-45'
)b on a.field0037=b.field0018 and a.field0032=b.field0017 and a.field0042=b.field0042
--车间费用表
left join(select i.field0004 nf,i.field0005 yf,k.showValue,j.field0016 as bgf,j.field0015 as sf,j.field0013 as df,j.field0012 as jwlf,j.field0011 as dypf
from formmain_0499 i
left join formson_0500 j on i.id=j.formmain_0499Id
--车间枚举表
left join(select a.id,a.enumname,b.showValue,b.enumvalue from form_enumlist a, form_enumvalue b where a.id=b.ref_enumid and a.enumname='车间'
)k on j.field0010=k.enumvalue
)c on a.nf=c.nf and a.mf=c.yf and a.field0042=c.showValue
)a group by a.field0037,a.field0032,a.field0034,a.field0042,a.clf,a.cljl,a.btgz
) g1 on g1.field0037=b.field0037 and g1.field0042=a.field0042
left join formmain_0449 h on h.field0001=b.field0037
left join (select a.field0002,a.field0005
,case when b.gxs1 is null and c.gxs2 is null then COUNT(a.field0003)-ISNULL(d.gxs3,0)
when b.gxs1 is null and c.gxs2 is not null then COUNT(a.field0003)-c.gxs2-ISNULL(d.gxs3,0)+1
when b.gxs1 is not null and c.gxs2 is null then COUNT(a.field0003)-b.gxs1-ISNULL(d.gxs3,0)+1
else COUNT(a.field0003)-b.gxs1-c.gxs2-ISNULL(d.gxs3,0)+2 end as gxs
from formmain_0447 a
left join(select a.field0002,a.field0005,COUNT(a.field0003) gxs1
from formmain_0447 a
where field0003 like '%上箱%' and a.field0005='包装'
group by a.field0002,a.field0005 having COUNT(a.field0003)>=2
)b on a.field0002=b.field0002 and a.field0005=b.field0005
left join(select a.field0002,a.field0005,COUNT(a.field0003) gxs2
from formmain_0447 a
where field0003 like '%吊卡%' and a.field0005='包装'
group by a.field0002,a.field0005
having COUNT(a.field0003)>=2
)c on c.field0002=a.field0002 and c.field0005=a.field0005
left join(select a.field0002,a.field0005,COUNT(a.field0003) gxs3
from formmain_0447 a
where field0003 like '%异常用工%'
group by a.field0002,a.field0005
)d on d.field0002=a.field0002 and d.field0005=a.field0005
--WHERE a.field0002='MAC186'
group by a.field0002,a.field0005,b.gxs1,c.gxs2,d.gxs3
)k on k.field0002=b.field0032 and k.field0005=a.field0042
left join (select a.field0037,a.field0032,a.field0034,a.field0042,SUM(a.lsggs)/8 as lsggs,SUM(a.lsggz)/8 as lsggz,SUM(a.wosi)/8 as wosi,SUM(a.waifa)/8 as waifa,SUM(a.ycyggz)as ycyggz
from(select b.field0037,b.field0032,b.field0034,a.field0042
,case when a.field0003='4'
then SUM(ISNULL(b.field0028,0))+SUM(ISNULL(b.field0038,0))+SUM(ISNULL(b.field0039,0))+SUM(ISNULL(b.field0040,0))
else 0 end as lsggs
,case when a.field0003='4'
then sum((isnull(b.field0028,0)+isnull(b.field0038,0)+isnull(b.field0039,0)+isnull(b.field0040,0))*a.field0004/(case a.field0005 when 0 then 1 else a.field0005 end))
else 0 end as lsggz
,case when (a.field0003 is null or a.field0003<>4)
then SUM(ISNULL(b.field0028,0))+SUM(ISNULL(b.field0038,0))
else 0 end as wosi
,case when (a.field0003 is null or a.field0003<>4)
then SUM(ISNULL(b.field0039,0))+SUM(ISNULL(b.field0040,0))
else 0 end as waifa
,case when (a.field0003 is null or a.field0003<>4)
then sum((isnull(b.field0028,0)+isnull(b.field0038,0)+isnull(b.field0039,0)+isnull(b.field0040,0))*a.field0004/(case a.field0005 when 0 then 1 else a.field0005 end))
else 0 end as ycyggz
from formmain_0439 a left join formson_0440 b on a.id=b.formmain_0439Id
where --a.field0027>='2015-10-01' and b.field0037='SE1508-15'
(b.field0037 like '%'+@ddh+'%' or @ddh is null)
and (b.field0032 like '%'+@kh+'%' or @kh is null)
and (b.field0034 like '%'+@lb+'%' or @lb is null)
and (a.field0042 like '%'+@cj+'%' or @cj is null)
and (a.field0027 >= @date or @date is null ) --起始日期
and (a.field0027 <= @date1 or @date1 is null ) --截止日期
group by b.field0037,b.field0032,a.field0042,b.field0034,a.field0003
having SUM(ISNULL(b.field0028,0))+SUM(ISNULL(b.field0038,0))+SUM(ISNULL(b.field0039,0))+SUM(ISNULL(b.field0040,0))>0
)a group by a.field0037,a.field0032,a.field0034,a.field0042
)j on j.field0037=b.field0037 and j.field0032=b.field0032 and j.field0034=b.field0034 and j.field0042=a.field0042
where b.field0037 is not null
and (b.field0037 like '%'+@ddh+'%' or @ddh is null)
and (b.field0032 like '%'+@kh+'%' or @kh is null)
and (b.field0034 like '%'+@lb+'%' or @lb is null)
and (a.field0042 like '%'+@cj+'%' or @cj is null)
and (field0027 >= @date or @date is null ) --起始日期
and (field0027 <= @date1 or @date1 is null ) --截止日期
group by b.field0037,b.field0032,b.field0034,h.field0005,a.field0042,g1.btgz
,g1.clf,g1.cljl,g1.bgf,g1.sf,g1.df,g1.dypf,g1.jwlf
,k.gxs,j.lsggs,j.lsggz,j.wosi,j.waifa,j.ycyggz,h.field0002
order by b.field0037 desc
(20 行受影响)
SQL Server Execution Times:
CPU time = 2200 ms, elapsed time = 2208 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.