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],EngineRunHr1-LAG(EngineRunHr1) Over(Partition by entity_id order by time)[TotalEngineHr1],
                       EngineRunHr2-LAG(EngineRunHr2) Over(Partition by entity_id order by time)[TotalEngineHr2],
					   EngineRunHr3-LAG(EngineRunHr3) Over(Partition by entity_id order by time)[TotalEngineHr3] from(
	Select date,entity_id,EngineRunHr1,EngineRunHr2,EngineRunHr3 from(
		Select * 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 (

				EngineRunHr1 float '$.EngineRunHr1',
				EngineRunHr2 float '$.EngineRunHr2',
				EngineRunHr3 float '$.EngineRunHr3'

				)as t
				)as final --where consumption>0 and consumption is not null