存货核算中的先进先出

SET  QUOTED_IDENTIFIER  ON  
GO
SET  ANSI_NULLS  ON  
GO

ALTER     proc  CaculateProfit_FIFO



as
    
/*

drop table #tbl
drop table #tbl2

*/
    

declare       @期初时间   datetime ,
         @期初数量   int ,
         @期初单价   decimal ( 12 , 4 )
        
         set   @期初时间 = ' 2001-01-31 23:59:59 '
         set   @期初数量 = 20
         set   @期初单价 = 200.00


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
     into  #tbl2
         from  dbo.InvertoryAccouter
         order   by  IODate  asc




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

select    @期初时间 ,
     @期初数量 ,
     @期初单价 ,
     null ,
     null
    
     union   all
select   *  
     from  #tbl2
    

    


declare   @日期   datetime ,
     @入库数量   int ,
     @入库单价   decimal ( 12 , 4 ),
     @出库数量   int ,
     @成本单价   decimal ( 12 , 4 ),
     @销售单价   decimal ( 12 , 4 ),
     @利润      decimal ( 12 , 4 )


declare   @当前被减项日期   datetime ,
     @当前被减项数量   int ,
     @当前被减项剩余数量   int ,
     @当前被减项单价   decimal ( 12 , 4 )

     set   @当前被减项日期 = @期初时间
     set   @当前被减项数量 = @期初数量
     set   @当前被减项剩余数量 = @期初数量
     set   @当前被减项单价 =   @期初单价

declare   @当前累加成本   decimal ( 12 , 4 )


declare   @总利润   decimal ( 12 , 4 )
     set   @总利润 = 0
    
DECLARE  FIFO_cursor  cursor
For   select  IODate,InputNum,
        InputUnitPrice,OutputNum,
        CostUnitPrice,SalesUnitPrice,Profit 
             from  #tbl

     OPEN  FIFO_cursor
     FETCH   NEXT   from  FIFO_cursor
         INTO   @日期 , @入库数量 ,
             @入库单价 , @出库数量 ,
             @成本单价 , @销售单价 , @利润

         WHILE   @@FETCH_STATUS = 0
             Begin
                        
                 --  @当前累加成本 重新清零
                 set   @当前累加成本 = 0
    
                 --     成本单价 = 成本/数量
                 if   @出库数量 > 0  
                     Begin
                         if   @当前被减项剩余数量 < @出库数量
                             Begin
                                 while ( @当前被减项剩余数量 < @出库数量 )
                                     begin
                                         -- 先统计当前项的累加成本准备和下个选项减出来的成本相加
                                         select   @当前累加成本 = @当前被减项剩余数量 * @当前被减项单价
                                        
                                         -- 移动到下一条入库纪录
                                         select   top   1  
                                          @当前被减项日期   =  IODate,
                                          @当前被减项数量 = inputNum,
                                          @当前被减项剩余数量 = @当前被减项剩余数量 + inputNum,
                                          @当前被减项单价   =  inputPrice
                                             from  dbo.InvertoryAccouter
                                                 where  IODate > @当前被减项日期
                                                     and  inputNum  is   not   null
                                                     and  inputPrice  is   not   null
                                                 order   by  IODate  asc
                                         -- select @当前被减项剩余数量= @当前被减项剩余数量-@出库数量
                                                            
                                     end
                                 -- 一直循环累加直至发现现在的 @当前被减项剩余数量 已经大于 @出库数量
                                 select   @当前被减项剩余数量 = @当前被减项剩余数量 - @出库数量
            
                                 select   @当前累加成本   =   @当前累加成本 +
                                            ( @当前被减项数量 - @当前被减项剩余数量 ) * @当前被减项单价
                                
                                 -- 计算出 出库的 成本单价
                                 select   @成本单价   =   @当前累加成本 / @出库数量
                                
                                 -- print (@销售单价-@成本单价)*@出库数量
                                
                                 select   @总利润   =   @总利润   +  ( @销售单价 - @成本单价 ) * @出库数量
                                 -- print @总利润
                             End
                        
                        
                         else
                             -- 如果 @出库数量<@当前被减项剩余数量
                             Begin
                                
                                 select   @当前被减项剩余数量 = @当前被减项剩余数量 - @出库数量
                                 /*
                                select @当前累加成本 = @当前被减项单价*@出库数量
                                --计算出 出库的 成本单价
                                select @成本单价 = @当前累加成本/@出库数量
                                 */
                                 select   @成本单价   =   @当前被减项单价
        
                                 -- print (@销售单价-@成本单价)*@出库数量    
                                
                                 select   @总利润   =   @总利润   +  ( @销售单价 - @成本单价 ) * @出库数量     
                                 -- print @总利润        
                             End
                        
                        
                     End  
                    
                
                 -- print @日期
                
                
                 FETCH   NEXT   from  FIFO_cursor
                     INTO   @日期 , @入库数量 ,
                         @入库单价 , @出库数量 ,
                         @成本单价 , @销售单价 , @利润
             End
            
    
     CLOSE  FIFO_cursor
DEALLOCATE  FIFO_cursor        

select    @总利润



GO
SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO

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