title: Subtracting Previous Value by Prior Group - SQL Server
body: <p>This question has been asked previously and been well answered for MySQL specifically, however I'd very much appreciate your guys' help in translating this for SQL Server. The prior posts are closed and I didn't want to reopen them as they're quite old.</p>
  
  <p>Below is the dataset to query, and the required output of the query solution. Below that is the excellent MySql solution posted here (<a href="https://stackoverflow.com/questions/13734976/sql-best-way-to-subtract-a-value-of-the-previous-row-in-the-query">SQL best way to subtract a value of the previous row in the query?</a>). My question is how to accomplish the same result with variables in SQL Server. I apologize if this is a novice question, but I'm new to variables in SQL Server and have failed in my attempts to translate to SQL Server based on MSDN documentation on variables in SQL Server.</p>
  
  <p>Thank you for your help!</p>
  
  <pre><code>select
        EL.SN,
        EL.Date,
        EL.EL.Value,
        if( @lastSN = EL.SN, EL.Value - @lastValue, 0000.00 ) as Consumption,
        @lastSN := EL.SN,
        @lastValue := EL.Value
     from
        EnergyLog EL,
        ( select @lastSN := 0,
                 @lastValue := 0 ) SQLVars
     order by
        EL.SN,
        EL.Date
  </code></pre>
  
  <p>
      </p>
  
  <p>I need to have the consumption value base on previous one by SN number.
  This is my data:</p>
  
  <p>TABLE EnergyLog</p>
  
  <pre><code>SN     Date                 Value
  2380   2012-10-30 00:15:51  21.01
  2380   2012-10-31 00:31:03  22.04
  2380   2012-11-01 00:16:02  22.65
  2380   2012-11-02 00:15:32  23.11
  20100  2012-10-30 00:15:38  35.21
  20100  2012-10-31 00:15:48  37.07
  20100  2012-11-01 00:15:49  38.17
  20100  2012-11-02 00:15:19  38.97
  20103  2012-10-30 10:27:34  57.98
  20103  2012-10-31 12:24:42  60.83
  </code></pre>
  
  <p>This is the result I need:</p>
  
  <pre><code>SN      Date                 Value  consumption
  2380    2012-10-30 00:15:51  21.01  0
  2380    2012-10-31 00:31:03  22.04  1.03
  2380    2012-11-01 00:16:02  22.65  0.61
  2380    2012-11-02 00:15:32  23.11  0.46
  20100   2012-10-30 00:15:38  35.21  0
  20100   2012-10-31 00:15:48  37.07  1.86
  20100   2012-11-01 00:15:49  38.17  1.1
  20100   2012-11-02 00:15:19  38.97  0.8
  20103   2012-10-30 10:27:34  57.98  0
  20103   2012-10-31 12:24:42  60.83  2.85
  </code></pre>