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

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
*/