Calculating moving difference

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Can some one please help me on this?  I got 2 records by individual
with some fileds, one associates with the min date and the other one
assocaites with max date.  So lay out looks as follows:

Key_ID | Dates_Min_Max  Avg_Weight
1234        1/2/2004            12
1234        1/2/2006            24

I need to get the difference change between the weights for individual
ids that is group by Key_ID and find the percent change.  Some thing
like this: (MAx_Row -  Previos Row)/Previous Row * 100.

How to do this?  How do I get the previos row between 2 records if I
order by the date?  Any suggestions?  Thanks

Re: Calculating moving difference

Sumon wrote:
Quoted text here. Click to load it

Whenever you compare values on two rows, you usually have to do a JOIN
to get them both on the same row of the result set, so you can compare
the values.

SELECT ((t2.avg_weight - t1.avg_weight) / t1.avg_weight) * 100
   AS pct_difference
FROM tablename AS t1 JOIN tablename AS t2
   ON t1.key_id = t2.key_id AND t1.dates_min_max < t2.dates_min_max;

Bill K.

Site Timeline