采购及时率统计表

采购及时率统计表 --drop proc stkd_采购及时率统计表CREATE proc stkd_采购及时率统计 表 @begdate datetime,@enddate datetime as set nocount on create table#aa(finterid int,fentryid int,fnumber varchar(80)default(''),fname varchar(80)default(''),forderdate datetime default(1900-0-0),fdate datetime default(1900-0-0),fsu varchar(80)default(''),FstockQty decimal(28,10),fauxqty decimal(28,10),--采购数量frqty decimal(28,10),--如期入库数fnqty decimal(28,10),--逾期入库树fyqty decimal(28,10),--逾期未入数fmqty decimal(28,10),--未到交期数fstatus smallint) insert into#aa(finterid,fentryid,fnumber,fname,forderdate,fauxqty,FstockQty,fsu,fstatus)select u1.finterid,u1.fentryid,t2.fnumber,t2.fname,u1.FDate,u1.fauxqty,u1.FstockQty,t3.fname,v1.fstatus from POOrder v1 Inner Join POOrderEntry u1 on v1.FInterID=u1.FInterID left outer join t_Supplier t3 on v1.FSupplyID=t3.FItemID Inner Join t_ICItem t2 on u1.FItemID=t2.FItemID and v1.fheadselfp0237='Y'and v1.fcheckerid is not null and u1.FDate=@begdate and u1.FDate=@enddate-- u1.FDate=@begdate and u1.FDate=@enddate update ii set fdate=t3.fdate from#aa ii,(select v1.fdate,u1.fsourceentryid,u1.fsourceinterid,u1.fauxqtymust from ICStockBill v1 Inner Join ICStockBillEntry u1 on v1.FInterID=u1.FInterID where 1=1 And v1.FTranType=1)t3 where ii.finterid=t3.fsourceinterid and ii.fentryid=t3.fsourceentryid and t3.fauxqtymust=ii.fauxqty select fsu as供应商,FstockQty as入库数 量,case when FstockQty 0then fdate else null end as入库日 期,forderdate as采购交期,datediff(day,forderdate,case when FstockQty 0then fdate else null end)as迟到日期,frqty,fnqty,fyqty,fmqty,fstatus into#bb from#aa select count(*)as如期入库数,(select count(*)from#bb 2 0)as逾期入库数,(select count(*)from#bb where where迟到日期- datediff(day,getdate(),采购交期)0 and入库数量=0 and fstatus～=3)as逾 期未入库数,(select count(*)from#bb where datediff(day,getdate(),采购 交期)=0 and迟到日期is null and fstatus～=3)as未到交期单数into#cc from#bb where迟到日期-2=0 select如期入库数+逾期入库数+逾期未入库数应 入库数,如期入库数,逾期入库数,逾期未入库数,未到交期单数from#cc drop table#aa,#bb,#cc GO