一天一报表之生产任务统计表 已经公开 2007年8月23日 9:04 <上一篇 | 下一篇>
昨天朋友的私生活出现了严重问题，开解了大半天；外面的雨一直下个不停，自己又病了，心情比较烦躁。台风圣帕虽然没对这里产生什么影响，不过这降雨，什么时候才会停呢。
今天继续一天一报表，发一张生产的统计表，按照上月、上季度、上年和本月的生产任务情况统计对比
调用方法：查询分析工具，新建报表，输入：stkd_生产任务统计表 '********','########','*ItemNo*','#ItemNo#'
使用版本：应该所有版本都适用，在v10.2以上环境测试通过
程序源码：
create proc stkd_生产任务统计表 
@begdate datetime,
@enddate datetime,
@begitem varchar(80),
@enditem varchar(80)
as
set nocount on
create table #aa(
fitemid int,
fnumber varchar(80),
fname varchar(80),
fmodel varchar(50),
fplanqty decimal(18,2) default(0),
ffinishqty decimal(18,2) default(0),
forderqty decimal(18,2) default(0),
flastmonthplanqty decimal(18,2) default(0),
flastmonthfinishqty decimal(18,2) default(0),
flastmonthorderqty decimal(18,2) default(0),
flastyearplanqty decimal(18,2) default(0),
flastyearfinishqty decimal(18,2) default(0),
flastyearorderqty decimal(18,2) default(0),
faplanqty decimal(18,2) default(0),
fafinishqty decimal(18,2) default(0),
faorderqty decimal(18,2) default(0)
)
insert into #aa(fitemid,fnumber,fname,fmodel)
select fitemid,fnumber,fname,fmodel
from t_icitem
where fnumber>=@begitem and fnumber<=@enditem
update t1
set t1.fplanqty=t2.fqty,t1.ffinishqty=t2.fqtyfinish,t1.flastmonthplanqty=t2.flastmonthqty,t1.flastmonthfinishqty=t2.flastmonthfinishqty,
t1.flastyearplanqty=t2.flastyearqty,t1.flastyearfinishqty=t2.flastyearfinishqty,t1.faplanqty=t2.faqty,t1.fafinishqty=t2.fafinishqty
from #aa t1,(
select fitemid,
sum(case when fplancommitdate>=@begdate and fplancommitdate<=@enddate then fqty else 0 end) as fqty,
sum(case when fplancommitdate>=@begdate and fplancommitdate<=@enddate then fqtyfinish else 0 end) as fqtyfinish,
sum(case when fplancommitdate>=dateadd(month,-1,@begdate) and fplancommitdate<=dateadd(month,-1,@enddate)
then fqty else 0 end) as flastmonthqty,
sum(case when fplancommitdate>=dateadd(month,-1,@begdate) and fplancommitdate<=dateadd(month,-1,@enddate)
then fqtyfinish else 0 end) as flastmonthfinishqty,
sum(case when fplancommitdate>=dateadd(month,-3,@begdate) and fplancommitdate<=dateadd(month,-3,@enddate)
then fqty else 0 end) as faqty,
sum(case when fplancommitdate>=dateadd(month,-3,@begdate) and fplancommitdate<=dateadd(month,-3,@enddate)
then fqtyfinish else 0 end) as fafinishqty,
sum(case when fplancommitdate>=dateadd(year,-1,@begdate) and fplancommitdate<=dateadd(year,-1,@enddate)
then fqty else 0 end) as flastyearqty,
sum(case when fplancommitdate>=dateadd(year,-1,@begdate) and fplancommitdate<=dateadd(year,-1,@enddate)
then fqtyfinish else 0 end) as flastyearfinishqty
from icmo where fstatus>0
group by fitemid) t2
where t1.fitemid=t2.fitemid
--select * from icmo
update t1
set t1.forderqty=t2.fqty,t1.flastmonthorderqty=t2.flastmonthqty,t1.flastyearorderqty=t2.flastyearqty,t1.faorderqty=t2.faqty
from #aa t1,(
select ibc.fitemid,
sum(case when se.fdate>=@begdate and se.fdate<=@enddate then see.fqty else 0 end) as fqty,
sum(case when se.fdate>=dateadd(month,-1,@begdate) and se.fdate<=dateadd(month,-1,@enddate)
then ibc.fqty else 0 end) as flastmonthqty,
sum(case when se.fdate>=dateadd(month,-3,@begdate) and se.fdate<=dateadd(month,-3,@enddate)
then ibc.fqty else 0 end) as faqty,
sum(case when se.fdate>=dateadd(year,-1,@begdate) and se.fdate<=dateadd(year,-1,@enddate)
then ibc.fqty else 0 end) as flastyearqty
from seorder se,seorderentry see,icbom ib,icbomchild ibc
where se.finterid=see.finterid and ib.finterid=ibc.finterid and see.fitemid=ib.fitemid
group by ibc.fitemid) t2
where t1.fitemid=t2.fitemid
delete from #aa where fplanqty=0 and forderqty=0
insert into #aa(fnumber,fplanqty,ffinishqty,forderqty,
flastmonthplanqty,flastmonthfinishqty,flastmonthorderqty,
faplanqty,fafinishqty,faorderqty,
flastyearplanqty,flastyearfinishqty,flastyearorderqty)
select '合计',sum(fplanqty),sum(ffinishqty),sum(forderqty),
sum(flastmonthplanqty),sum(flastmonthfinishqty),sum(flastmonthorderqty),
sum(faplanqty),sum(fafinishqty),sum(faorderqty),
sum(flastyearplanqty),sum(flastyearfinishqty),sum(flastyearorderqty)
from #aa
select fnumber as 物料代码,fname as 物料名称,fmodel as 规格型号,fplanqty as 计划数量,ffinishqty as 完成数量,forderqty as 订单数量,
flastmonthplanqty as 上月计划,flastmonthfinishqty as 上月完成,flastmonthorderqty as 上月订单,
faplanqty as 上季计划,fafinishqty as 上季完成,faorderqty as 上季订单,
flastyearplanqty as 上年计划,flastyearfinishqty as 上年完成,flastyearorderqty as 上年订单
from #aa order by fnumber
drop table #aa
图例：点击看大图
请登录后再发表评论以赚取更多积分
|
|
|