June 22, 2011 at 5:33 am
Hi there,
I'm trying NOT to use a cursor/while loop, but getting stuck. I am very close, but running into problems I think with the fact that my update statement updates a field with a formula which needs the value from a previously updated field. I'm thinking the problem is that the change hasn't been committed yet on the other record for which I need the updated value. I guess it's using an implicit transaction, since I haven't specified.
Any suggestions?
Here is the code, which is in a sproc.:
--BEGIN ------------ Rule 1: Update Calls, matching transactions to calls --------------------
/*
FYI, I'm attempting to map a series of calls to a transaction, with the tx.qty dictating how many matches are allowable. then there can be multiple tx's for the same match criteria, and calls can only get mapped to one tx each... below i'm attempting to put the tx's in a temp table with a kind of roadmap for the mapping (the From and To fields) to say that the first, say, 2 calls found would get mapped to tx 2356 because when calls are numbered within each criteria, the call with a call number (rownumber) BETWEEN "From" (1) and "To" (2) in my temp table would claim the match. Then The next tx that have the same criteria might have a qty of 3, so it would claim any calls between 3 and 5...
So I want to see:
TX Qty Row# From To
1234 1 1 1 1
2367 2 2 2 3
5849 1 3 4 4
4827 3 4 5 7
Incidentally, the code below works to return the first two rows, but when it comes to the 3rd and 4th row, it still is calculating based on the first row instead of the previous row.
*/
--a list of matches, numbered per match, with a match qty
Select OrderNumber,
EQPartNumber,
ABS(tx.Qty) as Qty,
tx.[Date],
SPACE(15) as MatchStatus,
0 as NumberFrom,
0 as NumberTo,
Row_Number() OVER(Partition By OrderNumber, EQPartNumber Order By OrderNumber, EQPartNumber, tx.[Date]) as recordnumber
Into #CallMatches
From dbo.Transactions tx
inner join dbo.Calls on tx.SR = calls.OrderNumber
And tx.Item = calls.EQPartNumber
order by OrderNumber, EQPartNumber, tx.[Date]
---ONE WAY I TRIED IT......------
Update #CallMatches
Set NumberFrom = m.NumberTo + 1,
NumberTo = m.NumberTo + ABS(tx.Qty)
From #CallMatches tx
inner join #CallMatch2 m on m.OrderNumber = tx.OrderNumber
and m.EQPartNumber = tx.EQPartNumber
and m.recordnumber = (tx.recordNumber - 1)
where tx.recordNumber > 1
And tx.NumberFrom = 0
----- ANOTHER WAY I TRIED IT......-----
Update #CallMatches
Set NumberFrom = ((Select m.NumberTo from #CallMatches m where m.OrderNumber = tx.OrderNumber
and m.EQPartNumber = tx.EQPartNumber
and m.recordnumber = (tx.recordNumber - 1)) + 1),
NumberTo = (((Select m.NumberTo from #CallMatches m where m.OrderNumber = tx.OrderNumber
and m.EQPartNumber = tx.EQPartNumber
and m.recordnumber = (tx.recordNumber - 1))) + ABS(tx.Qty))
From #CallMatches tx
where recordNumber > 1
And NumberFrom = 0
select * from #CallMatches
----------------------------------------------------------------------------
June 22, 2011 at 11:59 am
Please read and follow the advice given in the first article referenced in my signature on how to post SQL code questions.
We'll need table def and sample data ina ready to use format.
Even though there are a few people around that are able to analyze and optimize code just as it is, most of us (including me) prefer to test our solution.
So, please help us help you.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply