单价取最近的结账账期加权平均单价;数量根据传入参数确定。
传入参数:
年份:如2017
账期:如11
SQL直接使用时执行存储过程:
execute [huwei_sp_stock_Age] 2017,07
K3调用查询分析语句:
set nocount ON
exec 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@ int
as
declare @year int
declare @period int
declare @Fdate datetime
declare @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@=12
begin
set @year=@paraYear@+1
set @period=1
set @Fdate=convert(datetime,convert(nvarchar(20),@year)+'-01-01')-1
set @Fstartdate=convert(datetime,convert(nvarchar(20),@paraYear@)+'-12-01')
end
else
begin
set @year=@paraYear@
set @period=@parePeriod@+1
set @Fdate=convert(datetime,convert(nvarchar(20),@year)+'-'+convert(nvarchar(20),@period)+'-01')-1
set @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 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)
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,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)
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 t1
INNER JOIN ICInvBal u1 ON t1.FItemID=u1.FItemID
LEFT JOIN t_Stock t2 ON t2.FItemID = u1.FStockID
WHERE 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 END
FROM t_ICItem t1
INNER 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.FAmount
FROM t_ICItem t1
INNER 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.FItemID
WHERE v1.FDate>=@Fstartdate AND
v1.FDate<=@Fdate And t2.FTypeID NOT IN (504)
AND v1.Ftrantype=41
AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 ))
SELECT FItemID,FStockID,FBatchNo,SUM(FQty) AS FQty,SUM(FAmount) AS FAmount INTO #INVENTORY
FROM #InventoryHanppen
GROUP BY FItemID,FStockID,FBatchNo
DROP TABLE #InventoryHanppen
DELETE 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,0
From t_ICItem t1
Join #INVENTORY 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)
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 * 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
效果图: