create table 商品基本信息表
(
商品编号 int,
商品名称 varchar(50)
)
insert into 商品基本信息表 select 1,'商品A'create table 入库表
(
商品编号 int,
入库批次 int,
入库时间 datetime
入库数量 int,
已经出库数量 int
)
insert into 入库表 select 1,1,'2010-1-1',100,100
insert into 入库表 select 1,2,'2010-1-1',50,10create table 销售表
(
商品编号 int,
销售时间 datetime,
销售数量 int
)
insert into 销售表 select 1,'2010-1-2',110drop table 商品基本信息表
drop table 入库表
drop table 销售表
--------------------------------------------------------------------
问题：
1.如何在销售表中销售了“商品A”的数量110个之后，在“入库表”中自动按先进先出的规则递减库存，就是按照批次递减，第一批的减完，再减第二批的。
2.库存动向查询报表
商品编号 商品名称 入库时间 入库批次 入库数量 销售时间 销售数量 库存结余
1 商品A 2010-1-1 1 100 0 100
1 商品A 2010-1-1 2 50 0 50
1 商品A 2010-1-2 110 40
(
商品编号 int,
商品名称 varchar(50)
)
insert into 商品基本信息表 select 1,'商品A'create table 入库表
(
商品编号 int,
入库批次 int,
入库时间 datetime
入库数量 int,
已经出库数量 int
)
insert into 入库表 select 1,1,'2010-1-1',100,100
insert into 入库表 select 1,2,'2010-1-1',50,10create table 销售表
(
商品编号 int,
销售时间 datetime,
销售数量 int
)
insert into 销售表 select 1,'2010-1-2',110drop table 商品基本信息表
drop table 入库表
drop table 销售表
--------------------------------------------------------------------
问题：
1.如何在销售表中销售了“商品A”的数量110个之后，在“入库表”中自动按先进先出的规则递减库存，就是按照批次递减，第一批的减完，再减第二批的。
2.库存动向查询报表
商品编号 商品名称 入库时间 入库批次 入库数量 销售时间 销售数量 库存结余
1 商品A 2010-1-1 1 100 0 100
1 商品A 2010-1-1 2 50 0 50
1 商品A 2010-1-2 110 40
if object_id('入库表')is not null drop table 入库表
go
create table 入库表
(
商品编号 int,
入库批次 int,
入库时间 datetime,
入库数量 int,
已经出库数量 int
)insert 入库表
select 1,1,'2010-09-03',200,0 union all
select 2,1,'2010-09-04',100,0 union all
select 1,2,'2010-09-05',50,0 union all
select 1,3,'2010-09-06',200,0 union all
select 1,4,'2010-09-07',200,0 union all
select 1,5,'2010-09-08',200,0 union all
select 1,6,'2010-09-09',200,0 union all
select 1,7,'2010-09-10',200,0 union all
select 1,8,'2010-09-20',200,0
declare @OutNum int ,@n int
set @OutNum=510
set @n=0 --本次出库数量update 入库表
set @n=case when 入库数量-已经出库数量>=@OutNum then @OutNum else 入库数量-已经出库数量 end,
@OutNum=@OutNum-@n,
已经出库数量=@n
where 商品编号=1select * from 入库表商品编号 入库批次 入库时间 入库数量 已经出库数量
----------- ----------- ----------------------- ----------- -----------
1 1 2010-09-03 00:00:00.000 200 200
2 1 2010-09-04 00:00:00.000 100 0
1 2 2010-09-05 00:00:00.000 50 50
1 3 2010-09-06 00:00:00.000 200 200
1 4 2010-09-07 00:00:00.000 200 60
1 5 2010-09-08 00:00:00.000 200 0
1 6 2010-09-09 00:00:00.000 200 0
1 7 2010-09-10 00:00:00.000 200 0
1 8 2010-09-20 00:00:00.000 200 0(9 行受影响)
--库存先进先出简单例子：create table t(
id int identity(1,1),
name varchar(50),--商品名称
j int, --入库数量
c int, --出库数量
jdate datetime --入库时间
)
insert into t(name,j,c,jdate) select 'A',100,0,'2007-12-01'
insert into t(name,j,c,jdate) select 'A',200,0,'2008-01-07'
insert into t(name,j,c,jdate) select 'B',320,0,'2007-12-21'
insert into t(name,j,c,jdate) select 'A',100,0,'2008-01-15'
insert into t(name,j,c,jdate) select 'B',90,0,'2008-02-03'
insert into t(name,j,c,jdate) select 'A',460,0,'2008-02-01'
insert into t(name,j,c,jdate) select 'A',510,0,'2008-03-01'
gocreate proc wsp
@name varchar(50),--商品名称
@cost int --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(j)-sum(c) from t where name=@name
if(@spare>=@cost)
begin
--根据入库日期采用先进先出原则对货物的库存进行处理
update t set c=
case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and j!=c)>=0
then a.j
else
case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<a.jdate and j!=c)<0 then 0
else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c)
end
end
from t a where name=@name and j!=c
end
else
raiserror('库存不足',16,1)
return
go
--测试：exec wsp @name='A',@cost=180
select * from t
--drop table t
--drop proc wsp