How to calculate time of adjacent data subtraction-CodePudding

How to calculate time of adjacent data presupposed, 29 RM_value column equals 29 column x_value minus 28 column x_value, 28 RM_value column equals 28 same column x_value x_value minus 27 column, a great god please help

CodePudding user response:

The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T (Effective_Date DATETIME, [X_value] a decimal (18, 8)) 
Insert # T 
Select N '2019-08-27 01:00:20. 000, 1.00 union all 
Select N '2019-08-29 09:18:44. 000, 2.50 union all 
Select N '2019-08-29 09:20:33. 000, 3.00 
Go 
- the end of the test data 
; WITH cte AS (
Select *, ROW_NUMBER () OVER (ORDER BY Effective_Date) rn from # T 
) 
SELECT a. *, a.X _value - ISNULL (b.X _value, 0) AS RM_value FROM cte a LEFT JOIN cte ON a.r n=b b.r n + 1

CodePudding user response:

Brother????????

CodePudding user response:

refer to the second floor weixin_44405193 response:

brother??

if help to you, and remember,

CodePudding user response:

The

reference 1/f, February 16 response:

 
 - test dataIf not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T (Effective_Date DATETIME, [X_value] a decimal (18, 8)) 
Insert # T 
Select N '2019-08-27 01:00:20. 000, 1.00 union all 
Select N '2019-08-29 09:18:44. 000, 2.50 union all 
Select N '2019-08-29 09:20:33. 000, 3.00 
Go 
- the end of the test data 
; WITH cte AS (
Select *, ROW_NUMBER () OVER (ORDER BY Effective_Date) rn from # T 
) 
SELECT a. *, a.X _value - ISNULL (b.X _value, 0) AS RM_value FROM cte a LEFT JOIN cte ON a.r n=b b.r n + 1

yes??????

CodePudding user response:

refer to February 16 reply: 3/f

Quote: refer to the second floor weixin_44405193 response:
brother??

if help to you, and remember,

how knot

CodePudding user response:

The

reference 1/f, February 16 response:

 
 - test dataIf not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T (Effective_Date DATETIME, [X_value] a decimal (18, 8)) 
Insert # T 
Select N '2019-08-27 01:00:20. 000, 1.00 union all 
Select N '2019-08-29 09:18:44. 000, 2.50 union all 
Select N '2019-08-29 09:20:33. 000, 3.00 
Go 
- the end of the test data 
; WITH cte AS (
Select *, ROW_NUMBER () OVER (ORDER BY Effective_Date) rn from # T 
) 
SELECT a. *, a.X _value - ISNULL (b.X _value, 0) AS RM_value FROM cte a LEFT JOIN cte ON a.r n=b b.r n + 1

Borrow a floor, so a little bit more simple

SELECT *, X_value - ISNULL (LAG (X_value) OVER (ORDER BY Effective_Date), 0) FROM # T

CodePudding user response:

refer to 6th floor nature response:

Quote: reference 1/f, February 16 response:

The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
The Create table # T (Effective_Date DATETIME, [X_value] a decimal (18, 8)) 
Insert # T 
Select N '2019-08-27 01:00:20. 000, 1.00 union all 
Select N '2019-08-29 09:18:44. 000, 2.50 union all 
Select N '2019-08-29 09:20:33. 000, 3.00 
Go 
- the end of the test data 
; WITH cte AS (
Select *, ROW_NUMBER () OVER (ORDER BY Effective_Date) rn from # T 
) 
SELECT a. *, a.X _value - ISNULL (b.X _value, 0) AS RM_value FROM cte a LEFT JOIN cte ON a.r n=b b.r n + 1

Borrow a floor, so a little bit more simple

SELECT *, X_value - ISNULL (LAG (X_value) OVER (ORDER BY Effective_Date), 0) FROM # T

Not LAG function a lot of support, and should be a new database and function

CodePudding user response:

The difference between the adjacent data, look at here, using cte expression