0

I have a table where each row consists of an ID, date, variable values (eg. var1).

When there is a null value for var1 in a row, I want like to replace the null value with the most recent non-null value before that date for that ID. How can I do this quickly for a very large table?

So presume I start with this table:

+----+------------|-------+
| id |date        | var1  |
+----+------------+-------+
|  1 |'01-01-2022'|55     |
|  2 |'01-01-2022'|12     |
|  3 |'01-01-2022'|45     |
|  1 |'01-02-2022'|Null   |
|  2 |'01-02-2022'|Null   |
|  3 |'01-02-2022'|20     |
|  1 |'01-03-2022'|15     |
|  2 |'01-03-2022'|Null   |
|  3 |'01-03-2022'|Null   |
|  1 |'01-04-2022'|Null   |
|  2 |'01-04-2022'|77     |
+----+------------+-------+

Then I want this

+----+------------|-------+
| id |date        | var1  |
+----+------------+-------+
|  1 |'01-01-2022'|55     |
|  2 |'01-01-2022'|12     |
|  3 |'01-01-2022'|45     |
|  1 |'01-02-2022'|55     |
|  2 |'01-02-2022'|12     |
|  3 |'01-02-2022'|20     |
|  1 |'01-03-2022'|15     |
|  2 |'01-03-2022'|12     |
|  3 |'01-03-2022'|20     |
|  1 |'01-04-2022'|15     |
|  2 |'01-04-2022'|77     |
+----+------------+-------+
PhidStudent
  • 1
  • 1
  • Does this answer your question? [replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)](https://stackoverflow.com/questions/7045040/replace-null-values-with-latest-non-null-value-in-resultset-series-sql-server-2) – INDRAJITH EKANAYAKE Aug 10 '22 at 01:29

1 Answers1

1

cte suits perfect here this snippets returns the rows with values, just an update query and thats all (will update my response).

WITH selectcte AS
 (
     SELECT *  FROM testnulls where var1 is NOT NULL
 )

 SELECT t1A.id, t1A.date, ISNULL(t1A.var1,t1B.var1) varvalue
 FROM selectcte t1A
 OUTER APPLY (SELECT TOP 1 *
                 FROM selectcte 
                 WHERE id = t1A.id AND date < t1A.date
                 AND var1 IS NOT NULL
                 ORDER BY id, date DESC) t1B

Here you can dig further about CTEs :

https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16

jmvcollaborator
  • 2,141
  • 1
  • 6
  • 17