Untitled
unknown
sql
8 days ago
1.9 kB
2
Indexable
Never
Select date,entity_id,source_id,GeneratorPF,GeneratorEarthCurrent,EngineSpeed,ChargeAlternatorVolt ,EngineBetteryVolt ,CoolantTemp ,EngineRunHr3 ,GeneratorL1Current ,GeneratorL2_L3Volt ,GeneratorL3_L1Volt ,GeneratorKVAHour ,OilPressure ,GeneratorL1_NVolt ,GeneratorL3Current ,GeneratorL3_NVolt ,dbo.udsvf_DG_Run_Hr([entity_id],date) [RunningHour] from( Select convert(date,time) date, * from [ITC_PCPB_ULB_UTILITY_TW].[dbo].[stream] a where convert(date,a.time) between '2024-09-04' and '2024-09-06' and a.entity_id like '%DG%') as a CROSS APPLY OPENJSON(a.field_values,'$') WITH ( GeneratorPF float '$.GeneratorPF', GeneratorEarthCurrent float '$.GeneratorEarthCurrent' ,GeneratorKVArHour float '$.GeneratorKVArHour' ,GeneratorL2_NVolt float '$.GeneratorL2_NVolt' ,EngineSpeed float '$.EngineSpeed' ,ChargeAlternatorVolt float '$.ChargeAlternatorVolt' ,EngineBetteryVolt float '$.EngineBetteryVolt' ,CoolantTemp float '$.CoolantTemp' ,EngineRunHr3 float '$.EngineRunHr3' ,GeneratorL1Current float '$.GeneratorL1Current' ,GeneratorL2_L3Volt float '$.GeneratorL2_L3Volt' ,GeneratorL3_L1Volt float '$.GeneratorL3_L1Volt' ,GeneratorKVAHour float '$.GeneratorKVAHour' ,GeneratorL2Current float '$.GeneratorL2Current' ,OilPressure float '$.OilPressure' ,GeneratorL1_NVolt float '$.GeneratorL1_NVolt' ,GeneratorL3Current float '$.GeneratorL3Current' --,EngineRunHr1 float '$.EngineRunHr1' --,EngineRunHr2 float '$.EngineRunHr2' ,GeneratorL3_NVolt float '$.GeneratorL3_NVolt' ,GeneratorL1_L2Volt float '$.GeneratorL1_L2Volt' ,GeneratorKWHour float '$.GeneratorKWHour' ,GenFrequency float '$.GenFrequency' ,Fuellevel float '$.Fuellevel' )As t --group by date,[entity_id] order by entity_id --)as t1
Leave a Comment