足缺料分析表
余年寄山水
861次浏览
2020年07月29日 18:29
最佳经验
本文由作者推荐
铺垫的作用-发现的同义词
Create Table #DATA (
FIndex int identity,
FItemID int null,
FStockQty decimal(28,10) default(0) null,
FSafeQty decimal(28,10) default(0) null,
FWillInQty decimal(28,10) default(0) null,
FWillOutQty decimal(28,10) default(0) null,
FWillDiffQty decimal(28,10) default(0) null,
FCUStockQty decimal(28,10) default(0) null,
FCUSafeQty decimal(28,10) default(0) null,
FCUWillInQty decimal(28,10) default(0) null,
FCUWillOutQty decimal(28,10) default(0) null,
FCUWillDiffQty decimal(28,10) default(0) null,
FOrderPoint decimal(28,10) default(0) null,
FBatFixQty decimal(28,10) default(0) null,
FLowQty decimal(28,10) default(0) null,
FCUOrderPoint decimal(28,10) default(0) null,
FCUBatFixQty decimal(28,10) default(0) null,
FCULowQty decimal(28,10) default(0) null,
FPutInQty decimal(28,10) default(0) null,
FPlanEndDate datetime null ,
FBillQty decimal(28,10) default(0) null,
FDailyConsume decimal(28,10) default(0) null,
FCUUnitName varchar(80) null default(''),
FLeadTime int null default(0),
FPlanBeginDate datetime null ,
FCUBillQty decimal(28,10) default(0) null,
FSumSort int default(0) null )
create index idxitemid on #DATA(FItemID)
Insert into #DATA (FItemID,FSafeQty,FOrderPoint,FBatFixQty,FLowQty,FDailyConsume,FLeadTime)
Select D FItemID,round(v,cimal) FSafeQty,
Point,xEconomy,mit,Consume,ime
from t_ICItem t1
Where (sID=1 or sID=2 or sID=3 or sID=7) and rtrim(r) >='aa' and rtrim(r) <='aaa' and ed<>1
Update u1 set Qty = isnull(Qty,0)
From #DATA u1, (select D,isnull(sum(IsNull(Qty,0)),0) FStockQty
, isnull(sum(IsNull(LockQty,0)),0) FStockLockQty
From ( Select 2 FStockType,ID, D FItemID, round( - isnull(,0),cimal) FStockQty
, round( isnull(,0),cimal) as FStockLockQty
From (select D,ID,sum() as FQty From ICInventory u1 Group by ID,D ) s1
inner join t_icitem s2 on ( D = D )
inner join t_Stock s3 on ( ID = D and ail = 1)
inner join ( select distinct FItemID From #DATA ) d1 on (d = D)
left join ( select D,ID,sum(case when isnull(,0) <= 0 then 0 else END) FQty
From t_lockstock u2 Group by ID,D ) s4
on ( Id = ID and D = D )
union
Select 1 FStockType,ID, D FItemID,sum(round(,cimal)) FStockQty
, 0 as FStockLockQty
From POInventory s1, t_ICItem s2, t_Stock s3, (select distinct FItemID F
rom #DATA) d1
Where ID = D a
nd ( D=502 or D=503 )
and D = D and D = D
Group by ID,D ) a Group by D ) u2
Where D = D