Untitled
unknown
sql
11 days ago
1.0 kB
2
Indexable
Never
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
Leave a Comment