sql server - SQL Find difference between previous and current row

Question

I am trying to find the difference between the current row and the previous row. However, I am getting the following error message:

The multi-part identifier "tableName" could not be bound.

Not sure how to fix the error.

Thanks!

Output should look like the following:

columnOfNumbers     Difference
      1               NULL
      2               1
      3               1
      10              7
      12              2
      ....            ....

Code:

USE DATABASE;

WITH CTE AS 
(SELECT 
    ROW_NUMBER() OVER (PARTITION BY tableName ORDER BY columnOfNumbers) ROW,
    columnOfNumbers
    FROM tableName)
SELECT
    a.columnOfNumbers
FROM
    CTE a
    LEFT JOIN CTE b
    ON a.columnOfNumbers = b.columnOfNumbers AND a.ROW = b.ROW + 1

Answer 1

If you in SQL Server 2012+ You can use LAG.

 SELECT columnOfNumbers
       ,columnOfNumbers - LAG(columnOfNumbers, 1) OVER (ORDER BY columnOfNumbers)
   FROM tableName

Note: The optional third parameter of LAG is:

default

The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned. default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression.