Untitled
unknown
sql
a month ago
1.6 kB
2
Indexable
Never
USE ITC_PCPB_ULB_UTILITY_TW; -- Ensure you are using the correct database GO DECLARE @fromdate DATE = '2024-09-04'; -- Set your start date DECLARE @todate DATE = '2024-09-05'; -- Set your end date ;WITH NitrogenReport AS ( SELECT date, Asset, CONVERT(DECIMAL(18, 2), AVG(CycleTime)) AS [Cycle Time (Sec)], CONVERT(DECIMAL(18, 2), AVG(OutletPressure)) AS [Outlet Pressure(rpm)], CONVERT(DECIMAL(18, 2), AVG(Purity)) AS [Purity (%)] FROM ( SELECT date, CASE WHEN entity_id = 'ITC_PCPB_ULB_UTILITY_Nitrogen_Plant_1_Stream' THEN 'Nitrogen Plant 1' END AS Asset, CycleTime, OutletPressure, Purity FROM ( SELECT CONVERT(DATE, time) AS date, * FROM [ITC_PCPB_ULB_UTILITY_TW].[dbo].[stream] a WHERE CONVERT(DATE, a.time) BETWEEN @fromdate AND @todate 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 ) SELECT * FROM ( SELECT * FROM NitrogenReport ) AS t UNPIVOT ( value FOR parameters IN ([Cycle Time (Sec)], [Outlet Pressure(rpm)], [Purity (%)]) ) AS unpvt;
Leave a Comment