金蝶k3库存账龄分析报表（带参数）

单价取最近的结账账期加权平均单价；数量根据传入参数确定。

传入参数：

年份：如2017

账期：如11

SQL直接使用时执行存储过程：

execute [huwei_sp_stock_Age] 2017,07

K3调用查询分析语句：

set nocount ONexec gd_sp_stock_Age @ParaYear@ ,@parePeriod@

存储过程代码如下：

Set NoCount On SET ANSI_WARNINGS OFF USE [AIS20140104204141]GO/****** Object:  StoredProcedure [dbo].[huwei_sp_stock_Age]    Script Date: 12/15/2017 08:29:37 ******/ALTER procedure [dbo].[gd_sp_stock_Age]@paraYear@  int,@parePeriod@ intasdeclare @year intdeclare @period intdeclare @Fdate datetimedeclare @Fstartdate datetime--declare @paraYear@ int--declare @parePeriod@ int--set @paraYear@=(select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentYear')--set @parePeriod@=(select FValue from t_SystemProfile where FCategory='IC' and FKey='CurrentPeriod')if @parePeriod@=12beginset @year=@paraYear@+1set @period=1set @Fdate=convert(datetime,convert(nvarchar(20),@year)+'-01-01')-1set @Fstartdate=convert(datetime,convert(nvarchar(20),@paraYear@)+'-12-01')endelsebeginset @year=@paraYear@set @period=@parePeriod@+1set @Fdate=convert(datetime,convert(nvarchar(20),@year)+'-'+convert(nvarchar(20),@period)+'-01')-1set @Fstartdate=convert(datetime,convert(nvarchar(20),@Year)+'-'+convert(nvarchar(20),@parePeriod@)+'-01')end  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,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,@Fdate),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty1,((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,@Fdate),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,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,@Fdate),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty2,((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,@Fdate),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,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,@Fdate),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty3,((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,@Fdate),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,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,@Fdate),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty4,((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,@Fdate),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,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,@Fdate),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty5,((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,@Fdate),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,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty6,((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),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') GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0 From t_ICItem t1Join ICStockBill t5 On (t5.FStatus > 0 Or (t5.FUpStockWhenSave > 0 And t5.FCancellation <1 ))Join ICStockBillEntry t6 On t5.FInterID=t6.FInterIDLeft Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitIDLeft 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)  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,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,@Fdate),101)) Then t6.FBegQty Else 0 End)  As FQty1,((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-29,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,@Fdate),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty1,(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,@Fdate),101)) Then t6.FBegQty Else 0 End)  As FQty2,((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-59,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-30,@Fdate),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty2,(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,@Fdate),101)) Then t6.FBegQty Else 0 End)  As FQty3,((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-89,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-60,@Fdate),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty3,(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,@Fdate),101)) Then t6.FBegQty Else 0 End)  As FQty4,((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-179,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-90,@Fdate),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty4,(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,@Fdate),101)) Then t6.FBegQty Else 0 End)  As FQty5,((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-180,@Fdate),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty5,(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),101)) Then t6.FBegQty Else 0 End)  As FQty6,((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,@Fdate),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') GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0From t_ICItem t1Join ICInvInitial t6 On t1.FItemID = t6.FItemIDLeft Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitIDLeft Join t_Stock t2 On t2.FItemID = t6.FStockID Where 1=1  And t2.FTypeID NOT IN (504)CREATE TABLE #InventoryHanppen(FItemID    INT           NOT NULL, FStockID   INT           NULL, FBatchNo   Varchar(255)  NULL, FQty     DECIMAL(28,10)  NOT NULL, FAmount  DECIMAL(28,10)  NOT NULL )INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)SELECT u1.FItemID,u1.FStockID,u1.FBatchNo,u1.FBegQty,u1.FBegBal FROM t_ICItem t1INNER JOIN ICInvBal u1 ON t1.FItemID=u1.FItemID LEFT  JOIN t_Stock  t2 ON t2.FItemID = u1.FStockIDWHERE u1.FYear=@paraYear@ AND u1.FPeriod=@parePeriod@ And t2.FTypeID NOT IN (504)INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)SELECT u1.FItemID,t2.FItemID,u1.FBatchNo, CASE WHEN v1.FTranType IN (1,2,5,10,40,41,101,102) OR (v1.FTranType=100 AND v1.FBillTypeID=12542) THEN u1.FQty ELSE -1 * u1.FQty END,CASE WHEN v1.FTranType IN (1,2,5,10,40,101,102) OR (v1.FTranType=100 AND v1.FBillTypeID=12542) THEN u1.FAmount WHEN v1.FTranType=41 THEN u1.FAmtRef ELSE -1 * u1.FAmount ENDFROM t_ICItem t1INNER JOIN ICStockBillEntry u1 ON t1.FItemID=u1.FItemID INNER JOIN ICStockBill v1 ON u1.FInterID=v1.FInterID LEFT  JOIN t_Stock  t2 ON ((v1.FTrantype=24 AND u1.FSCStockID=t2.FItemID) OR (v1.FTranType IN (1,2,5,10,21,41,28,29,43,40,100,101,102) AND u1.FDCStockID=t2.FItemID ))WHERE v1.FDate>=@Fstartdate AND v1.FDate<=@Fdate And t2.FTypeID NOT IN (504)AND v1.Ftrantype In (1,2,5,10,21,24,41,28,29,43,40,100,101,102) AND (NOT (v1.FTrantype In (1) AND isnull(v1.FPOMode,0) = 36681 ))AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 ))INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)SELECT u1.FItemID,t2.FItemID,u1.FBatchNo,-1 * u1.FQty ,-1 * u1.FAmountFROM t_ICItem t1INNER JOIN ICStockBillEntry u1 ON t1.FItemID=u1.FItemID INNER JOIN ICStockBill v1 ON u1.FInterID=v1.FInterID LEFT  JOIN t_Stock  t2 ON  u1.FSCStockID=t2.FItemIDWHERE v1.FDate>=@Fstartdate AND v1.FDate<=@Fdate And t2.FTypeID NOT IN (504)AND v1.Ftrantype=41AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 ))SELECT FItemID,FStockID,FBatchNo,SUM(FQty) AS FQty,SUM(FAmount) AS FAmount INTO #INVENTORYFROM #InventoryHanppenGROUP BY FItemID,FStockID,FBatchNoDROP TABLE #InventoryHanppenDELETE FROM #INVENTORY WHERE FQty<=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') GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0From t_ICItem t1 Join #INVENTORY t3 On t1.FItemID = t3.FItemID Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitIDLeft Join t_Stock t2 On t2.FItemID = t3.FStockIDWhere 1=1 And t2.FTypeID NOT IN (504) Insert Into #HAPPEN2Select 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-FQty5Update #Happen2 Set FAmount1=FPrice*FQty1Update #Happen2 Set FAmount2=FPrice*FQty2Update #Happen2 Set FAmount3=FPrice*FQty3Update #Happen2 Set FAmount4=FPrice*FQty4Update #Happen2 Set FAmount5=FPrice*FQty5Update #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 #HAPPENSelect 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=1Group 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 * FROM #DATA  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 DROP TABLE #INVENTORY GO

效果图：