Subtracting values from two consecutive rows

A friend needed help with subtracting the column from two consecutive rows and since I was feeling bored, being home sick in bed, I wrote some code for the problem between the sheets.

1) Create a table to try it out. Don’t use this unless you are sure of what it will be doing. USE tempdb GO /****** Object: Table [dbo].[atable] Script Date: 02/01/2010 20:40:02 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[atable]’) AND type in (N’U’)) DROP TABLE [dbo].[atable] GO   –create a table to fill with data that makes us recognize the problem CREATE TABLE atable ( aindex INT, atimestamp SMALLDATETIME, acounter_c INT, acounter_d INT ) go  

2) Fill it with some data (i.e. performance counters or something of that matter) INSERT atable VALUES ( 695 ,’2010-01-29 11:05:00′, 60299 , 23901 ) INSERT atable VALUES ( 694 ,’2010-01-29 11:04:00′, 60207 , 23870 ) INSERT atable VALUES ( 693 ,’2010-01-29 11:03:00′, 60112 , 23839 ) INSERT atable VALUES ( 692 ,’2010-01-29 11:02:00′, 60021 , 23809 ) INSERT atable VALUES ( 691 ,’2010-01-29 11:01:00′,  59930 , 23778 ) INSERT atable VALUES ( 690 ,’2010-01-29 11:00:00′,  59839 , 23748 ) INSERT atable VALUES ( 689 ,’2010-01-29 10:59:00′, 59747 , 23717 ) INSERT atable VALUES ( 688 ,’2010-01-29 10:58:00′,  59656 , 23687 )

3) Show whats inside the table after insert  SELECT * FROM atable ORDER BY aindex DESC /* aindex, atimestamp, acounter_c, acounter_d 695 2010-01-29 11:05:00 60299  23901 694 2010-01-29 11:04:00 60207  23870 693 2010-01-29 11:03:00 60112  23839 692 2010-01-29 11:02:00 60021  23809 691 2010-01-29 11:01:00 59930  23778 690 2010-01-29 11:00:00 59839  23748 689 2010-01-29 10:59:00 59747  23717 688 2010-01-29 10:58:00 59656  23687 */

4) Now – make the magic happen. The secret is in the LEFT JOIN joining columns.  Using a left join since the last row will not get a match. — You would need to decide what lowest date you want to use for the last row. — that will not show in the finished query below, but in this testversion I want to point that out.   SELECT t1.aindex AS YourIndexColumn , t1.atimestamp AS YourTimestamp , ISNULL(t2.aindex,0) AS SelfJoinWithRowBeneath , ISNULL(t2.atimestamp,’1999-01-01 00:00:00′) AS YourDateInRowBeneath , t1.acounter_c – ISNULL(t2.acounter_c,0) AS Diff_c , t1.acounter_d – ISNULL(t2.acounter_d,0) AS Diff_d , t1.acounter_c AS counter_c_Table1 , ISNULL(t2.acounter_c,0) AS counter_c_Table2 , t1.acounter_d AS counter_d_Table1 , ISNULL(t2.acounter_d,0) AS counter_d_Table2 FROM atable t1  LEFT  JOIN atable t2 ON t1.aindex = t2.aindex +1 ORDER BY t1.aindex DESC /* for these: 689 2010-01-29 10:59:00 59747  23717 688 2010-01-29 10:58:00 59656  23687 I would want the answer 2010-01-29 10:59:00 91 30 */

5) Finalize the deployable code SELECT  t1.aindex AS YourIndexColumn , t1.atimestamp AS YourTimestamp , t1.acounter_c AS Counter_c , CASE ISNULL(t2.acounter_c,0)   WHEN 0 THEN 0  ELSE t1.acounter_c – ISNULL(t2.acounter_c,0) END AS Diff_c , t1.acounter_d AS Counter_d , CASE ISNULL(t2.acounter_d,0)  WHEN 0 THEN 0  ELSE t1.acounter_d – ISNULL(t2.acounter_d,0) END AS Diff_d FROM atable t1 LEFT JOIN atable t2 ON t1.aindex = t2.aindex +1 — <—

The magic +1 joins the two rows with each other since the index column is in consecutive indexes ORDER BY t1.aindex DESC –Showing the result, that is what my friend wants, hopefully. /* YourIndexColumn YourTimeStamp Counter_c Diff_c Counter_d Diff_d 695 2010-01-29 11:05:00 60299  92 23901 31 694 2010-01-29 11:04:00 60207  95 23870 31 693 2010-01-29 11:03:00 60112  91 23839 30 692 2010-01-29 11:02:00 60021  91 23809 31 691 2010-01-29 11:01:00 59930  91 23778 30 690 2010-01-29 11:00:00 59839  92 23748 31 689 2010-01-29 10:59:00 59747  91 23717 30 — <– That is the diff numbers I wanted. 688 2010-01-29 10:58:00 59656  0  23687  0 — <– No row to compare, leave the diff = 0 */