Calculate the difference in meter between the current value and the last value recorded

Question

I would like to create a query to calculate the difference (in meters) between the current value and the last value recorded.

Follow my SpareParts table:

 ----------------------------
|Serial | PartCode | Meter  | 
 ----------------------------
|   1   |    A     | 1000   |
|   1   |    A     | 2000   |
|   1   |    A     | 2500   |
|   1   |    A     | 4000   |
|   2   |    A     | 1000   |
|   2   |    A     | 1400   |
|   2   |    A     | 2900   |
|   1   |    B     | 5000   |
|   1   |    B     | 9000   |
|   1   |    B     | 14000  |
 ----------------------------

And I would like a result like this :

 -----------------------------------
|Serial | PartCode | Meter  | Diff  |
 -----------------------------------
|   1   |    A     | 1000   |  0    |   
|   1   |    A     | 2000   | 1000  |
|   1   |    A     | 2500   | 500   |
|   1   |    A     | 4000   | 1500  |
|   2   |    A     | 1000   |  0    |
|   2   |    A     | 1400   | 400   |
|   2   |    A     | 2900   | 1500  |
|   1   |    B     | 5000   |  0    |
|   1   |    B     | 9000   | 4000  |
|   1   |    B     | 14000  | 5000  |
 -----------------------------------
Diff = Current Meter - Last Meter

Could you help me with this question?

Thanks,

You should check out LAG. https://msdn.microsoft.com/en-us/library/hh231256.aspx — Sean Lange, Jan 05 '16 at 15:39
Possible duplicate of [How to get difference between two rows for a column field?](http://stackoverflow.com/questions/634568/how-to-get-difference-between-two-rows-for-a-column-field) — Tab Alleman, Jan 05 '16 at 15:47
By the way, if your table doesn't have a column that you can ORDER BY, then you cannot reliably get your desired result by any means. — Tab Alleman, Jan 05 '16 at 15:50

score 1 · Answer 1 · answered Jan 05 '16 at 15:51

This might work for you:

SELECT Serial, PartCode, Meter, 
   LAG(Meter, 1,0) OVER (PARTITION BY Serial, PartCode ORDER BY Meter DESC) AS Diff
FROM SpareParts;

Since you are using SQL Server 2012. This divides the table into sets of rows (partitions) defined by serial number and part code, orders each of those sets by the meter value and then calculates the difference between the 1-back rows.

Calculate the difference in meter between the current value and the last value recorded

1 Answers1