I have a Table 'Meter1' in my MSSQL database DB_TEST. Table structure is like this,
| MeterID | Timestamp | Value |
|---|---|---|
| 7 | 2022-09-16 11:00:00.000 | 1800 |
| 7 | 2022-09-16 12:00:00.000 | 1805 |
| 7 | 2022-09-16 13:00:00.000 | 1820 |
| 7 | 2022-09-16 14:00:00.000 | 1860 |
| 7 | 2022-09-16 15:00:00.000 | 1875 |
I need to calculate the hourly consumption by substracting the current value - previous value.
I have achieved this by using LAG function,
SELECT [MeterID]
,[Timestamp]
,[Value]
,VALUE - LAG (VALUE)
OVER (ORDER BY TIMESTAMP) AS Consumption
FROM [DB_TEST].[dbo].[Meter1]
& the results like this
| MeterID | Timestamp | Value | Consumption |
|---|---|---|---|
| 7 | 2022-09-16 11:00:00.000 | 1800 | NULL |
| 7 | 2022-09-16 12:00:00.000 | 1805 | 5 |
| 7 | 2022-09-16 13:00:00.000 | 1820 | 15 |
| 7 | 2022-09-16 14:00:00.000 | 1860 | 40 |
| 7 | 2022-09-16 15:00:00.000 | 1875 | 15 |
But how can I update or Insert the same results to my existing table with another column "Consumption".