Set Nocount On
美轮美奂什么意思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)
Inrt into #DATA (FItemID,FSafeQty,FOrderPoint,FBatFixQty,FLowQty,FDailyConsume,FLeadTime)
喜爱反义词Select t1.FItemID FItemID,round(t1.FSecInv,t1.FQtyDecimal) FSafeQty,
好的写真摄影t1.FOrderPoint,t1.FBatFixEconomy,t1.FLowLimit,t1.FDailyConsume,t1.FLeadTime
不宜
from t_ICItem t1
Where (t1.FErpClsID=1 or t1.FErpClsID=2 or t1.FErpClsID=3 or t1.FErpClsID=7) and rtrim(t1.FNumber) >='aa' and rtrim(t1.FNumber) <='aaa' and t1.FDeleted<>1
Update u1 t u1.FStockQty = isnull(u2.FStockQty,0)
From #DATA u1, (lect a.FItemID,isnull(sum(IsNull(a.FStockQty,0)),0) FStockQty
, isnull(sum(IsNull(a.FStockLockQty,0)),0) FStockLockQty
From ( Select 2 FStockType,s1.FStockID, s1.FItemID FItemID, round(s1.FQty - isnull(s4.FQty,0),s2.FQtyDecimal) FStockQty
, round( isnull(s4.FQty,0),s2.FQtyDecimal) as FStockLockQty
From (lect u1.FItemID,u1.FStockID,sum(u1.FQty) as FQty From ICInventory u1 Group by u1.FStockID,u1.FItemID ) s1
inner join t_icitem s2 on ( s1.FItemID = s2.FItemID )
inner join t_Stock s3 on ( s1.FStockID = s3.FItemID and s3.FMRPAvail = 1)
inner join ( lect distinct FItemID From #DATA ) d1 on (s1.FItemId = d1.FItemID)
left join ( lect u2.FItemID,u2.FStockID,sum(ca when isnull(u2.FQty,0) <= 0 then 0 el u2.FQty END) FQty
From t_lockstock u2 Group by u2.FStockID,u2.FItemID ) s4
on ( s1.FStockId = s4.FStockID and s1.FItemID = s4.FItemID )
union
Select 1 FStockType,s1.FStockID, s1.FItemID FItemID,sum(round(s1.FQty,s2.FQtyDecimal)) FStockQty
, 0 as FStockLockQty
特仑苏是什么意思From POInventory s1, t_ICItem s2, t_Stock s3, (lect distinct FItemID F
rom #DATA) d1
Where s1.FStockID = s3.FItemID a
nd ( s3.FTypeID=502 or s3.FTypeID=503 )
and s1.FItemID = s2.FItemID and s2.FItemID = d1.FItemID
Group by s1.FStockID,s1.FItemID ) a Group by a.FItemID ) u2
Where u1.FItemID = u2.FItemID
