how to average consecutive values in a record which meets a condition using case statements in sql

Question

SO I have a table which has fields: So, This value - 1023 and QualityCode - 53 can occur at random times, and for every MeterNum there are about 24 values, the ROW_ID increments till 24 and then starts from 1 for new MeterNum, I need to replace the 1023 with an average of 46 and 21 (row_num 19 & 21) if not at least add another alias column in the select statement. I was trying to come up with case statement, but I am not able to average the values, can someone help with this.

Answer 1

You can use Lag and Lead to do this sort of logic inside of a CASE statement:

UPDATE TABLE
SET VALUE = CASE 
        WHEN VALUE = 1023
            THEN (
                    Lag(Value, 1) OVER (PARTITION BY meternum ORDER BY row_id ASC) 
                    + Lead(Value, 1) OVER (PARTITION BY meternum ORDER BY row_id ASC)
                  ) / 2
        ELSE VALUE
        END

Lag and Lead are Window Functions. Lag will go back the number of records specified (here we go back 1) and grab a value. It determines which records by the PARTITION BY clause (here only go back records with the same MeterNum) and determines the order of those records by the ORDER BY clause (ordering here by your row_id).