如何计算先进先出销售成本
请教各位大虾,我有一个明细表mingx
id 商品代码 数量 价格 日期 类别
1 001 10 8 2007-03-01 采购
2 001 8 12 2007-03-10 采购
3 001 12 6 2007-04-15 采购
4 002 5 10 2007-04-02 采购
5 001 2 16 2007-04-05 销售
6 002 1 20 2007-04-02 销售
7 001 13 16 2007-04-20 销售
8 001 1 16 2007-05-01 销售
假设我准备2007-05-10销售001商品5个，mingx表中还没有生成该条记录，我的目的是按照先进先出方法用sql语句得出这5个商品的销售成本2*12元+3*6元=42元，请问各位大虾怎么写sql语句或存储过程,急
[解决办法]
create table mingxi(id nvarchar(20),code nvarchar(20),num int ,price int ,Da datetime, sort nvarchar (20))
insert into mingxi
select 1 , '001 ' , 10 , 8 , '2007-03-01 ' , 'caigou '
union select
2 , '001 ' ,8 , 12 , '2007-03-10 ' , 'caigou '
union select
3 , '001 ' ,12 , 6 , '2007-04-15 ' , 'caigou '
union select
4 , '002 ' , 5 , 10 , '2007-04-02 ' , 'caigou '
union select
5 , '001 ' , 2 , 16 , '2007-04-05 ' , 'xiaoshou '
union select
6 , '002 ' , 1 , 20 , '2007-04-02 ' , 'xiaoshou '
union select
7 , '001 ' ,13 , 16 , '2007-04-20 ' , 'xiaoshou '
union select
8 , '001 ' , 1 , 16 , '2007-05-01 ' , 'xiaoshou '
CREATE PROCEDURE [dbo].[aes_b]
@c nvarchar(20),
@date datetime
as
begin
declare @id nvarchar(50)
declare @code nvarchar(50)
declare @num int
declare @price int
declare @p int
declare @num_day int
declare @total int
declare @num_sum int
set @p=0
set @total=0
select @num_sum=sum(num) from mingxi where da <=@date and code=@c and sort= 'xiaoshou '
select @num_day =sum(num) from mingxi where da=@date and code=@c and sort= 'xiaoshou '
create table #ming (total int )
declare ming cursor
for select distinct id,code,num,price from mingxi where sort= 'caigou ' and code=1
open ming
fetch next from ming
into @id ,@code,@num,@price
while @@fetch_status=0
begin
set @p=@p+@num
if @p> @num_sum-@num_day and @p> =@num_sum AND @p-@num <@num_sum-@num_day
begin
set @total=@total+(@num_day)*@price
end
if @p> @num_sum-@num_day and @p <=@num_sum
begin
set @total=@total+(@p+@num_day-@num_sum)*@price
end
if @p-@num> @num_sum-@num_day and @p-@num <@num_sum
begin
set @total=@total+(@num_sum-@p+@num)*@price
end
fetch next from ming
into @id ,@code,@num,@price
end
close ming
deallocate ming
insert into #ming values (@total)
select * from #ming
end
GO
[解决办法]
修正以上两错误
drop table mingx
create table mingx
(
id int identity(1,1),
商品代码 varchar(5),
数量 int,
价格 int,
日期 datetime,
类别 varchar(5)
)
go
insert into mingx(商品代码,数量,价格,日期,类别) values ( '001 ',10,8, '2007-03-01 ', '采购 ')
insert into mingx(商品代码,数量,价格,日期,类别) values ( '001 ',8,12, '2007-03-10 ', '采购 ')
insert into mingx(商品代码,数量,价格,日期,类别) values ( '001 ',12,6, '2007-04-15 ', '采购 ')
insert into mingx(商品代码,数量,价格,日期,类别) values ( '002 ',5,10, '2007-04-02 ', '采购 ')
insert into mingx(商品代码,数量,价格,日期,类别) values ( '001 ',2,16, '2007-04-05 ', '销售 ')
insert into mingx(商品代码,数量,价格,日期,类别) values ( '002 ',1,20, '2007-04-02 ', '销售 ')
insert into mingx(商品代码,数量,价格,日期,类别) values ( '001 ',13,16, '2007-04-20 ', '销售 ')
insert into mingx(商品代码,数量,价格,日期,类别) values ( '001 ',1,16, '2007-05-01 ', '销售 ')
--采购表与销售表分离出来
select * into #in from mingx where 类别= '采购 ' order by 日期
select * into #out from mingx where 类别= '销售 ' order by 日期
--如改为存储过程此处定义传入代码及数量
declare @dm varchar(10)
declare @sl int
set @dm= '001 '
set @sl =14
declare @datebegin datetime
declare @dateend datetime
select top 1 @datebegin=日期 from #in a
where 商品代码=@dm and (select sum(数量) from #in where 商品代码=@dm and 日期 <=a.日期)> (select sum(数量) from #out where 商品代码=@dm)
select top 1 @dateend=日期 from #in a
where 商品代码=@dm and (select sum(数量) from #in where 商品代码=@dm and 日期 <=a.日期)> (select sum(数量) from #out where 商品代码=@dm)+@sl
if @dateend is null
select @dateend = max(日期) from #in where 商品代码=@dm
--取出指定时间段内进货符合出货数量的集合
if @datebegin <@dateend
begin
select
出货数量=
(
case 日期
when @datebegin then
(select sum(数量)-(select sum(数量) from #out where 商品代码=@dm) from #in where 商品代码=@dm and 日期 <=@datebegin)
when @dateend then
(select (select sum(数量) from #out where 商品代码=@dm)+@sl-sum(数量) from #in where 商品代码=@dm and 日期 <@dateend)
else
数量
end
),价格 from #in where 商品代码=@dm and 日期> =@datebegin and 日期 <=@dateend
end
else
begin
select 出货数量=@sl,价格 from #in where 商品代码=@dm and 日期> =@datebegin and 日期 <=@dateend
end
drop table #in,#out
