* How to Replace a Null with the Corresponding Value in the Previous Record in SQL?

 

We have a database table HEADER, which has data as follows:

NAME

ENG1

ENG2

ENG3

UPDATEBY

UPDATEDDATE

adobe

Alex

Bob

John

2020-09-29 13:10:00

adobe

Clint

John

2020-09-29 13:12:00

adobe

Mary

John

2020-09-29 13:14:00

adobe

Sheila

John

2020-09-29 14:10:00

There is no connection between ENG1, ENG2 and ENG3, and there are null values under them. We can record them as ENGX. We are trying to sort the table by UPDATEDDATE, and, if ENGX in the current record is null, enter ENGX in the previous record in. Below is the desired result table:

NAME

ENG1

ENG2

ENG3

UPDATEBY

UPDATEDDATE

adobe

Alex

Bob

John

2020-09-29 13:10:00

adobe

Clint

Alex

Bob

John

2020-09-29 13:12:00

adobe

Clint

Mary

Bob

John

2020-09-29 13:14:00

adobe

Sheila

Mary

Bob

John

2020-09-29 14:10:00

SQL written in SQL Server:

SELECT H.*, MAX(ENG1) OVER (PARTITION BY NAME, GRP1 ) AS IMPUTED_ENG1

            , MAX(ENG2) OVER (PARTITION BY NAME, GRP2 ) AS IMPUTED_ENG2

            , MAX(ENG3) OVER (PARTITION BY NAME, GRP3 ) AS IMPUTED_ENG3

FROM (

            SELECT H.*, COUNT(ENG1) OVER (PARTITION BY NAME ORDER BY UPDATEDDATE) AS GRP1

                        , COUNT(ENG2) OVER (PARTITION BY NAME ORDER BY UPDATEDDATE) AS GRP2

                        , COUNT(ENG3) OVER (PARTITION BY NAME ORDER BY UPDATEDDATE) AS GRP3

            FROM HEADER H

) H;

It is easy to approach the task using the natural way of thinking. Sort rows by UPDATEDDATE, and for each null ENGX, assign the previous ENGX value to it. As SQL sets are unordered, we need the window function to mark ENGX null and non-null values with different identifiers and populate desired values to nulls. This is complex because we need two SELECTs.

 

It is simple to implement the algorithm using the open-source esProc SPL:

A

1

=connect("mssql")

2

=A1.query@x("SELECT * FROM HEADER ORDER BY UPDATEDDATE")

3

>A2.run(ENG1=ifn(ENG1,ENG1[-1]), ENG2=ifn(ENG2,ENG2[-1]), ENG3=ifn(ENG3,ENG3[-1]))

4

return A2

SPL sets are ordered. It is easy for it to implement computations between neighboring values/records.

Q & A Collection

https://stackoverflow.com/questions/64126270/replacing-empty-rows-with-previous-non-null-values-in-sql