用户反馈某个物料在库存账龄分析表内的统计后存在超过1080天的数量，但实际该料只是在一年前导入，故不可能存在超过1080天的数据。
原因分析：用户没勾选包含调拨单的数据，报表统计时将调拨单排除在外。造成即时库存数据大于流水单据（不含调拨单）数据，多出来的部分系统将其归类到最后一项天数。
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), 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), 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), FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10) )--ICBal存货余额，先分仓库统计流水Insert Into #Happen1Select t5.FTranType,t5.FBillNo,t5.FDate,t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0,(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,'-07-16'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'-07-16'),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty1,((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,'-07-16'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'-07-16'),101)) Then t5.FRob*t6.FQty Else 0 End))/t7.FCoefficient As FCUUnitQty1,(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1079,'-07-16'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,'-07-16'),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty2,((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1079,'-07-16'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,'-07-16'),101)) Then t5.FRob*t6.FQty Else 0 End))/t7.FCoefficient As FCUUnitQty2,(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'-07-16'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'-07-16'),101)) Then t5.FRob*t6.FQty Else 0 End) As FQty3,((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'-07-16'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'-07-16'),101)) Then t5.FRob*t6.FQty Else 0 End))/t7.FCoefficient As FCUUnitQty3,1 ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13Where t13.FItemID = t6.FItemID And t13.FYear = And t13.FPeriod = 7GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0From 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 t1.FNumber>='05.22.01.00049' And t1. FNumber<='05.22.01.00049'--期初余额ICInvInitial，再统计期初余额，不懂为什么这一步Insert Into #Happen1Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0,(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,'-07-16'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'-07-16'),101)) Then t6.FBegQty Else 0 End) As FQty1,((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-359,'-07-16'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'-07-16'),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty1,(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1079,'-07-16'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,'-07-16'),101)) Then t6.FBegQty Else 0 End) As FQty2,((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1079,'-07-16'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-360,'-07-16'),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty2,(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'-07-16'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'-07-16'),101)) Then t6.FBegQty Else 0 End) As FQty3,((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'-07-16'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-1080,'-07-16'),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient As FCUUnitQty3,1 ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13Where t13.FItemID = t6.FItemID And t13.FYear = And t13.FPeriod = 7GROUP 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.FStockIDWhere 1=1 And t1.FNumber>='05.22.01.00049' And t1. FNumber<='05.22.01.00049'--统计即时库存的数据，最终以此为准CREATE TABLE #InventoryHanppen(FItemID INT NOT NULL,FStockID INT NULL,FBatchNo Varchar(255) NULL,FQtyDECIMAL(28,10) NOT NULL,FAmount DECIMAL(28,10) NOT NULL )SELECT * FROM #InventoryHanppen--ICInvBal存货余额INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)SELECT u1.FItemID,u1.FStockID,u1.FBatchNo,u1.FBegQty,u1.FBegBal--, t2.FNameFROM t_ICItem t1INNER JOIN ICInvBal u1 ON t1.FItemID=u1.FItemID LEFT JOIN t_Stock t2 ON t2.FItemID = u1.FStockIDWHERE u1.FYear= AND u1.FPeriod=7 And t1.FNumber>='05.22.01.00049' And t1. FNumber<='05.22.01.00049'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 END--,t2.FNameFROM 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>='/07/01' AND v1.FDate<='/07/16' And t1.FNumber>='05.22.01.00049' And t1. FNumber<='05.22.01.00049'AND v1.Ftrantype In (1,2,5,10,21,24,41,28,29,43,40,100,101,102)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>='/07/01' AND v1.FDate<='/07/16' And t1.FNumber>='05.22.01.00049' And t1. FNumber<='05.22.01.00049'AND v1.Ftrantype=41AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 ))--TRUNCATE TABLE #InventoryHanppenSELECT a.FItemID,FStockID,FBatchNo,SUM(FQty) AS FQty,SUM(FAmount) AS FAmount,dbo.t_Stock.FName FROM #InventoryHanppen a LEFT JOIN t_Stock ON t_Stock.FItemID = a.FStockID GROUP BY a.FItemID,FStockID,FBatchNo,FNameSELECT FItemID,FStockID,FBatchNo,SUM(FQty) AS FQty,SUM(FAmount) AS FAmount INTO #INVENTORYFROM #InventoryHanppenGROUP BY FItemID,FStockID,FBatchNoSELECT * FROM #INVENTORYDROP TABLE #InventoryHanppen--DROP TABLE #INVENTORYInsert Into #Happen1 Select t1.FItemID,t2.FItemID As FStockID,t3.FBatchNo,(t3.FQTY) As FQTY,(t3.FQTY)/t7.FCoefficient As FCUUnitQty,0,0,0,0,0,0,1 ,ISNULL(((SELECT t12.FBegBal/t12.FBegQty FROM t_ICItem t11INNER JOIN (SELECT FItemID,SUM(FBegBal) AS FBegBal,SUM(FBegQty) AS FBegQty FROM ICBal t13Where t13.FItemID = t3.FItemID And t13.FYear = And t13.FPeriod = 7GROUP BY t13.FItemID) t12 ON t11.FItemID=t12.FItemID AND t12.FBegBal>0 AND t12.FBegQty>0) ),0),0,0From t_ICItem t1Join #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=1And t1.FNumber>='05.22.01.00049' And t1. FNumber<='05.22.01.00049'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,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.FStockID,t1.FBatchNo --如果统计的360天内的账龄数量大于库存，那么可以肯定多出来那部分已经出库，下方同理Delete From #Happen2 Where FQty<=0 Update #Happen2 Set FQty1= FQty,FQty2=0,FQty3=0Where FQty-FQty1<0 Update #Happen2 Set FQty2= FQty-FQty1,FQty3=0Where FQty-FQty1-FQty2<0 Update #Happen2 Set FQty3= FQty-FQty1-FQty2 Update #Happen2 Set FAmount1=FQty1* FPrice,FAmount2=FQty2* FPrice,FAmount3=FQty3* FPriceUpdate #Happen2 Set FCUUnitQty1= FCUUnitQty,FCUUnitQty2=0,FCUUnitQty3=0Where FCUUnitQty-FCUUnitQty1<0 Update #Happen2 Set FCUUnitQty2= FCUUnitQty-FCUUnitQty1,FCUUnitQty3=0Where FCUUnitQty-FCUUnitQty1-FCUUnitQty2<0 Update #Happen2 Set FCUUnitQty3= FCUUnitQty-FCUUnitQty1-FCUUnitQty2SELECT * FROM #HappenInsert 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,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) SET NOCOUNT ONCREATE TABLE #ItemLevel( FNumber1 Varchar(355),FName1 Varchar(355),FNumber2 Varchar(355),FName2 Varchar(355),FNumber3 Varchar(355),FName3 Varchar(355),FItemID int,FNumber Varchar(355))INSERT INTO #ItemLevel SELECT CASE WHEN CHARINDEX('.',FFullNumber)-1= -1 or FLevel<2 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber)-1) END, '',CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1) END, '',CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1= -1 or FLevel<4 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1) END, '',FItemID,FNumber FROM t_ItemWHERE FItemClassID=4AND FDetail=1 AND FNumber>='05.22.01.00049' AND FNumber<='05.22.01.00049' AND FItemID In (Select Distinct FItemID from #Happen )UPDATE t0 SET t0.FName1='[' + t1.FNumber + ']'+ t1.FName,t0.FName2='[' + t2.FNumber + ']'+ t2.FName,t0.FName3='[' + t3.FNumber + ']'+ t3.FNameFROM #ItemLevel t0 left join t_Item t1 On t0.FNumber1=t1.FNumber AND t1.FItemClassID=4 AND t1.FDetail=0 left join t_Item t2 On t0.FNumber2=t2.FNumber AND t2.FItemClassID=4 AND t2.FDetail=0 left join t_Item t3 On t0.FNumber3=t3.FNumber AND t3.FItemClassID=4 AND t3.FDetail=0 --最后汇总CREATE TABLE #DATA(FName1 Varchar(355) Null,FName2 Varchar(355) Null,FName3 Varchar(355) Null,FNumber Varchar(355) null,FShortNumber Varchar(355) null,FName Varchar(355) null,FModel Varchar(355) null,FUnitName Varchar(355) null,FCUUnitName Varchar(355) null,FBatchNo NVarchar(200) 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),FSumSort smallint not null Default(0),FID int IDENTITY)INSERT INTO #DATA SELECT tt1.FName1,tt1.FName2,tt1.FName3,t1.FNumber, '','','','','','',6,4,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),CASE WHEN Grouping(tt1.FName1)=1 THEN 106WHEN Grouping(tt1.FName2)=1 THEN 107WHEN Grouping(tt1.FName3)=1 THEN 108WHEN Grouping(t1.FNumber)=1 THEN 109 ELSE 0 END FROM #Happen v2Inner Join t_ICItem t1 On v2.FItemID=t1.FItemIDLeft Join t_Stock t2 On v2.FStockID=t2.FItemIDInner Join #ItemLevel tt1On t1.FItemID=tt1.FItemID Where 1=1Group By tt1.FName1,tt1.FName2,tt1.FName3,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=1Update #Data Set FName1=isnull(FName1,'')+'(小计)' WHERE FSumSort=107Update #Data Set FName2=isnull(FName2,'')+'(小计)' WHERE FSumSort=108Update #Data Set FName3=isnull(FName3,'')+'(小计)' WHERE FSumSort=109Update #Data Set FName1='合计' WHERE FSumSort=106Update #Data Set FSumSort=101 WHERE FSumSort=106DELETE #DATA WHERE FSumSort = 1000 UPDATE #DATA SET FBatchNo = '' WHERE FSumSort <> 0 DELETE #DATA WHERE FSumSort = 0 SELECT * FROM #DATA WHERE FSumSort>100Order by FID DROP TABLE #DATA DROP TABLE #ItemLevelDrop Table #HappenDrop Table #Happen1Drop Table #Happen2DROP TABLE #INVENTORY
如果觉得《金蝶 K3 库存账龄分析表 数据不一致》对你有帮助，请点赞、收藏，并留下你的观点哦！
