存货核算中的后进先出

SET  QUOTED_IDENTIFIER  ON  
GO
SET  ANSI_NULLS  ON  
GO

ALTER     proc  CaculateProfit_LIFO


as


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

*/



-- 后进先出型
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
    


select  IODate,inputNum,inputUnitPrice
         into  #tblInput
             from  #tbl
             where  inputNum  is   not   null
                 and  inputUnitPrice  is   not   null
             order   by  IODate  asc
    


/*
select * from #tbl
select * from #tblInput
*/




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


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


declare   @出库剩余量   int ;
     set   @出库剩余量 = 0


     /*  找出第一个被减项 ,后进先出的第一个后进项 */
declare   @第一个出库日期   datetime
     select       top   1  
         @第一个出库日期 = IODate,
         @出库剩余量   =  outputNum 
             from  dbo.InvertoryAccouter
             where  outputNum  is   not   null
                 and  SalesPrice  is   not   null
                 order   by  IODate  asc
    


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


declare   @总利润   decimal ( 12 , 4 )
     set   @总利润 = 0



DECLARE  LIFO_cursor  cursor
FOR    select  IODate,InputNum,
        InputUnitPrice,OutputNum,
        CostUnitPrice,SalesUnitPrice,Profit 
             from  #tbl
        
     OPEN  LIFO_cursor
     FETCH   NEXT   FROM  LIFO_cursor
     INTO   @日期 , @入库数量 ,
             @入库单价 , @出库数量 ,
             @成本单价 , @销售单价 , @利润
     WHILE   @@FETCH_STATUS = 0
         BEGIN
             --  @当前累加成本 重新清零
             set   @当前累加成本 = 0

             if   @出库数量 > 0  
                 Begin
                     declare   @日期零点   datetime
                     -- 取时间零整点
                     select       @日期零点   =  ( convert ( nvarchar ( 10 ), datepart ( year , @日期 )) + ' - '
                             + convert ( nvarchar ( 10 ), datepart ( month , @日期 )) + ' - '
                             + convert ( nvarchar ( 10 ), datepart ( day , @日期 )))    
                    
                     select   top   1  
                         @当前被减项日期   =  IODate,
                         @当前被减项数量 = inputNum,
                         @当前被减项单价   =  inputUnitPrice
                         from  #tblInput
                         where  InputNum  is   not   null
                             and  inputUnitPrice  is   not   null
                             and  IODate < @日期零点
                             order   by  IODate  desc


                     SELECT    @出库剩余量   =   @出库数量
 
                     -- 如果出库数量大于0则循环递减
                     While ( @出库剩余量 > 0 )
                         Begin
                             IF ( @当前被减项数量 < @出库剩余量 )
                                 BEGIN
                                     -- 减少当前的库存 删除临时表中的库存相应纪录
                                     select   @出库剩余量   =   @出库剩余量 - @当前被减项数量
                                    
                                     select   @当前累加成本   =   @当前累加成本
                                                 + @当前被减项数量 * @当前被减项单价
                            
                                     delete   from  #tblInput  
                                         where  IODate  =   @当前被减项日期
                                            
                                     select   top   1  
                                         @当前被减项日期   =  IODate,
                                         @当前被减项数量 = inputNum,            
                                         @当前被减项单价   =  inputUnitPrice
                                         from  #tblInput
                                         where  InputNum  is   not   null
                                             and  inputUnitPrice  is   not   null
                                             and  IODate < @当前被减项日期
                                             order   by  IODate  desc
                                    
                                    
                                        
                                    
                                 END
                             ELSE
                                 BEGIN
                                     update  #tblInput 
                                         set  inputNum  =  (inputNum  -   @出库剩余量 )
                                         where  IODate  =   @当前被减项日期
                                    
                                     /* 输出当前虚拟表的工作情况    
                                    select * from #tblInput
                                     */
                                    
                                    
                                     select   @当前累加成本   =   @当前累加成本
                                                 + @出库剩余量 * @当前被减项单价     
            
                                                
                                     select   top   1  
                                         @当前被减项日期   =  IODate,
                                         @当前被减项数量 = inputNum,            
                                         @当前被减项单价   =  inputUnitPrice
                                         from  #tblInput
                                         where  InputNum  is   not   null
                                             and  inputUnitPrice  is   not   null
                                             and  IODate < @当前被减项日期

                                        
                                    
                                        
                                     select   @出库剩余量 = 0
                                        
                                    
                                 END
                            
                            
                         End
                    
                     -- print @当前累加成本/@出库数量    
                     select   @成本单价 = @当前累加成本 / @出库数量
                     select   @总利润   =   @总利润
                             + ( @销售单价 - @成本单价 ) * @出库数量
                 End
                    
                    
                    
                    
                        
                
                
             FETCH   NEXT   FROM  LIFO_cursor
             INTO   @日期 , @入库数量 ,
                     @入库单价 , @出库数量 ,
                     @成本单价 , @销售单价 , @利润
         END  
     CLOSE  LIFO_cursor
DEALLOCATE  LIFO_cursor


select   @总利润


GO
SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO

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