Calculating moving difference

Sumon

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

Jun 18 '06 #1

Bill Karwin

Sumon wrote:

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.

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;

Regards,
Bill K.

Jun 19 '06 #2