title: Difference between consecutive rows in SQL, inclusive of the first and last row?
body: <p>I'm trying to get the successive differences of rows of data in SQL, including differences between first and last row and <code>0</code>.</p>
  
  <p>I have two tables that look like this</p>
  
  <pre><code>+------------+-------+     +------------+-------+
  | Date       | Name  |     | Date       | Value |
  +------------+-------+     +------------+-------+
  | 2019-10-10 | AAA   |     | 2019-10-11 | 100   |
  | 2019-10-11 | BBB   |     | 2019-10-12 | 150   |
  | 2019-10-12 | CCC   |     | 2019-10-14 | 300   |
  | 2019-10-13 | DDD   |     +------------+-------+
  | 2019-10-14 | EEE   |
  | 2019-10-15 | FFF   |
  +------------+-------+
  </code></pre>
  
  <p>The end result I'm looking for is</p>
  
  <pre><code>+------------+-------+-------+---------------+------------+
  | Date       | Name  | Value | PreviousValue | Difference |
  +------------+-------+-------+---------------+------------+
  | 2019-10-11 | BBB   | 100   | 0             | 100        |
  | 2019-10-12 | CCC   | 150   | 100           | 50         |
  | 2019-10-14 | EEE   | 300   | 150           | 150        |
  | 2019-10-15 | FFF   | 0     | 300           | -300       |
  +------------+-------+-------+---------------+------------+
  </code></pre>
  
  <p>I can get the first row by using <code>LAG</code>, but I don't quite know how to get the last row at the same time.</p>
  
  <pre><code>SELECT
      d.[Date],
      d.[Name],
      v.[Value],
      [PreviousValue] = COALESCE(LAG(v.[Value) OVER (ORDER BY v.[Date]), 0)
      [PreviousLossAmount] = v.[Value] - COALESCE(LAG(v.[Value) OVER (ORDER BY v.[Date]), 0)
  FROM
      [Dates] d
  LEFT JOIN
      [Values] v
  ON
      d.[Date] = v.[Date]
  </code></pre>
  
  <p>Note that in reality, my tables are more complex, and I'd need to group and partition by multiple columns.</p>