Getting the difference from the same filed of two lines

ralfs

Hi,

I'm trying to get the difference of two values of the same field in two different line. In this specific case of the free usable capacity of an storage array. We checking that value every 6 hours so I come up with this solution:

SELECT TOP 1 
   ((SA2.CapacityStatistics.CapacityUserFree - SA.CapacityStatistics.CapacityUserFree)/1024/1024/1024) 
   AS DIFFER
FROM Orion.SRM.StorageArrays SA
JOIN Orion.SRM.StorageArrays SA2 on SA2.DisplayName = SA.DisplayName
WHERE SA.DisplayName = 'pgc2gl-sntap01' 
   AND SA2.CapacityStatistics.ObservationTimestamp > 
       (AddHour(-8,SA.CapacityStatistics.ObservationTimestamp))
   AND SA2.CapacityStatistics.ObservationTimestamp < 
        SA.CapacityStatistics.ObservationTimestamp
ORDER BY SA.CapacityStatistics.ObservationTimestamp DESC 

Does anybody has a better way how to do that?