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

  • 问题

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

    -->成本函数(查询以单据编号)
    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'
    
    2010年1月14日 8:22

答案

  • 这个你自己已经知道怎么去算成本了

    但是如何改成批量查询呢?你可以按照时间段查询单据号码,然后在根据这个号码来分别查询成本。

    如果直接用你这个查单个单据的去一个一个查,估计很慢,那么你需要修改你的代码,避免循环,多利用临时表来完成。不过这个还得靠你自己,毕竟我们不了解你系统表结构。

    具体思路问题可以讨论,代码还是自己写吧。


    family as water
    2010年1月14日 13:43