Untitled

CREATE FUNCTION dbo.GetTotalConsumption
(
    @entityid VARCHAR(MAX),
    @date DATE
)
RETURNS FLOAT
AS
BEGIN
    DECLARE @totalConsumption FLOAT;

    SELECT @totalConsumption = SUM(consumption)
    FROM (
        SELECT time,
               [entity_id],
               GeneratorKWHour - LAG(GeneratorKWHour) OVER (PARTITION BY entity_id ORDER BY time) AS [consumption]
        FROM (
            SELECT CONVERT(DATE, time) AS date,
                   *
            FROM [ITC_PCPB_ULB_UTILITY_TW].[dbo].[stream] a
            WHERE CONVERT(DATE, a.time) = @date
              AND a.entity_id = @entityid
        ) AS a
        CROSS APPLY OPENJSON(a.field_values, '$')
        WITH (
            GeneratorKWHour FLOAT '$.GeneratorKWHour'
        ) AS t
    ) AS final
    -- Optionally, filter out rows with negative or null consumption
    -- WHERE consumption > 0 AND consumption IS NOT NULL;

    RETURN @totalConsumption;
END;