WHERE (((S1.SiteID)=[S2].[SiteID]) AND ((S2.SiteHistoryAsOfDate)=(SELECT MAX([SiteHistoryAsOfDate]) FROM SiteHistory AS S3

WHERE S3.SiteID = S1.SiteID

AND S3.SiteHistoryID < S1.SiteHistoryID)))

ORDER BY S1.SiteID, S1.SiteHistoryAsOfDate;

What that's doing is pulling up two copies of the table side-by-side, then matching rows in the two on the SiteID and the "previous" record based on SiteHistoryAsOfDate. "Find me the row in the second copy that has the same SiteID and the largest date that is less than the date in the first copy."

You can do that by using two copies of your table and linking them on SiteID and either the previous SiteHistoryID or SiteHistoryAsOfDate. The reason I ask about those two columns is your data is a bit confusing. For SiteID 700, you have:

I have data that I want to analyze in terms of daily net change. Data reflecting the current volume is collected on a daily basis from approx. 75 locations. The data provider is unable to collect daily changes but needs this value computed by subtracting the previous reading from the last reading. Some locations have frequently changing daily levels while other have only occasional levels.

I am able to produce a short-term history (7 days) using a crosstab to flatten the date. I use the crosstab as a subquery in which each column is subtracted from the column that represents its predecessor.

The situation is similar to electrical power data where meter readings are periodically collected and consumption is calculated by subtracting the previous reading from the current reading. My data is slightly different because the values may indicate decreases as well as increases but this shouldn't affect the calculation.

Setting up a crosstab to calculate over the long term (as opposed to just the past 7 days) is not really practical. So I am looking for an efficient way to calculate periodic changes for a large number of periods.