sql calculate delta between the column value for one day and the previous day

Question

I have a query like this:

;WITH A AS (SELECT * FROM T1 where T1.targetDate=@inputdate), 
      B AS (SELECT A.*, T2.SId, T2.Type, T2.Value
            FROM A 
            INNER JOIN T2 ON A.SId = T2.SId )
SELECT A.*, B.Type, B.Value 
FROM B

My question is, instead of getting the Value for @inputdate, how to get the delta of Value between @inputdate and the previous day (DATEADD(day, -1, @inputdate ))?

Edited:

Sorry for not being clear, the 'Value' is of type int. For example, if @inputdate = '20130708', the Value for '20130708' is 30, and the 'Value' for previous day '20130707' is 20, so it should return (30 - 20) which is 10.

**Please add Schema**. What are the columns that have 20 and 30? you are using 2 different tables, `CTE A` uses table `T1`, `CTE B` uses table `T2`. — Luis LL, Jul 09 '13 at 10:21

Luis LL · Answer 1 · 2013-07-09T10:22:22.427

0

Something like this, and assuming that Value is DATE format

;WITH A AS (SELECT * FROM T1 where T1.targetDate=@inputdate), 
      B AS (SELECT A.*, T2.SId, T2.Type, T2.Value
            FROM A 
            INNER JOIN T2 on A.SId = T2.SId )
SELECT A.*, T2.Type, T2.Value, DATEDIFF(DAY, b.Value, DATEDADD(DAY, -1,@InputDate)) AS Delta
FROM B

edited Jul 09 '13 at 10:22

answered Jul 09 '13 at 10:07

Luis LL

2,912
2
19
21

Edited, I forgot to add `DATEADD(day, -1 @InpuDate)` – Luis LL Jul 09 '13 at 10:10
Sorry for not being clear, the 'Value' is of type int. I've edited the question accordingly. – wd113 Jul 09 '13 at 10:16

score 0 · Answer 2 · edited Mar 25 '19 at 14:45

let's say you have a stock prices table: which has the symbol, date and closing prices etc, you could use something like this:

select symb, ret_dt, close, (close-(lead(close,1) over (partition by symb order by ret_dt desc,close)))as difference, (lead(close,1) over 
(partition by symb order by ret_dt desc,close)) as lead
from stocks.nyse2010;

Note: here ret_dt is the date, close is the closing price and I have added an additional lead column for representational purposes.

sql calculate delta between the column value for one day and the previous day

2 Answers2