存货核算中的加权平均

SET  QUOTED_IDENTIFIER  ON  
GO
SET  ANSI_NULLS  ON  
GO

ALTER    proc  CaculateProfit_JiaQuanPingJun

as

-- 加权平均
-- 创建虚拟表
-- 1.计算出所有的进货成本

/*
   drop table #tbl
   drop table #tbl2
*/



declare       @期初数量   int ,
         @期初单价   decimal ( 12 , 4 )
        
         set   @期初数量 = 20
         set   @期初单价 = 200.00


declare       @入库总数量   int ,
         @入库总成本   decimal ( 12 , 4 ),
         @入库成本单价   decimal ( 12 , 4 )
        
         set   @入库总数量 = 0
         set   @入库总成本 = 0
         set   @入库成本单价 = 0
        
         select   @入库总数量 = sum ( isnull (inputNum, 0 )),
             @入库总成本 = sum ( isnull (inputNum, 0 ) * isnull (inputPrice, 0 ))
             from  dbo.InvertoryAccouter
         -- 计算出成本单价
         select   @入库成本单价   =   @入库总成本 / @入库总数量


create   table  #tbl(IODate   datetime   default ( getdate ()),
            InputNum  int ,
            InputUnitPrice  decimal ( 12 , 4 ),
            OutputNum  int ,
            CostUnitPrice  decimal ( 12 , 4 ),
            SalesUnitPrice  decimal ( 12 , 4 ),
            Profit  decimal ( 12 , 4 ))


select  IODate,
    inputNum,
    inputPrice,
    outputNum,
    SalesPrice,
     case   when  (outputNum  is   not   null  
                 and  
                SalesPrice  is   not   null )
             then   @入库成本单价
         else
              null
        
     end  CostUnitPrice
     into  #tbl2
         from  dbo.InvertoryAccouter
         order   by  IODate  asc



insert   into  #tbl
    (IODate,InputNum,InputUnitPrice,
     -- InputCost,
    OutputNum,SalesUnitPrice,CostUnitPrice)

select   ' 2001-01-31 23:59:59 ' ,
     @期初数量 ,
     @期初单价 ,
     null ,
     null ,
     null
    
     union   all
select   *  
     from  #tbl2
    
-- select * from #tbl


select     SUM ( isnull (OutputNum, 0 ) *
        ( isnull (SalesUnitPrice, 0 ) - isnull (CostUnitPrice, 0 )))
     from  #tbl







GO
SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO

转载于:https://www.cnblogs.com/Bruce_H21/archive/2007/08/02/839987.html