Untitled

USE ITC_PCPB_ULB_UTILITY_TW;  
GO

CREATE FUNCTION udtvf_Nitrogen_Report(@fromdate DATE, @todate 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 
                CONVERT(DATE, time) AS 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 
        Asset,
        parameters,
        value
    FROM (
        SELECT * 
        FROM NitrogenReport
    ) AS t
    UNPIVOT (
        value FOR parameters IN ([Cycle Time (Sec)], [Outlet Pressure(Bar)], [Purity (%)])
    ) AS unpvt
);
GO