存货核算中的移动平均核算

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



ALTER     proc CaculateProfit_YiDongPingJun

as

--创建虚拟表
--1.计算出所有的进货成本
/*
   drop table #tbl
   drop table #tbl2
*/
declare     @期初数量 int,
        @期初单价 decimal(12,4)
        
        set @期初数量=20
        set @期初单价=200.00


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


select IODate,
    inputNum,
    inputPrice,
    case when inputNum is null
        then null
        when inputPrice is null
        then null
        else
            inputNum*inputPrice
    end InputCost,
    outputNum,
    SalesPrice
    into #tbl2
        from dbo.InvertoryAccouter
        order by IODate asc




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

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

--2.计算出所有的成本单价

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


declare @截至昨日入库总成本 decimal(12,4),
    @截至今天出库总成本 decimal(12,4),
    @截至昨日出库总数量 int,
    @截至昨日库存 int
    set @截至昨日入库总成本=0
    set @截至今天出库总成本=0
    set @截至昨日出库总数量=0
    set @截至昨日库存 = 0


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


DECLARE Customer_cursor CURSOR 

    FOR  select IODate ,
        InputNum,
        InputUnitPrice ,
        InputCost ,
        OutputNum ,
        CostUnitPrice,
        SalesUnitPrice,
        Profit
    from #tbl
    
    OPEN Customer_cursor
    FETCH NEXT FROM Customer_cursor 
            INTO @日期,@入库数量,@入库单价,@入库成本,@出库数量,@成本单价,@销售单价,@利润
   
       WHILE @@FETCH_STATUS = 0
             Begin
                
            declare @date datetime
            
            select  @date=(convert(nvarchar(10),datepart(year,@日期))+'-'
                            +convert(nvarchar(10),datepart(month,@日期))+'-'
                            +convert(nvarchar(10),datepart(day,@日期)))
            
            select @截至昨日入库总成本 =isnull(sum(isnull(inputNum,0)*isnull(inputPrice,0)),0),
                   @截至昨日出库总数量 = sum(isnull(outputNum,0)),
                   @截至昨日库存 = isnull(sum(isnull(inputNum,0)-isnull(outputNum,0)),0)
                from dbo.InvertoryAccouter
                    where IODate< @date
            
            
            --加上期初值     
            select @截至昨日入库总成本= @截至昨日入库总成本+(@期初数量*@期初单价)
            select @截至昨日库存 = @截至昨日库存+@期初数量
            
            --检查截至昨天是否有出库,有则@截至昨日入库总成本应当减去出库总成本            
            if (@截至昨日出库总数量 >0)
                --计算当天成本单价
                begin
                    --@截至今天出库总成本 在执行循环累加之前还是昨天的数据
                    select @成本单价 = (@截至昨日入库总成本-@截至今天出库总成本)/@截至昨日库存
                end
            else
                begin
                    select @成本单价 = @截至昨日入库总成本/@截至昨日库存
                end

            

            
            select @截至今天出库总成本= @截至今天出库总成本+(isnull(@出库数量,0)*@成本单价)

            
            if @出库数量>0
                begin
                    --print @成本单价
                    --3.计算利润
                    select @利润 = (@销售单价-@成本单价)*@出库数量
                    
                    
                    select @总利润 = @总利润+@利润
                    
                    --4.统计出总利润
                    --print @总利润
                end
            

            
                
                 FETCH NEXT FROM Customer_cursor 
                     INTO @日期,@入库数量,@入库单价,@入库成本,@出库数量,@成本单价,@销售单价,@利润
             End

CLOSE Customer_cursor
DEALLOCATE Customer_cursor



select @总利润





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

创建相关的数据表

CREATE TABLE [InvertoryAccouter] (
    [ids] [int] IDENTITY (1, 1) NOT NULL ,
    [IODate] [datetime] NOT NULL CONSTRAINT [DF_InvertoryAccouter_IODate] DEFAULT (getdate()),
    [inputNum] [int] NULL ,
    [inputPrice] [decimal](18, 2) NULL ,
    [outputNum] [int] NULL ,
    [SalesPrice] [decimal](18, 2) NULL ,
    CONSTRAINT [PK_InvertoryAccouter] PRIMARY KEY  CLUSTERED 
    (
        [ids]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO

insert into InvertoryAccouter
 
select    '2007-08-01 09:24:15.000',15,190.00,NULL,NULL
select    '2007-08-02 09:25:00.000',30,185.00,NULL,NULL
select    '2007-08-02 09:26:01.000',NULL,NULL,21,300.00
select    '2007-08-02 09:26:04.000',NULL,NULL,5,300.00
select    '2007-08-03 09:26:04.000',NULL,NULL,10,300.00
select    '2007-08-04 09:27:29.000',25,190.00,NULL,NULL
select    '2007-08-05 09:27:56.000',5,210.00,NULL,NULL
select    '2007-08-05 09:29:02.000',NULL,NULL,11,300.00
select    '2007-08-06 09:29:57.000',5,215.00,NULL,NULL
select    '2007-08-06 09:30:09.000',6,210.00,NULL,NULL
select    '2007-08-07 09:30:48.000',NULL,NULL,10,300.00
select    '2007-08-08 09:30:49.000',NULL,NULL,10,300.00


/*
最后的结果为：7164.4695
*/