关于成本的SQL算法(希望高手指点)

Question

目前是以单据为查询依据,查某期某单据的成本,但是如果要翻转到某一期的所有单据,偶这种算法就有问题了,效率跟不上了

-->成本函数(查询以单据编号)
CREATE FUNCTION f_Cost(@FBillNo VARCHAR(40),@dt VARCHAR(10))
RETURNS @t2 TABLE(FTranType VARCHAR(40),FBillNo VARCHAR(40),FItemID VARCHAR(40),FAuxStockQty DECIMAL(20,2),层次 VARCHAR(40))
AS
BEGIN
DECLARE @t INT
SET @t=1
/*
 将本期内生产任务单中符合所查询的“生产任务单编号”的各“材料品号”的数量聚合汇总插入到表@t2，其中生产任务单的状态为结案
*/
INSERT @t2
SELECT [单据类型]=CASE WHEN a.FTranType=85 THEN '厂内生产任务单' WHEN a.FTranType=571 THEN '委外生产任务单' END,
[生产任务单号]=a.FBillNo,[材料编码]=b.FItemID,[投料数量]=ISNULL(b.FAuxStockQty,0),[层次]=@t
FROM ICMO a 
LEFT OUTER JOIN PPBomEntry b ON a.FInterID=b.FICMOInterID
WHERE a.FBillNo=@FBillNo AND a.FStatus='3' 
AND a.FCheckDate>=CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),@dt,120)+'-28' ELSE CONVERT(VARCHAR(7),DATEADD(MONTH,-1,@dt),120)+'-28' END
AND a.FCheckDate<CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),DATEADD(MONTH,1,@dt),120)+'-28' ELSE CONVERT(VARCHAR(7),@dt,120)+'-28' END
/*
 将表@t2中的“材料品号”并且在本期内有生产的“材料品号”提取出来放入@t3中，进行循环
*/
DECLARE @t3 TABLE(FTranType VARCHAR(40),FBillNo VARCHAR(40),FItemID VARCHAR(40),FAuxStockQty DECIMAL(20,2),层次 VARCHAR(40))
INSERT @t3
SELECT [单据类型]=a.FTranType,[生产任务单号]=a.FBillNo,[材料编码]=a.FItemID,[投料数量]=ISNULL(a.FAuxStockQty,0),[层次]=a.层次
FROM @t2 a 
WHERE a.FItemID IN (SELECT b.FItemID FROM PPBom b LEFT OUTER JOIN PPBomEntry c ON b.FInterID=c.FInterID 
WHERE b.FDate>=CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),@dt,120)+'-28' ELSE CONVERT(VARCHAR(7),DATEADD(MONTH,-1,@dt),120)+'-28' END
    AND b.FDate<CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),DATEADD(MONTH,1,@dt),120)+'-28' ELSE CONVERT(VARCHAR(7),@dt,120)+'-28' END )
/*
  循环提取“材料品号”并且在本期内有生产的“材料品号”提取出来放入@t3和插入@t2表
*/
WHILE @@ROWCOUNT>0
  BEGIN
   SET @t=@t+1
    INSERT @t2
    SELECT a.FTranType,a.FBillNo,c.FItemID,[投料数量]=CASE WHEN SUM(c.FAuxStockQty)=0 THEN SUM(a.FAuxStockQty*c.FAuxStockQty)/COUNT(a.FItemID) ELSE SUM(ISNULL(c.FAuxStockQty,0))*SUM(a.FAuxStockQty)/COUNT(a.FItemID)/SUM(d.FAuxStockQty) END,@t 
    FROM @t3 a 
    LEFT OUTER JOIN PPBOM b ON a.FItemID=b.FItemID
    LEFT OUTER JOIN PPBOMEntry c ON b.FInterID=c.FInterID
    LEFT OUTER JOIN ICMO d ON b.FICMOInterID=d.FInterID
    WHERE 
    b.FDate>=CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),@dt,120)+'-28' ELSE CONVERT(VARCHAR(7),DATEADD(MONTH,-1,@dt),120)+'-28' END
    AND b.FDate<CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),DATEADD(MONTH,1,@dt),120)+'-28' ELSE CONVERT(VARCHAR(7),@dt,120)+'-28' END
    AND d.fStatus='3' AND a.层次=@t-1
    GROUP BY a.FTranType,a.FBillNo,c.FItemID
    
    INSERT @t3
    SELECT a.FTranType,a.FBillNo,a.FItemID,a.FAuxStockQty,a.层次
    FROM @t2 a
    WHERE a.层次=@t AND a.FItemID IN 
(SELECT b.FItemID FROM PPBom b LEFT OUTER JOIN PPBomEntry c ON b.FInterID=c.FInterID LEFT OUTER JOIN ICMO d ON b.FICMOInterID=d.FInterID 
 WHERE b.FDate>=CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),@dt,120)+'-28' ELSE CONVERT(VARCHAR(7),DATEADD(MONTH,-1,@dt),120)+'-28' END
 AND b.FDate<CASE WHEN DAY(@dt)>=28 THEN CONVERT(VARCHAR(7),DATEADD(MONTH,1,@dt),120)+'-28' ELSE CONVERT(VARCHAR(7),@dt,120)+'-28' END 
 AND d.FStatus='3')
  END
/*
删除在@t2表中在本期内有生产的“材料品号”的物料编码
*/
DELETE FROM @t2 
WHERE FItemID IN (SELECT FItemID FROM @t3)
RETURN
END

-->成本存储过程(查询以单据编号)
CREATE PROC sp_f_cost(@FBillNo VARCHAR(40))
AS
BEGIN
/*
  因为在自定义函数不能使用GETDATE()取当天的函数,所以定义字符串变量@dt,传入函数f_Cost_Pro中
*/
DECLARE @dt VARCHAR(10)
SET @dt=CONVERT(VARCHAR(10),GETDATE(),120)
SELECT [单据类型]=a.FTranType,[单据编号]=a.FBillNo,[成本]=SUM(a.FAuxStockQty*CASE WHEN  c.FBegQty=0 OR c.FBegQty IS NULL THEN 0 ELSE c.FBegBal/c.FBegQty END)
FROM f_Cost(@FBillNo,@dt) a 
LEFT OUTER JOIN t_ICItem b ON a.FItemID=b.FItemID
LEFT OUTER JOIN 
--利用先聚合再求值,求出当前最大的会计期间，并且仓库不为“不良品仓”的物料编码的期初单价
(SELECT b.FItemID,b.FPeriod,a.FStockID,a.FBegQty,a.FBegBal 
FROM
--求出的成本除以本期内该产品的入库数量，即为产品的单位成本
(SELECT FItemID,[FPeriod]=MAX(FPeriod) FROM ICInvInitial WHERE FStockID<>'1365' GROUP BY FItemID) b 
LEFT OUTER JOIN ICInvInitial a ON a.FItemID=b.FItemID AND a.FPeriod=b.FPeriod
WHERE a.FStockID<>'1365' )
c ON a.FItemID=c.FItemID
GROUP BY a.FTranType,a.FBillNo
END

-->成本自定义函数测试
DECLARE @dt VARCHAR(10)
SET @dt=CONVERT(VARCHAR(10),GETDATE(),120)
SELECT * FROM f_cost('WORK026867',@dt)

-->成本存储过程测试
EXEC sp_f_cost @FBillNo='WORK026867'