金蝶K3库存账龄分析表（优化）

最新推荐文章于 2023-02-08 14:04:47 发布
最新推荐文章于 2023-02-08 14:04:47 发布
阅读量4.4k 收藏 6
点赞数
分类专栏： 金蝶K3 文章标签： 金蝶 K3 库存账龄 分析
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hzfw2008/article/details/78645223
版权 
Set NoCount On 
 SET ANSI_WARNINGS OFF 
 Create Table #Happen2(
        FItemID int Null, 
        FStockID int Null, 
        FBatchNo NVARCHAR(200), 
        FQty decimal(28,10) Null, 
        FCUUnitQty decimal(28,10) Null, 
        FQty1 Decimal(28,10), 
        FCUUnitQty1 Decimal(28,10), 
        FAmount1 Decimal(28,10), 
        FQty2 Decimal(28,10), 
        FCUUnitQty2 Decimal(28,10), 
        FAmount2 Decimal(28,10), 
        FQty3 Decimal(28,10), 
        FCUUnitQty3 Decimal(28,10), 
        FAmount3 Decimal(28,10), 
        FQty4 Decimal(28,10), 
        FCUUnitQty4 Decimal(28,10), 
        FAmount4 Decimal(28,10), 
        FQty5 Decimal(28,10), 
        FCUUnitQty5 Decimal(28,10), 
        FAmount5 Decimal(28,10), 
        FQty6 Decimal(28,10), 
        FCUUnitQty6 Decimal(28,10), 
        FAmount6 Decimal(28,10), 
        FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10))
 Create Table #Happen(
        FItemID int Null, 
        FStockID int Null, 
        FBatchNo NVARCHAR(200), 
        FQty decimal(28,10) Null, 
        FCUUnitQty decimal(28,10) Null, 
        FQty1 Decimal(28,10), 
        FCUUnitQty1 Decimal(28,10), 
        FAmount1 Decimal(28,10), 
        FQty2 Decimal(28,10), 
        FCUUnitQty2 Decimal(28,10), 
        FAmount2 Decimal(28,10), 
        FQty3 Decimal(28,10), 
        FCUUnitQty3 Decimal(28,10), 
        FAmount3 Decimal(28,10), 
        FQty4 Decimal(28,10), 
        FCUUnitQty4 Decimal(28,10), 
        FAmount4 Decimal(28,10), 
        FQty5 Decimal(28,10), 
        FCUUnitQty5 Decimal(28,10), 
        FAmount5 Decimal(28,10), 
        FQty6 Decimal(28,10), 
        FCUUnitQty6 Decimal(28,10), 
        FAmount6 Decimal(28,10), 
        FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10))
 Create Table #Happen1(
        FItemID int Null, 
        FStockID int Null, 
        FBatchNo NVARCHAR(200), 
        FQty decimal(28,10) Null, 
        FCUUnitQty decimal(28,10) Null, 
FQty1 Decimal(28,10), 
FCUUnitQty1 Decimal(28,10), 
FQty2 Decimal(28,10), 
FCUUnitQty2 Decimal(28,10), 
FQty3 Decimal(28,10), 
FCUUnitQty3 Decimal(28,10), 
FQty4 Decimal(28,10), 
FCUUnitQty4 Decimal(28,10), 
FQty5 Decimal(28,10), 
FCUUnitQty5 Decimal(28,10), 
FQty6 Decimal(28,10), 
FCUUnitQty6 Decimal(28,10), 
 FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10) )
  Insert Into #Happen1
  Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0,
(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,Getdate()),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty1,
((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,Getdate()),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient  As FCUUnitQty1,
(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,Getdate()),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty2,
((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,Getdate()),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient  As FCUUnitQty2,
(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,Getdate()),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty3,
((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,Getdate()),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient  As FCUUnitQty3,
(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,Getdate()),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty4,
((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,Getdate()),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient  As FCUUnitQty4,
(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,Getdate()),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty5,
((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,Getdate()),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient  As FCUUnitQty5,
(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty6,
((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient  As FCUUnitQty6,
1  ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11
 INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13
 Where t13.FItemID = t6.FItemID And t13.FYear = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentYear') And t13.FPeriod = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentPeriod')--And t13.FYear = 2017 And t13.FPeriod = 10
 GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0
 From t_ICItem t1
Join ICStockBill t5 On (t5.FStatus > 0 Or (t5.FUpStockWhenSave > 0 And t5.FCancellation <1 join="" icstockbillentry="" t6="" on="" t5="" finterid="t6.FInterID" left="" join="" t_measureunit="" t7="" on="" t1="" fstoreunitid="t7.FMeasureUnitID" left="" join="" t_stock="" t2="" on="" t2="" fitemid="(case" when="" t5="" ftrantype="24" then="" t6="" fscstockid="" else="" t6="" fdcstockid="" end="" where="" t1="" fitemid="t6.FItemID" and="" t5="" ftrantype="" in="" 1="" 2="" 5="" 10="" 40="" and="" t5="" frob="1)" or="" t5="" ftrantype="" in="" 21="" 24="" 28="" 29="" and="" t5="" frob="-1))" and="" not="" t5="" ftrantype="" in="" 1="" and="" t5="" fpomode="36681" and="" not="" t5="" ftrantype="1" and="" t6="" fsourceinterid=""> 0 and EXISTS(SELECT 1 FROM ICHookRelations t8 where t6.FinterID=t8.fIBInterID and t8.FIBTag=4 )))
 And t2.FTypeID NOT IN (504)--And t1.FNumber='041-017981-00' And t1. FNumber='041-017981-00' --如果要限定物料编码，可以启用此条件
 AND t1.FDeleted=0

  Insert Into #Happen1
  Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0,
(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,Getdate()),101)) Then t6.FBegQty Else 0 End)  As FQty1,
((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,Getdate()),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty1,
(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,Getdate()),101)) Then t6.FBegQty Else 0 End)  As FQty2,
((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,Getdate()),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty2,
(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,Getdate()),101)) Then t6.FBegQty Else 0 End)  As FQty3,
((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,Getdate()),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty3,
(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,Getdate()),101)) Then t6.FBegQty Else 0 End)  As FQty4,
((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,Getdate()),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty4,
(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,Getdate()),101)) Then t6.FBegQty Else 0 End)  As FQty5,
((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,Getdate()),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty5,
(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) Then t6.FBegQty Else 0 End)  As FQty6,
((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,Getdate()),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty6,
1   ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11
 INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13
 Where t13.FItemID = t6.FItemID And t13.FYear = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentYear') And t13.FPeriod = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentPeriod')--And t13.FYear = 2017 And t13.FPeriod = 10
 GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0

From t_ICItem t1
Join ICInvInitial t6 On t1.FItemID = t6.FItemID
Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID
Left Join t_Stock t2 On t2.FItemID = t6.FStockID
 Where 1=1  And t2.FTypeID NOT IN (504)--And t1.FNumber='041-017981-00' And t1. FNumber='041-017981-00' --如果要限定物料编码，可以启用此条件
 AND t1.FDeleted=0

 Insert Into #Happen1 
Select t1.FItemID,t2.FItemID As FStockID,t3.FBatchNo,(t3.FQTY) As FQTY,CAST(t3.FQTY AS DECIMAL(28,10))/t7.FCoefficient As FCUUnitQty,0,0,0,0,0,0,0,0,0,0,0,0,1   ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11
 INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13
 Where t13.FItemID = t3.FItemID And t13.FYear = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentYear') And t13.FPeriod = (select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentPeriod')--And t13.FYear = 2017 And t13.FPeriod = 10
 GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0

From t_ICItem t1
 Join ICINVENTORY t3 On t1.FItemID = t3.FItemID 
Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID
Left Join t_Stock t2 On t2.FItemID = t3.FStockID
Where 1=1
 And t2.FTypeID NOT IN (504)--And t1.FNumber='041-017981-00' And t1. FNumber='041-017981-00' --如果要限定物料编码，可以启用此条件
 AND t1.FDeleted=0

 Insert Into #HAPPEN2
Select t1.FITEMID,0,t1.FBatchNo,Sum(FQTY)As FQTY,Sum(FCUUnitQTY)As FCUUnitQTY,
Sum(fqty1) As FQty1 ,Sum(fCUUnitqty1) As FCUUnitQty1 ,0,Sum(fqty2) As FQty2 ,Sum(fCUUnitqty2) As FCUUnitQty2 ,0,Sum(fqty3) As FQty3 ,Sum(fCUUnitqty3) As FCUUnitQty3 ,0,Sum(fqty4) As FQty4 ,Sum(fCUUnitqty4) As FCUUnitQty4 ,0,Sum(fqty5) As FQty5 ,Sum(fCUUnitqty5) As FCUUnitQty5 ,0,Sum(fqty6) As FQty6 ,Sum(fCUUnitqty6) As FCUUnitQty6 ,0,1,Min(FPrice),case Sum(FCUUnitQTY) when 0 then 0 else (Min(FPrice)*Sum(FQTY))/Sum(FCUUnitQTY) end,Min(FPrice)*Sum(FQTY) 
From #HAPPEN1 t1 INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID  
GROUP By t1.FITEMID,t1.FBatchNo  Update #Happen2 Set FQty1= FQty,FQty2=0
,FQty3=0
,FQty4=0
,FQty5=0
,FQty6=0
 Where  FQty-FQty1<0 
 Update #Happen2 Set FQty2= FQty-FQty1,FQty3=0
,FQty4=0
,FQty5=0
,FQty6=0
 Where  FQty-FQty1-FQty2<0 
 Update #Happen2 Set FQty3= FQty-FQty1-FQty2,FQty4=0
,FQty5=0
,FQty6=0
 Where  FQty-FQty1-FQty2-FQty3<0 
 Update #Happen2 Set FQty4= FQty-FQty1-FQty2-FQty3,FQty5=0
,FQty6=0
 Where  FQty-FQty1-FQty2-FQty3-FQty4<0 
 Update #Happen2 Set FQty5= FQty-FQty1-FQty2-FQty3-FQty4,FQty6=0
 Where  FQty-FQty1-FQty2-FQty3-FQty4-FQty5<0 
 Update #Happen2 Set FQty6= FQty-FQty1-FQty2-FQty3-FQty4-FQty5
Update #Happen2 Set FAmount1=FPrice*FQty1
Update #Happen2 Set FAmount2=FPrice*FQty2
Update #Happen2 Set FAmount3=FPrice*FQty3
Update #Happen2 Set FAmount4=FPrice*FQty4
Update #Happen2 Set FAmount5=FPrice*FQty5
Update #Happen2 Set FAmount6=FPrice*FQty6

 Update #Happen2 Set FCUUnitQty1= FCUUnitQty,FCUUnitQty2=0
,FCUUnitQty3=0
,FCUUnitQty4=0
,FCUUnitQty5=0
,FCUUnitQty6=0
 Where  FCUUnitQty-FCUUnitQty1<0 
 Update #Happen2 Set FCUUnitQty2= FCUUnitQty-FCUUnitQty1
,FCUUnitQty3=0
,FCUUnitQty4=0
,FCUUnitQty5=0
,FCUUnitQty6=0
 Where  FCUUnitQty-FCUUnitQty1-FCUUnitQty2<0 
 Update #Happen2 Set FCUUnitQty3= FCUUnitQty-FCUUnitQty1
-FCUUnitQty2
,FCUUnitQty4=0
,FCUUnitQty5=0
,FCUUnitQty6=0
 Where  FCUUnitQty-FCUUnitQty1-FCUUnitQty2-FCUUnitQty3<0 
 Update #Happen2 Set FCUUnitQty4= FCUUnitQty-FCUUnitQty1
-FCUUnitQty2
-FCUUnitQty3
,FCUUnitQty5=0
,FCUUnitQty6=0
 Where  FCUUnitQty-FCUUnitQty1-FCUUnitQty2-FCUUnitQty3-FCUUnitQty4<0 
 Update #Happen2 Set FCUUnitQty5= FCUUnitQty-FCUUnitQty1
-FCUUnitQty2
-FCUUnitQty3
-FCUUnitQty4
,FCUUnitQty6=0
 Where  FCUUnitQty-FCUUnitQty1-FCUUnitQty2-FCUUnitQty3-FCUUnitQty4-FCUUnitQty5<0 update="" happen2="" set="" fcuunitqty6="FCUUnitQty-FCUUnitQty1" -fcuunitqty2="" -fcuunitqty3="" -fcuunitqty4="" -fcuunitqty5="" insert="" into="" happen="" select="" t1="" fitemid="" 0="" t1="" fbatchno="" sum="" fqty="" as="" fqty="" sum="" fcuunitqty="" as="" fcuunitqty="" sum="" fqty1="" as="" fqty1="" sum="" fcuunitqty1="" as="" fcuunitqty1="" sum="" famount1="" as="" famount1="" sum="" fqty2="" as="" fqty2="" sum="" fcuunitqty2="" as="" fcuunitqty2="" sum="" famount2="" as="" famount2="" sum="" fqty3="" as="" fqty3="" sum="" fcuunitqty3="" as="" fcuunitqty3="" sum="" famount3="" as="" famount3="" sum="" fqty4="" as="" fqty4="" sum="" fcuunitqty4="" as="" fcuunitqty4="" sum="" famount4="" as="" famount4="" sum="" fqty5="" as="" fqty5="" sum="" fcuunitqty5="" as="" fcuunitqty5="" sum="" famount5="" as="" famount5="" sum="" fqty6="" as="" fqty6="" sum="" fcuunitqty6="" as="" fcuunitqty6="" sum="" famount6="" as="" famount6="" 1="" min="" fprice="" case="" sum="" fcuunitqty="" when="" 0="" then="" 0="" else="" min="" fprice="" sum="" fqty="" sum="" fcuunitqty="" end="" min="" fprice="" sum="" fqty="" from="" happen2="" t1="" inner="" join="" t_icitem="" t2="" on="" t1="" fitemid="t2.FItemID" group="" by="" t1="" fitemid="" t1="" fbatchno="" having="" sum="" fqty="">=0) 
CREATE TABLE #DATA(
     FNumber  Varchar(355) null,
     FShortNumber  Varchar(355) null,
     FName  Varchar(355) null,
     FModel  Varchar(355) null,
     FUnitName  Varchar(355) null,
     FCUUnitName  Varchar(355) null,
     FQtyDecimal smallint null, 
     FPriceDecimal smallint null, 
     FQty Decimal(28,10) Null, 
     FCUUnitQty Decimal(28,10) Null, 
     FPrice Decimal(28,10) NULL, 
     FCUPrice Decimal(28,10) Null, 
     FAmount Decimal(28,10) Null, 
 FQty1 Decimal(28,10),
 FCUUnitQty1 Decimal(28,10),
 FAmount1 Decimal(28,10),
 FQty2 Decimal(28,10),
 FCUUnitQty2 Decimal(28,10),
 FAmount2 Decimal(28,10),
 FQty3 Decimal(28,10),
 FCUUnitQty3 Decimal(28,10),
 FAmount3 Decimal(28,10),
 FQty4 Decimal(28,10),
 FCUUnitQty4 Decimal(28,10),
 FAmount4 Decimal(28,10),
 FQty5 Decimal(28,10),
 FCUUnitQty5 Decimal(28,10),
 FAmount5 Decimal(28,10),
 FQty6 Decimal(28,10),
 FCUUnitQty6 Decimal(28,10),
 FAmount6 Decimal(28,10),
     FSumSort smallint not null Default(0),
Flevel0 Decimal(10,3), 
Flevel1 Decimal(10,3), 
Flevel2 Decimal(10,3), 
Flevel3 Decimal(10,3), 
Flevel4 Decimal(10,3), 
Flevel5 Decimal(10,3), 
Flevel6 Decimal(10,3), 

     FID int IDENTITY)

  INSERT INTO #DATA (
FNumber,FShortNumber,FName,FModel,FUnitName,FCUUnitName,
 FQtyDecimal,FPriceDecimal,FQty,FCUUnitQty,FPrice,FCUPrice,FAmount,FQty1,
FCUUnitQty1,
FAmount1,
FQty2,
FCUUnitQty2,
FAmount2,
FQty3,
FCUUnitQty3,
FAmount3,
FQty4,
FCUUnitQty4,
FAmount4,
FQty5,
FCUUnitQty5,
FAmount5,
FQty6,
FCUUnitQty6,
FAmount6,
FSumSort)
 SELECT CASE   WHEN GROUPING(t1.FNumber)=1 THEN '合计' 
 ELSE t1.FNumber END, 
'','','','','',MAX(t1.FQtyDecimal),MAX(t1.FPriceDecimal),Sum(FQty),Sum(FCUUnitQty), case Sum(FQty) when 0 then 0 else Sum(FAmount)/Sum(FQty) end,(CASE Sum(FCUUnitQty) WHEN 0 THEN 0 ELSE Sum(FAmount)/Sum(FCUUnitQty) END), sum(FAmount), SUM(FQty1),
 SUM(FCUUnitQty1),
 SUM(FAmount1),
 SUM(FQty2),
 SUM(FCUUnitQty2),
 SUM(FAmount2),
 SUM(FQty3),
 SUM(FCUUnitQty3),
 SUM(FAmount3),
 SUM(FQty4),
 SUM(FCUUnitQty4),
 SUM(FAmount4),
 SUM(FQty5),
 SUM(FCUUnitQty5),
 SUM(FAmount5),
 SUM(FQty6),
 SUM(FCUUnitQty6),
 SUM(FAmount6),

 CASE  WHEN GROUPING(t1.FNumber)=1 THEN 101 
 ELSE 0 END  FROM #Happen v2
 Inner Join t_ICItem t1 On v2.FItemID=t1.FItemID
 Left Join t_Stock t2 On v2.FStockID=t2.FItemID
 Where 1=1




Group by t1.FNumber WITH ROLLUP Having Sum(FQty)>0 

 Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FUnitName=t3.FName,t1.FCUUnitName=t4.FName ,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal  From #DATA t1,t_ICItem t2,t_MeasureUnit t3,t_MeasureUnit t4  Where t1.FNumber=t2.FNumber  And t2.FUnitGroupID=t3.FUnitGroupID  And t2.FStoreUnitID=t4.FMeasureUnitID  And t3.FStandard=1

SELECT FNumber 物料编码,FName 物料名称,FUnitName 基本计量单位,FCUUnitName 库存单位,FQty 库存数量,FPrice 单价,FAmount 金额,
Fqty1 '0-29天数量',fAmount1 '0-29天金额',Fqty2 '30-59天数量',fAmount2 '30-59天金额',Fqty3 '60-89天数量',fAmount3 '60-89天金额',Fqty4 '90-179天数量',fAmount4 '90-179天金额',Fqty5 '180-359天数量',fAmount5 '180-359天金额',Fqty6 '360天以上数量',fAmount6 '360天以上金额' FROM #DATA  
  

DROP TABLE #DATA  

 Drop Table #Happen
 Drop Table #Happen1
 Drop Table #Happen2

数量取即时库存。单价依据为期初余额加权平均单价。

hzfw2008
关注
  • 0
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
金蝶云K3 Cloud V2.0 SP1-发版说明.pdf
03-06
。。。
金蝶k3库存账龄分析报表（带参数）
mamengna的博客
01-14 2309
单价取最近的结账账期加权平均单价；数量根据传入参数确定。 传入参数： 年份：如2017 账期：如11 SQL直接使用时执行存储过程： execute [huwei_sp_stock_Age] 2017,07 K3调用查询分析语句： set nocount ON exec gd_sp_stock_Age @ParaYear@ ,@parePeriod@ 存储过程代码如下： Set NoCount O...
金蝶K3CLOUD 常用SQL
03-10
金蝶K3CLOUD 常用SQL 。金蝶维护人员的好助手
金蝶K3数据库表结构及注释
06-10
金蝶K3数据库表结构及注释
星空系统常用数据表
weixin_44252983的博客
02-08 220
星空系统常用数据表
金蝶K3 SQL报表系列-库存账龄分析
hzfw2008的博客
08-13 3544
金蝶K3 SQL报表系列-库存账龄分析
K3账龄分析表SQL
钟爱技术
06-01 3896
Set NoCount On  SET ANSI_WARNINGS OFF  Create Table #Happen2(         FItemID int Null,         FStockID int Null,         FBatchNo NVARCHAR(200),         FQty decimal(28,10) Null,       
金蝶 K3 库存账龄分析表 数据不一致
大猴子
07-17 3749
用户反馈某个物料在库存账龄分析表内的统计后存在超过1080天的数量，但实际该料只是在一年前导入，故不可能存在超过1080天的数据。 原因分析：用户没勾选包含调拨单的数据，报表统计时将调拨单排除在外。造成即时库存数据大于流水单据（不含调拨单）数据，多出来的部分系统将其归类到最后一项天数。 Set NoCount On SET ANSI_WARNINGS OFF Create Table ...
金蝶云星空库存账龄分析表(SQL)
01-16
金蝶云星空库存账龄分析表(SQL)
金蝶K3WISE常用数据表(整理)
07-06
金蝶K3WISE常用数据表(整理)
金蝶K3-Cloud云星空数据库表字典
06-18
金蝶K3-Cloud云星空开发，二次开发
金蝶K3自动编制现金流量表(外).pdf
02-11
金蝶K3自动编制现金流量表(外).pdf
金蝶K3数据库表结构-51个excel
12-03
金蝶K3 数据库表结构, 一共53个excel文件.包含了大部分业务需要处理的表结构, 表结构包含详细注释
金蝶K3wise15.1发版说明
05-16
金蝶K3wise15.1发版说明（简体，繁体，英文）
金蝶云K3Cloud接口
07-27
提供金蝶K3Cloud接口Demo，有C#也有java版本的代码，接口演示生产订单的同步及入库的操作，方便大家的使用
金蝶k3 cloud表结构
11-10
下载后直接运行K3CloudDataDictionary.exe进行查看，方便二次开发人员
金蝶k3 wise表结构
11-10
下载后，直接运行 KDTabs.exe就可以查看金蝶k3 wise表结构,方便二次开发人员
金蝶k3数据库表结构(全部)
最新发布
07-13
金蝶 K3 数据库表结构涵盖了多个模块和功能，下面是对其中一些主要模块的表结构的简要介绍： 1. 基础资料模块：包括了客户、供应商、物料等基础信息的表结构，如客户档案表、供应商档案表和物料档案表等。 2. 采购模块：包括了采购订单、采购入库等相关功能的表结构，如采购订单表、采购入库单表等。 3. 销售模块：包括了销售订单、销售出库等相关功能的表结构，如销售订单表、销售出库单表等。 4. 库存模块：包括了库存查询、库存调拨等相关功能的表结构，如库存档案表、库存调拨单表等。 5. 生产模块：包括了生产订单、生产领料等相关功能的表结构，如生产订单表、生产领料单表等。 6. 财务模块：包括了核算科目、凭证等相关功能的表结构，如核算科目表、凭证表等。 不同模块的表结构相互关联，通过数据库中的表之间的关系，可以实现不同功能之间的数据交互和数据共享。 需要注意的是，金蝶 K3 数据库的表结构可能会根据不同版本和定制需求而有所差异，以上仅是对一些主要模块的表结构进行概括性的介绍。如需详细了解金蝶 K3 数据库的全部表结构，建议参考官方提供的相关文档或联系金蝶 K3 的技术支持团队获取详细信息。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值