SQL calculation between dates

Question

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 will reveal the difference in Market Valuebetween 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 to write this function?

Answer 1

In SQL Server 2012+, the most efficient and easiest way is to use the built-in function LEAD.

SELECT
    [Date]
    ,[Market Value]
    ,LEAD([Market Value]) OVER (ORDER BY [Date] DESC) - [Market Value] AS Delta
FROM YourTable
;

LEADreturns the value of the next line, as indicated in its sentence ORDER BY.

All other methods that join the table are less effective.

Answer 2

For SQL Server below 2012, you can try the following:

with cte as
(SELECT
   ROW_NUMBER() OVER (ORDER BY [Date] DESC) row,
   [Date],
   [Market Value]
 FROM [YourTable])
SELECT 
   a.[Date] ,
   b.[Market Value] - ISNULL(a.[Market Value],0) AS Delta
FROM
   cte a
   LEFT  JOIN cte b
   on a.row = b.row+1

: SQL SQL-Server 2012 LEAD.

Answer 3

If you have a fixed date, you can do

select t1.date, t1.market_value, t1.market_value-t2.market_value from data_table t1 left join data_table t2 on t1.date-1=t2.date

If you do not have a fixed date and want to calculate the difference between Monday and Friday, you can use rownum, for example, like this

select t1.date, t1.market_value, t1.market_value-t2.market_value from (select rownum, date,market_value from data_table) t1 left join (select rownum, date,market_value from data_table) t2 on t1.rownum-1=t2.rownum

Answer 4

CREATE PROCEDURE UPDATE_DELTA 
@START_DATE DATETIME, 
@END_DATE DATETIME
AS BEGIN

    UPDATE T
    SET DELTA = MARKET_VALUE - (SELECT MARKET_VALUE 
                                FROM YOURTABLE 
                                WHERE [DATE] = T.[DATE] - 1)
    FROM YOURTABLE T
    WHERE [DATE] BETWEEN @START_DATE AND @END_DATE

END

And then execute:

EXEC UPDATE_DELTA '2016-09-05', '2016-09-08'

This works as long as you have sequenced dates.

Answer 5

Add a column and update it as follows:

UPDATE t SET t.Delta = t.Market_Value-t2.Market_Value
FROM yourtable t
INNER JOIN yourtable t2 ON DATEADD(DD,-1,t.Date) = t2.Date