Calculate annual differences and changes through time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Would like to display elevation changes for instruments that are surveyed on
an annual basis. Also, would like to display total change through time.

InstID Survey Date Elevation
4 05/09/2001 176.245
4 07/11/2002 176.166
4 04/15/2003 176.170

I modified an SQL statement from a previous reply by Jamie Collins on
10/28/2004 as follows (table name is Test):

SELECT T2.InstID, T2.SurveyDate AS Expr1, [T2].[Elevation]-IIf((SELECT
T1.Elevation FROM Test T1 WHERE T1.SurveyDate = (SELECT MAX(SurveyDate) FROM
Test WHERE SurveyDate<T2.SurveyDate)) Is Null,0,(SELECT T1.Elevation FROM
Test T1 WHERE T1.SurveyDate = (SELECT MAX(SurveyDate) FROM
Test WHERE SurveyDate<T2.SurveyDate))) AS new_amount
FROM Test AS T2
ORDER BY T2.SurveyDate;

Results
InstID Survey Date new_amount
4 05/09/2001 176.245
4 07/11/2002 -0.079
4 04/15/2003 0.004

This is very close to what I would like to accomplish. How do I modify the
SQL to calculate the new_amount for the first Survey Date to be NULL or 0 so
that I may sum the new_amount for the period of record?

Thanks in advance for any assistance,
Chris
 
Try this:

SELECT T2.InstID, T2.SurveyDate AS Expr1, [T2].[Elevation]-IIf((SELECT
T1.Elevation FROM Test T1 WHERE T1.SurveyDate = (SELECT MAX(SurveyDate) FROM
Test WHERE SurveyDate<T2.SurveyDate)) Is Null,0,(SELECT T1.Elevation FROM
Test T1 WHERE T1.SurveyDate = Nz((SELECT MAX(SurveyDate) FROM
Test WHERE SurveyDate<T2.SurveyDate),0))) AS new_amount
FROM Test AS T2
ORDER BY T2.SurveyDate;
 
Back
Top