Untitled

DECLARE @start DATE = '2024-09-02';
DECLARE @end DATE = '2024-09-03';

DECLARE @total VARCHAR(MAX) = '[';
DECLARE @daterange VARCHAR(MAX) = '[';
DECLARE @datediff AS INT = DATEDIFF(day, @start, @end) + 1;
DECLARE @start2 DATE = @start;

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

-- Removing trailing characters and closing brackets
SET @daterange = LEFT(@daterange, LEN(@daterange) - 1) + ']';
SET @total = LEFT(@total, LEN(@total) - 3) + ')';

-- Building the dynamic SQL query
DECLARE @sql VARCHAR(MAX) = '
SELECT 
    CONVERT(DECIMAL(18, 2), ' + @total + ') AS Total,
    CONVERT(DECIMAL(18, 2), (' + @total + ' / ' + CONVERT(VARCHAR, @datediff) + ')) AS Average,
    entity_id, 
    parameters 
FROM (
    SELECT 
        date, 
        entity_id, 
        parameters, 
        value 
    FROM (
        SELECT 
            [entity_id],
            date, 
            CONVERT(DECIMAL(18, 2), COALESCE(AVG(CycleTime), 0)) AS [CycleTime(Sec)],
            CONVERT(DECIMAL(18, 2), COALESCE(AVG(OutletPressure), 0)) AS [OutletPressure(Bar)],
            CONVERT(DECIMAL(18, 2), COALESCE(AVG(Purity), 0)) AS [Purity(%)]
        FROM 
        ( 
            SELECT 
                CONVERT(date, time) AS 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 ''%Nitrogen%''
        ) AS a
        CROSS APPLY 
            OPENJSON(a.field_values, ''$'')
            WITH (
                 CycleTime float ''$.CycleTime'',
                 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 and execute the dynamic SQL
PRINT @sql;
EXEC (@sql);