27,580
社区成员
发帖
与我相关
我的任务
分享统计有效期报表
入仓表（input）
code编号 date1入货日期 quantity数量 date2有效日期
code date1 quantity date2
0001 2011-08-01 10 2012-01-01
0002 2011-09-01 3 2012-02-01
0001 2011-09-02 20 2012-02-01
0001 2011-10-01 10 2012-03-01
库存表（stock）
code name quantity
0001 胶囊 35
0002 素片 2
0003 维生素片 2
要求：
根据库存数量，计算出入仓表中的库存有效日期商品，入仓中超过库存数量的不计（先进先出），即把input表的date1(入仓日期)按DESC排序，把最后入库的商品跟库存计算。
想得到如下结果：
code name quantity input.quantity input.date2
0001 胶囊 35 10 2012-03-01
0001 胶囊 35 20 2012-02-01
0001 胶囊 35 5 2012-01-01
0002 素片 2 2 2012-02-01
0003 维生素片 2
请指教！
code编号 date1入货日期 quantity数量 date2有效日期
code date1 quantity date2
0001 2011-08-01 10 2012-01-01
0002 2011-09-01 3 2012-02-01
0001 2011-09-02 20 2012-02-01
0001 2011-10-01 10 2012-03-01
库存表（stock）
code name quantity
0001 胶囊 35
0002 素片 2
0003 维生素片 2
要求：
根据库存数量，计算出入仓表中的库存有效日期商品，入仓中超过库存数量的不计（先进先出），即把input表的date1(入仓日期)按DESC排序，把最后入库的商品跟库存计算。
想得到如下结果：
code name quantity input.quantity input.date2
0001 胶囊 35 10 2012-03-01
0001 胶囊 35 20 2012-02-01
0001 胶囊 35 5 2012-01-01
0002 素片 2 2 2012-02-01
0003 维生素片 2
请指教！
...全文
请发表友善的回复…
发表回复
jiemo587 2011-09-15
- 打赏
- 举报
/*
--最后的结果
code name quantity input.quantity input.date1 input.date2 num_id
---------- ---------- ----------- -------------- ----------------------- ----------------------- --------------------
0001 胶囊 35 1 2012-11-03 00:00:00.000 2013-05-01 00:00:00.000 1
0001 胶囊 35 5 2012-10-03 00:00:00.000 2013-04-01 00:00:00.000 2
0001 胶囊 35 10 2011-12-05 00:00:00.000 2012-05-01 00:00:00.000 3
0001 胶囊 35 19 2011-11-03 00:00:00.000 2012-04-01 00:00:00.000 4
0001 胶囊 35 0 2011-10-01 00:00:00.000 2012-03-01 00:00:00.000 5
0001 胶囊 35 0 2011-09-02 00:00:00.000 2012-02-01 00:00:00.000 6
0001 胶囊 35 0 2011-08-01 00:00:00.000 2012-01-01 00:00:00.000 7
0002 素片 2 2 2011-09-01 00:00:00.000 2012-02-01 00:00:00.000 1
0003 维生素片 2 NULL NULL NULL 1
(9 行受影响)
*/
jiemo587 2011-09-15
- 打赏
- 举报
create table #input (code varchar(10),date1 datetime,quantity int,date2 datetime)
insert into #input
values
('0001', '2011-08-01', 10, '2012-01-01'),
('0002', '2011-09-01', 3, '2012-02-01'),
('0001', '2011-09-02', 20, '2012-02-01'),
('0001', '2011-10-01', 10, '2012-03-01'),
('0001', '2011-11-03', 20, '2012-04-01'),
('0001', '2012-10-03', 5, '2013-04-01'),
('0001', '2012-11-03', 1, '2013-05-01'),
('0001', '2011-12-05', 10, '2012-05-01')
create table #stock(code varchar(10),name varchar(10),total int)
insert into #stock
values
('0001', '胶囊', 35),
('0002', '素片', 2),
('0003', '维生素片',2)
--select * from #input
--select * from #stock
--增加排名，是为了游标中的WHile语句循环
select b.code,b.name,b.total as quantity,a.quantity as [input.quantity],
date1 as [input.date1],date2 as [input.date2],num_id=ROW_NUMBER() over (partition by a.code order by date1 desc)
into #2
from #input a full join #stock b on a.code=b.code
--select * from #2
declare @code varchar(10)
DECLARE OnceCustomerUpgrade CURSOR FOR
select distinct code from #2 where [input.quantity] is not null order by code
OPEN OnceCustomerUpgrade
FETCH NEXT FROM OnceCustomerUpgrade INTO @code
WHILE (@@FETCH_STATUS = 0)
begin
declare @i int
set @i=1
while(@i<=(select count(1) from #2 where code=@code))
begin
declare @total int
declare @quantity int
declare @input_quantity int
set @total=(select isnull(sum([input.quantity]),0) from #2 where code=@code and num_id<@i)
select @quantity=quantity,@input_quantity=[input.quantity] from #2 where code=@code and num_id=@i
if(@quantity>@total)
begin
if(@input_quantity<=@quantity-@total)
begin
update #2 set [input.quantity]=@input_quantity where code=@code and num_id=@i
end
else
begin
update #2 set [input.quantity]=@quantity-@total where code=@code and num_id=@i
end
set @i=@i+1
end
else
begin
update #2 set [input.quantity]=0 where code=@code and num_id>=@i
set @i=(select count(1)+1 from #2 where code=@code)
end
end
FETCH NEXT FROM OnceCustomerUpgrade INTO @code
END
CLOSE OnceCustomerUpgrade --关闭游标
DEALLOCATE OnceCustomerUpgrade
select * from #2 order by code,[input.date2] desc
/*
code name quantity input.quantity input.date1 input.date2 num_id
---------- ---------- ----------- -------------- ----------------------- ----------------------- --------------------
0001 胶囊 35 1 2012-11-03 00:00:00.000 2013-05-01 00:00:00.000 1
0001 胶囊 35 5 2012-10-03 00:00:00.000 2013-04-01 00:00:00.000 2
0001 胶囊 35 10 2011-12-05 00:00:00.000 2012-05-01 00:00:00.000 3
0001 胶囊 35 20 2011-11-03 00:00:00.000 2012-04-01 00:00:00.000 4
0001 胶囊 35 10 2011-10-01 00:00:00.000 2012-03-01 00:00:00.000 5
0001 胶囊 35 20 2011-09-02 00:00:00.000 2012-02-01 00:00:00.000 6
0001 胶囊 35 10 2011-08-01 00:00:00.000 2012-01-01 00:00:00.000 7
0002 素片 2 3 2011-09-01 00:00:00.000 2012-02-01 00:00:00.000 1
0003 维生素片 2 NULL NULL NULL 1
(9 行受影响)
*/
--給分吧
sunfor 2011-09-15
- 打赏
- 举报
用这个方法可以，但如果入仓数据很大，如0001商品有1000条记录，其实我只统计它前3条记录，
后面的在游标里判别小于35的就不用比较了，直接跳到0002商品。
code name quantity input.quantity input.date2
0001 胶囊 35 10 2012-03-01
0001 胶囊 35 20 2012-02-01
0001 胶囊 35 5 2012-01-01
0001 胶囊 35 0 2011-12-01
0001 胶囊 35 0 2011-12-01
0001 胶囊 35 0 2011-11-01
0001 胶囊 35 0 2011-10-01
0001 胶囊 35 0 2011-10-01
....
0001 胶囊 35 0 2011-01-01
0002 ....
[Quote=引用 4 楼 jiemo587 的回复:]
SQL code
--用游标
create table #input (code varchar(10),date1 datetime,quantity int,date2 datetime)
insert into #input
values
('0001', '2011-08-01', 10, '2012-01-01'),
('0002', '2011-09-01', ……
[/Quote]
后面的在游标里判别小于35的就不用比较了，直接跳到0002商品。
code name quantity input.quantity input.date2
0001 胶囊 35 10 2012-03-01
0001 胶囊 35 20 2012-02-01
0001 胶囊 35 5 2012-01-01
0001 胶囊 35 0 2011-12-01
0001 胶囊 35 0 2011-12-01
0001 胶囊 35 0 2011-11-01
0001 胶囊 35 0 2011-10-01
0001 胶囊 35 0 2011-10-01
....
0001 胶囊 35 0 2011-01-01
0002 ....
[Quote=引用 4 楼 jiemo587 的回复:]
SQL code
--用游标
create table #input (code varchar(10),date1 datetime,quantity int,date2 datetime)
insert into #input
values
('0001', '2011-08-01', 10, '2012-01-01'),
('0002', '2011-09-01', ……
[/Quote]
jiemo587 2011-09-14
- 打赏
- 举报
--用游标
create table #input (code varchar(10),date1 datetime,quantity int,date2 datetime)
insert into #input
values
('0001', '2011-08-01', 10, '2012-01-01'),
('0002', '2011-09-01', 3, '2012-02-01'),
('0001', '2011-09-02', 20, '2012-02-01'),
('0001', '2011-10-01', 10, '2012-03-01')
create table #stock(code varchar(10),name varchar(10),total int)
insert into #stock
values
('0001', '胶囊', 35),
('0002', '素片', 2),
('0003', '维生素片',2)
select * from #input
select * from #stock
select b.code,b.name,b.total as quantity,a.quantity as [input.quantity],
date1 as [input.date1],date2 as [input.date2] into #2 from #input a full join #stock b on a.code=b.code
declare @code varchar(10)
declare @quantity int
declare @input_quantity int
declare @input_date1 datetime
DECLARE OnceCustomerUpgrade CURSOR FOR
select code,quantity,[input.quantity],[input.date1] from #2 order by code,[input.date1] desc
OPEN OnceCustomerUpgrade
FETCH NEXT FROM OnceCustomerUpgrade INTO @code,@quantity,@input_quantity,@input_date1
WHILE (@@FETCH_STATUS = 0)
begin
declare @total int
set @total=(select sum([input.quantity]) from #2 where code=@code and [input.date1]>@input_date1)
if(@quantity>=@total)
begin
if(@input_quantity<=@quantity-@total)
update #2 set [input.quantity]=@input_quantity where code=@code and [input.date1]=@input_date1
else
update #2 set [input.quantity]=@quantity-@total where code=@code and [input.date1]=@input_date1
end
FETCH NEXT FROM OnceCustomerUpgrade INTO @code,@quantity,@input_quantity,@input_date1
END
CLOSE OnceCustomerUpgrade --关闭游标
DEALLOCATE OnceCustomerUpgrade
update #2 set [input.quantity]=quantity where [input.quantity]>quantity --入仓中超过库存数量的不计
select * from #2 order by code,[input.date2] desc
/*
code name quantity input.quantity input.date1 input.date2
---------- ---------- ----------- -------------- ----------------------- -----------------------
0001 胶囊 35 10 2011-10-01 00:00:00.000 2012-03-01 00:00:00.000
0001 胶囊 35 20 2011-09-02 00:00:00.000 2012-02-01 00:00:00.000
0001 胶囊 35 5 2011-08-01 00:00:00.000 2012-01-01 00:00:00.000
0002 素片 2 2 2011-09-01 00:00:00.000 2012-02-01 00:00:00.000
0003 维生素片 2 NULL NULL NULL
(5 行受影响)
*/
--小F-- 2011-09-14
- 打赏
- 举报
--库存先进先出简单例子：
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'
go
create 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--小F-- 2011-09-14
- 打赏
- 举报
精华帖子里面有个先进先出的实例 楼主可以看看
-晴天 2011-09-14
- 打赏
- 举报
可以用递归做.
sunfor 2011-09-14
- 打赏
- 举报
这个是库存处理的，但我现要对入仓日期及数量处理。
[Quote=引用 3 楼 fredrickhu 的回复:]
SQL code
--库存先进先出简单例子：
create table t(
。。。
insert into t(name,j,c,jdate) select ……
[/Quote]
[Quote=引用 3 楼 fredrickhu 的回复:]
SQL code
--库存先进先出简单例子：
create table t(
。。。
insert into t(name,j,c,jdate) select ……
[/Quote]
