各位大侠,帮帮忙吧,实在想不出来啦.
各位大侠,情况是这样的,
例如:
某月1号,进货300件,单价50元,
同月2号,进货100件,单价100元,
同月3号,发货200件,单价100元,
同月4号,发货150件,单价50元,

问题如下:如何才能用存储过程实现求利润?
以上为例,这中间牵扯到第一次发货200件后,第一次进货还剩下100件,然后第二次发货150件,需发完第一次剩下的100件后,再发第二次进货的50件.

各位大侠,各位高手,各位高手之高手之高高手们,
请大家给出出主意吧.
小弟,技穷啦,实在想不出来了.

------解决方案--------------------

--先寫個游標的,你看看是不是你要的結果???


--測試環境
create table tabin(id int,name varchar(10),amount int,price int)
insert into tabin
select 1, 'aa ',300,50 union all
select 2, 'aa ',100,100

create table tabout(id int,name varchar(10),amount int,price int)
insert into tabout
select 1, 'aa ',200,100 union all
select 2, 'aa ',150,50
GO

/*求利潤*/
select *,[used]=0 into #t1 from tabin
select *,[money]=0 into #t2 from tabout

declare @id int,@name varchar(10),@amount int,@price int
declare c1 cursor for
select id,name,amount,price from #t2 order by id
open c1
fetch next from c1 into @id,@name,@amount,@price
while @@fetch_status=0
begin
update a
set [used]=case when isnull((select sum(amount) from #t1 where id <a.id and name=a.name and name=@name),0)> =@amount
then 0
else case when isnull((select sum(amount) from #t1 where id <=a.id and name=a.name and name=@name),0)> =@amount
then @amount-isnull((select sum(amount) from #t1 where id <a.id and name=a.name and name=@name),0)
else amount end
end
from #t1 a
where name=@name

update #T2 set [money]=amount*price-(select sum([used]*price) from #t1 where #t1.name=#t2.name)
where id=@id


update #t1 set amount=amount-[used]
delete #t1 where amount <=0
fetch next from c1 into @id,@name,@amount,@price
end
close c1
deallocate c1

/*查看結果*/
select * from #t2
/*
id name amount price money
----------- ---------- ----------- ----------- -----------
1 aa 200 100 10000
2 aa 150 50 -2500
*/

/*刪除測試環境*/
drop table tabin,tabout,#t1,#t2

------解决方案--------------------
那就create 成store procedure啊

Create Procedure dbo.usp_test
AS
set nocount on
select *,[used]=0 into #t1 from tabin
select *,[money]=0 into #t2 from tabout

declare @id int,@name varchar(10),@amount int,@price int
declare c1 cursor for
select id,name,amount,price from #t2 order by id
open c1
fetch next from c1 into @id,@name,@amount,@price
while @@fetch_status=0
begin
update a
set [used]=case when isnull((select sum(amount) from #t1 where id <a.id and name=a.name and name=@name),0)> =@amount
then 0
else case when isnull((select sum(amount) from #t1 where id <=a.id and name=a.name and name=@name),0)> =@amount
then @amount-isnull((select sum(amount) from #t1 where id <a.id and name=a.name and name=@name),0)
else amount end
end
from #t1 a
where name=@name

update #T2