Difference between the values of a column in 2 different rows

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

JPG Old Hand Points: 346 More actions · Answer 1

I cannot think of a way to do that without a Cursor

peterhe SSChampion Points: 11469 More actions · Answer 2

The following is the pseudo code:

DECLARE @TmpTable (RID int identity, stockid, time, Amt, Delta)

INSERT @TmpTable (Stockid,time,Amt, Delta)

SELECT Stockid,time,Amt,0

FROM YourTable

ORDER BY Stockid, time

UPDATE A SET Delta=b.Amt- a.Amt -- If you need abs change, add ABS()

FROM @TmpTable a, @TmpTable b

WHERE a.Stockid=b.Stockid and a.rid=b.rid-1

SELECT StockID, CAST(SUM(Delta)/(COUNT(*)-1) as numeric(10.2)) AS AvgAmt

FROM @TmpTable

GROUP BY StockID

HAVING COUNT(*)>1

JPG Old Hand Points: 346 More actions · Answer 3

I guess you could do it without a Cursor

Did not see the Time part

bhavna dalal SSC Veteran Points: 235 More actions · Answer 4

I tried this but mysql does'nt allow and update from statement,so this does'nt work