Untitled

Declare @start as date;
Declare @end as date;



    Declare @total varchar(max)='[';
	Declare @daterange varchar(max)='[';
	Declare @datediff as varchar(max)=DATEDIFF(day,@start,@end)+1
	Declare @start2 DATE=@start

	WHILE @start2<@end
	BEGIN
		SET @daterange=@daterange+CONVERT(VARCHAR,@start2)+'],['
		SET @total = @total + CONVERT(VARCHAR,@start2)+']+['
		SELECT @start2=DATEADD(day,1,@start2);
	END
	set @total=@total + CONVERT(VARCHAR,@end)+']'
	SET @daterange=@daterange+CONVERT(VARCHAR,@end)+']'
	print @total

	DECLARE @sql VARCHAR(MAX)='
	Select case when parameters like ''%Running%'' then convert(decimal(18,2), '+@total+') else 0  end as Total,
	convert(decimal(18,2),('+@total+')/'+@datediff+') as Average
	else ''''
	end as Asset
	,* from(
	Select 
		date,
		entity_id,
		parameters,
		value 
	from(
		Select 
			[entity_id],
			date, 
			CONVERT(Decimal(18,2),COALESCE(AVG(CycleTime),0)) [CycleTime(Sec)],
			CONVERT(Decimal(18,2),COALESCE(AVG(OutletPressure),0)) [OutletPressure(Bar)],
			CONVERT(Decimal(18,2),COALESCE(AVG(Purity),0)) [Purity(%)]

		FROM 
		( 
			Select 
			convert(date,time) date,
			* 
			from
				[ITC_PCPB_ULB_UTILITY_TW].[dbo].[stream] a 
				where 
				convert(date,a.time) between '''+convert(varchar(max),@start)+''' and '''+convert(varchar(max),@end) +'''
				and a.entity_id like ''%Nitogen%'') as a
			CROSS APPLY 
				OPENJSON(a.field_values,''$'')
				WITH (
					 CycleTime float ''$. CycleTime float''
					,OutletPressure float ''$.OutletPressure ''
					,Purity float ''$.Purity''
				 
				)As t group by date,[entity_id]
		)as t1 
		unpivot
		(
		value for parameters in (
	  
			  [CycleTime(Sec)],
			  [OutletPressure(Bar)],
			  [Purity(%)]
             ))as pvt
	 )as t2 pivot(max(value) for date in ('+@daterange+')) as t3 order by entity_id,parameters'
	 print @sql
    EXEC (@sql)
	 END

EXEC getDGReport '2024-09-02','2024-09-03'


select * FROM  [ITC_PCPB_ULB_UTILITY_TW].[dbo].[stream]
where entity_id like '%Nitro%'