Q:

SQL Calculation between dates

I have the following two columns.
    Date     |  Market Value
------------------------------
 2016-09-08  |      100
 2016-09-07  |      130
 2016-09-06  |      140
 2016-09-05  |      180

I want to add a column that calulcate the difference in Market Value between the two dates.
    Date     |  Market Value   |  Delta 
------------------------------------------ 
 2016-09-08  |     100         | -30
 2016-09-07  |     130         | -10
 2016-09-06  |     140         | -40
 2016-09-05  |     180         | 

.
100 (2016-09-08) minus 130 (2016-09-07) = -30

How do I write that function?

A:

In SQL Server 2012+ the most efficient and simple way is to use the built-in LEAD function.
SELECT
    [Date]
    ,[Market Value]
    ,LEAD([Market Value]) OVER (ORDER BY [Date] DESC) - [Market Value] AS Delta
FROM YourTable
;

LEAD returns the value of the next row as specified by its ORDER BY clause.
All other methods that self-join the table are less efficient.

