Link to home
Create AccountLog in
Avatar of MadIce

asked on

While running query determine if last rows column value has changed

Hope this make sense. Within a Stored Procedure, I have a large query pulling from several tables with lots of calcs for fields. One of the calcs I need I first need to check if  the items name has changed. For example: if ColA value is the same than colD = ColB + ColD else ColD = ColE. If I can't add it to my current Query, I'll just stick the results in a table and loop thru it. But not sure how to add it with my main query. Thanks in advance.
Avatar of Alpha Au
Alpha Au
Flag of Hong Kong image

Do you need transaction?

within a transaction, the row will hold a lock and cannot be updated by other.


query 1

begin tran
update tableA set ColA = 1 where ColB = 'ABC'

query 2
select ColA from tableA where ColB = 'ABC'

if you run query1 1st, then query 2
the query 2 will not response,
query 2 can select the value only after you perform a "commit tran" in query 1

Avatar of Sharath S
Can you provide some sample data and expected result?
Select *, Case ColomnName
 when 'Condition' then 'Value'
 when 'Condition' then 'Value'
 when 'Condition' then 'Value'...
End as ColonmName1 from TableName where ColomnName='Value'
Avatar of MadIce


Here would be example query I have now

Select ItemName, sum(SerialQty) as SerialQty, sum(LotQty) as LotQty, Condition
From TableA
Where AreaZone = 2

So I would get results like the following:
ARAR     200      50     A
ARAR     100      10     B
BBCC      50        20     A
BBCC     70         90     C

what I need to do is add another col that accumulate SerialQty and LotQty until the ItemName Changes.
ARAR     200      50     A  250
ARAR     100      10     B  360
BBCC      50        20     A  70
BBCC     70         90     C  230

Maybe I can add afunction within the Query but I think I need an Id field to see what last record was. That's why I might need to add the results to a table and then do an update query. But I was hoping to avoid that and add it to my current query.

Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of MadIce


Sorry for the delay. I thought it was working quite the way I needed but turns out it does. the only difference is I did not use a temp table. I wanted to add to my main query which I was able to do with your example. Thank you so much.