存货核算中的移动平均核算
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
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
*/
[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
*/
