Calculate Hourly Energy Consumption

Issue

This Content is from Stack Overflow. Question asked by jj89

I have a Table ‘Meter1’ in my MSSQL database DB_TEST. Table structure is like this,

MeterIDTimestampValue
72022-09-16 11:00:00.0001800
72022-09-16 12:00:00.0001805
72022-09-16 13:00:00.0001820
72022-09-16 14:00:00.0001860
72022-09-16 15:00:00.0001875

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

MeterIDTimestampValueConsumption
72022-09-16 11:00:00.0001800NULL
72022-09-16 12:00:00.00018055
72022-09-16 13:00:00.000182015
72022-09-16 14:00:00.000186040
72022-09-16 15:00:00.000187515

But how can I update or Insert the same results to my existing table with another column “Consumption”.



Solution

This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.

This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?