Untitled

mail@pastecode.io avatar
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