库存中是这样的: 物资编号 数量 价格 日期00001 1000 1.11 2006-10-1100001 2000 1.12 2006-10-1200001 3000 1.13 2006-10-1300001 4000 1.14 2006-10-14 我要调拨5000 那我在库存表中根据日期最早和价格最低的顺序来调拨出库 最后的结果就是: 物资编号 数量 价格 日期
00001 0 1.11 2006-10-1100001 0 1.12 2006-10-1200001 1000 1.13 2006-10-1300001 4000 1.14 2006-10-14
00001 0 1.11 2006-10-1100001 0 1.12 2006-10-1200001 1000 1.13 2006-10-1300001 4000 1.14 2006-10-14
create table 库存表(物资编号 varchar(10),数量 int,价格 numeric(20,6),日期 datetime)
insert into 库存表
select '00001',1000,1.11,'2006-10-11'
union all select '00001',2000,1.12,'2006-10-12'
union all select '00001',3000,1.13,'2006-10-13'
union all select '00001',4000,1.14,'2006-10-14'create proc up_test(@qty int)
as
declare @no varchar(10)
declare @sl int
declare @price numeric(20,6)
declare @date datetime
declare cur_tmp cursor for
select 物资编号,数量,价格,日期
from 库存表
order by 数量,日期
open cur_tmp
fetch next from cur_tmp into @no,@sl,@price,@date
while @@fetch_status=0
begin
select @qty = @qty - @sl
if @qty >= 0
begin
update 库存表
set 数量=0
where 物资编号=@no and 价格=@price and 日期=@date
end
else
begin
update 库存表
set 数量=abs(@qty)
where 物资编号=@no and 价格=@price and 日期=@date
goto ends
end
fetch next from cur_tmp into @no,@sl,@price,@date
end
close cur_tmp
deallocate cur_tmp
ends:
select '00001' as 物资编号, 1000 as 数量, 1.11 as 价格, cast('2006-10-11' as datetime) as 日期
into test
union select '00001', 2000, 1.12, '2006-10-12'
union select '00001', 3000, 1.13, '2006-10-13'
union select '00001', 4000, 1.14, '2006-10-14'
union select '00002', 4000, 1.14, '2006-10-11'
declare @No varchar(5), @Num int
select @No = '00001', @Num = 5000
------------------------------------------------------------
update aa
set aa.数量 = (case when b.物资编号 <> @No then b.数量 when b.总数量 <= @Num then 0 when b.总数量 - b.数量 <= @Num then b.总数量 - @Num else b.数量 end)
from test aa
join (select a.物资编号, (select sum(数量) from test where 价格 <= a.价格 and 日期 <= a.日期) as 总数量,
数量, 价格, 日期
from test a) b on aa.物资编号 = b.物资编号 and aa.价格 = b.价格 and aa.日期 <= b.日期select * from test
/*
物资编号 数量 价格 日期
----- ----------- ----- ------------------------------------------------------
00001 0 1.11 2006-10-11 00:00:00.000
00001 0 1.12 2006-10-12 00:00:00.000
00001 1000 1.13 2006-10-13 00:00:00.000
00001 4000 1.14 2006-10-14 00:00:00.000
00002 4000 1.14 2006-10-11 00:00:00.000*/
------------------------------------------------------------
drop table test