Untitled

DECLARE @fromdate DATE = '2024-09-04';  -- Set your start date
DECLARE @todate DATE = '2024-09-05';    -- Set your end date

-- Declare variables for dynamic SQL
DECLARE @daterange VARCHAR(MAX) = '[';
DECLARE @total VARCHAR(MAX) = '(';
DECLARE @start2 DATE = @fromdate;

-- Building dynamic columns for the PIVOT
WHILE @start2 <= @todate
BEGIN
    SET @daterange = @daterange + QUOTENAME(CONVERT(VARCHAR, @start2), '''') + ',';
    SET @total = @total + 'ISNULL(' + QUOTENAME(CONVERT(VARCHAR, @start2), '''') + ', 0) + ';
    SET @start2 = DATEADD(day, 1, @start2);
END

-- Remove trailing commas
SET @daterange = LEFT(@daterange, LEN(@daterange) - 1) + ']';
SET @total = LEFT(@total, LEN(@total) - 2) + ')';

-- Building dynamic SQL query
DECLARE @sql VARCHAR(MAX) = '
;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 
    Asset,
    parameters,
    CONVERT(DECIMAL(18, 2), ' + @total + ') AS Total,
    CONVERT(DECIMAL(18, 2), (' + @total + ' / ' + CONVERT(VARCHAR, DATEDIFF(day, @fromdate, @todate) + 1) + ')) AS Average,
    ' + @daterange + '
FROM (
    SELECT * 
    FROM NitrogenReport
) AS t
UNPIVOT (
    value FOR parameters IN ([Cycle Time (Sec)], [Outlet Pressure(rpm)], [Purity (%)])
) AS unpvt
PIVOT (
    MAX(value) FOR date IN (' + @daterange + ')
) AS pvt
ORDER BY Asset, parameters;';

-- Print and execute the dynamic SQL
PRINT @sql;
EXEC (@sql);