SQL Sever 2012
Raw Data
ID VAL Time
+---+----+---------------------+
| 2 | 1 | 2015-05-09 12:54:39 |
| 3 | 10 | 2015-05-09 12:54:39 |
| 2 | 1 | 2015-05-09 12:56:39 |
| 3 | 10 | 2015-05-09 12:56:39 |
| 2 | 5 | 2015-05-09 13:48:30 |
| 3 | 16 | 2015-05-09 13:48:30 |
| 2 | 7 | 2015-05-09 15:01:09 |
| 3 | 20 | 2015-05-09 15:01:09 |
+---+----+---------------------+
I have a table where VAL is increasing forever in time. I want to manipulate the data to show how much VAL is increasing for each ID over time. So Val at Time2 - Val at Time1
Ideal Result:
ID VALI Time
+---+----+---------------------+
| 2 | 0 | 2015-05-09 12:56:39 |
| 3 | 0 | 2015-05-09 12:56:39 |
| 2 | 4 | 2015-05-09 13:48:30 |
| 3 | 6 | 2015-05-09 13:48:30 |
| 2 | 2 | 2015-05-09 15:01:09 |
| 3 | 4 | 2015-05-09 15:01:09 |
+---+----+---------------------+
Code so far:
select
t1.Time,t1.[ID],t2.[VAL]-t1.[VAL] AS [ValI]
from #tempTable t1
inner join #tempTable t2 ON t1.[ID]=t2.[ID]
AND t1.[Time]<t2.[Time]
I need to calculate the difference between the current timestamp and ONLY the Time right before current timestamp not all timestamps before the current timestamp. As of now I get a lot of repeating values when VAL did not change.
You can use this.
DECLARE @MyTable TABLE (ID INT, VAL INT, [Time] DATETIME)
INSERT INTO @MyTable VALUES
(2, 1 ,'2015-05-09 12:54:39'),
(3, 10 ,'2015-05-09 12:54:39'),
(2, 1 ,'2015-05-09 12:56:39'),
(3, 10 ,'2015-05-09 12:56:39'),
(2, 5 ,'2015-05-09 13:48:30'),
(3, 16 ,'2015-05-09 13:48:30'),
(2, 7 ,'2015-05-09 15:01:09'),
(3, 20 ,'2015-05-09 15:01:09')
;WITH CTE AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [Time]) RN FROM @MyTable
)
SELECT T1.ID, T2.VAL - T1.VAL AS VALI, T2.Time FROM CTE T1
INNER JOIN CTE T2 ON T1.ID = T2.ID AND T1.RN = T2.RN - 1
ORDER BY T1.[Time], T1.ID
Result:
ID VALI Time
----------- ----------- -----------------------
2 0 2015-05-09 12:56:39.000
3 0 2015-05-09 12:56:39.000
2 4 2015-05-09 13:48:30.000
3 6 2015-05-09 13:48:30.000
2 2 2015-05-09 15:01:09.000
3 4 2015-05-09 15:01:09.000