Untitled

USE ITC_PCPB_ULB_UTILITY_TW
GO
Create function udtvf_Nitrogen_Report(@fromdate as date,@todate as date)
Returns table as return
Select * from(
Select * from(
Select date,Asset,convert(decimal(18,2),avg(CycleTime)) [Cycle Time (Sec)]
,convert(decimal(18,2),avg(OutletPressure)) [Outlet Pressure(rpm)]
,convert(decimal(18,2),avg(Purity)) [Purity (%)]

from(
Select date,case when entity_id='ITC_PCPB_ULB_UTILITY_Nitrogen_Plant_1_Stream' 
			then 'Nitrogen Plant 1'
			end as Asset
,AirendDischargeTemp,MotorSpeed,SystemPressure 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-05'
				and a.entity_id like '%Nitrogen%'
				) as a
			CROSS APPLY 
				OPENJSON(a.field_values,'$')
				WITH (
					CycleTime float '$.CycleTime'
					,OutletPressure float '$.OutletPressure'
					,Purity float '$.Purity'
				)as t
				)as final group by Asset,date
				)as t

				unpivot
		( value for parameters in ( [Cycle Time (Sec)],[Outlet Pressure(rpm)],[Purity (%)])) as unpvt
				)as final