title: MSSQL Calculate Date Difference in same column
body: <p>My data is as follows:</p>
  <pre>
  ID  DATE
  1   2014-02-01 09:00:00.00
  1   2014-03-01 11:00:00.00
  1   2014-01-01 11:00:00.00
  2   2010-12-01 05:00:00.00
  2   2011-10-01 03:30:00.00
  2   2012-09-01 02:40:00.00
  </pre>
  <p>I need to calculate the date difference(day or hours difference is fine) between each row. The first row can be 0. I have tried:</p>
  <pre><code>date - coalesce(lag(date) over(order by id, date) as day_difference
  
  daydiff(date - coalesce(lag(date) over(order by id, date)) as day_difference
  </code></pre>
  <p>both variants are not working(gives incorrect syntax error).</p>
  <p>Please help.</p>