Untitled

USE ITC_PCPB_ULB_UTILITY_TW;  -- Ensure you are using the correct database
GO

create function udtvf_Nitrogen_Report(@fromdate as date,@todate as date)
--DECLARE @fromdate DATE ='2024-09-04';  -- Set your start date
--DECLARE @todate DATE = '2024-09-10';    -- Set your end date
Returns
Table
As
Return

;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(Bar)],
        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(Bar)], [Purity (%)])
) AS unpvt;