January 31, 2008 at 7:10 pm
dear all,
i have a table named #stock with the record like below
Product|STATUS|BEGIN|TRX|END
A IN 0 5 0
A IN 0 1 0
A OUT 0 2 0
i want to make a store procedure which the result like
Product|STATUS|BEGIN|TRX|END
A IN 0 5 5
A IN 5 1 6
A OUT 6 2 4
i want to update begin and END fields one by one record,depend on the status field but the begin field must be filled depend on the previous record to calculate the end field..
anyone can help me,what should i write using SQL command,because I'm new using SQL,thanks in advance..
regards
martell
January 31, 2008 at 10:38 pm
Presumably that you have another column which maintains the uniqueness and sorting order, such as
1. An identifier such as ‘ID’
2. A ‘DateTime’ field which is part of composite PK.
Then you probably have 2 options:
1.Using cursor. (Which is normally bad practice in terms of performance)
2.Using subquery.
In addition, just use ‘CASE’ to decide the action base on the ‘Status’ is ‘in’ or ‘out’.
Hopefully this does bit help.
January 31, 2008 at 11:03 pm
If u hav an inique ID or primary key for the table u can Use this Logic
that means
PID|Product|STATUS|BEGIN|TRX|END
1 A IN 0 5 0
2 A IN 0 1 0
3 A OUT 0 2 0
ANd i this is not the right Syntaxt , So check the syntaxt
i just write Out the Logic ...Ok ALl the Best
Craete Proc Sample
AS
Begin
Declare Cur cursor for
select pid , product , status , begin ,trx , end from table
Declare @ED
Declare @ED2
Open Cur
fetch next from Cur into @pid ,@pr , @stat , @beg ,@trx , @end
@ED = 0
while @@fetch_status =0
if(@stat = 'IN')
@ED2 = @ED+ @trx
else
@ED2 = @ED - @trx
update #table
set BEGIN = @ED ,END =@ED2
where PID = @pid
@ED = @ED2
fetch into @pid ,@pr , @stat , @beg ,@trx , @end
Begin
🙂
February 1, 2008 at 12:33 am
i'm already add the index for the table like
Product|STATUS|BEGIN|TRX|END|Index
A IN 0 5 0 1
A IN 0 1 0 2
A OUT 0 2 0 3
B IN 0 10 0 1
B IN 0 5 0 2
so the result like
Product|STATUS|BEGIN|TRX|END|index
A IN 0 5 5 1
A IN 5 1 6 2
A OUT 6 2 4 3
B IN 0 10 10 1
B IN 10 5 15 2
so what should i gonna do..cursor?i've never use it before..can you help me?thanks alot
February 1, 2008 at 12:53 am
Hi
A cursor or a while loop is the simplest choice here. I would prefer the while loop.
"Keep Trying"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply