Untitled

Declare @entityid as varchar(max)='ITC_PCPB_ULB_UTILITY_DG_1_Stream'
Declare @date as date='2024-09-05'
Select sum(consumption) from(
  Select time,[entity_id],GeneratorKWHour-LAG(GeneratorKWHour) Over(Partition by entity_id order by time)[consumption] from(
			Select 
			convert(date,time) date,
			* 
			from
				[ITC_PCPB_ULB_UTILITY_TW].[dbo].[stream] a 
				where 
				convert(date,a.time)=@date
				and a.entity_id =@entityid) as a
			CROSS APPLY 
				OPENJSON(a.field_values,'$')
				WITH (

				GeneratorKWHour float '$.GeneratorKWHour'
				)as t
				)as final --where consumption>0 and consumption is not null