0

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".

Arvo
  • 10,349
  • 1
  • 31
  • 34
jj89
  • 1
  • 1
  • alter the table then https://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server – nbk Sep 19 '22 at 09:47
  • 1
    Why do you need write dynamic values into table? They go stale quickly. You can always consume results of your query. – Arvo Sep 19 '22 at 09:48
  • is there any primary key or unique key? You need that to find the row for update. Also you can create a view instead of persist value in table. – Meyssam Toluie Sep 19 '22 at 09:54

0 Answers0