I have the below data set. There is a value for each day, which is always more than the value of the previous day.
TimeStamps are given in dd/mm/yyyy format.
TimeStamp Device Value
------------------- ------ ---------
25/05/2017 00:00:00 A_Mill 150292432
26/05/2017 00:00:00 A_Mill 150507748
27/05/2017 00:00:00 A_Mill 150745778
28/05/2017 00:00:00 A_Mill 150918209
29/05/2017 00:00:00 A_Mill 151201139
30/05/2017 00:00:00 A_Mill 151413118
31/05/2017 00:00:00 A_Mill 151617243
01/06/2017 00:00:00 A_Mill 151798964
02/06/2017 00:00:00 A_Mill 151985446
I need to add two columns:
First, the 1-day difference. In the above example, the 01/06/2017 row's new 1DayDifference would be the Value minus the 02/06/2017 row's Value..
Second, the 7-day difference. The 02/06/2017 row's new 7DayDifference would be the Value minus the 26/05/2017 row's Value.
TimeStamp Device Value 1DayDifference 7DayDifference
------------------- ------ --------- -------------- --------------
25/05/2017 00:00:00 A_Mill 150292432 215316
26/05/2017 00:00:00 A_Mill 150507748 238030
27/05/2017 00:00:00 A_Mill 150745778 172431
28/05/2017 00:00:00 A_Mill 150918209 282930
29/05/2017 00:00:00 A_Mill 151201139 211979
30/05/2017 00:00:00 A_Mill 151413118 204125
31/05/2017 00:00:00 A_Mill 151617243 181721 1506532
01/06/2017 00:00:00 A_Mill 151798964 186482 1477698
02/06/2017 00:00:00 A_Mill 151985446
This is all nice and easy to achieve in Excel but I don't know where to start with it in T-SQL.