Replace null value with previous available value in Row SQL server query

Question

I am looking for building a query to replace null value with previous available values.can somebody help.Here is the table currently looking like

11/30/2015   ID1     CLassName   1
NULL         ID1     CLassName   2
NULL         ID1     CLassName   3
NULL         ID1     CLassName   4
11/30/2015   ID1     CLassName   5
NULL         ID1     CLassName   6
NULL         ID1     CLassName   7
12/31/2015   ID1     CLassName   1
NULL         ID1     CLassName   2
NULL         ID1     CLassName   3
NULL         ID1     CLassName   4
12/31/2015   ID1     CLassName   5
NULL         ID1     CLassName   6
NULL         ID1     CLassName   7

Output shall look like

11/30/2015       ID1     CLassName   1
11/30/2015       ID1     CLassName   2
11/30/2015       ID1     CLassName   3
11/30/2015       ID1     CLassName   4
11/30/2015       ID1     CLassName   5
11/30/2015       ID1     CLassName   6
11/30/2015       ID1     CLassName   7
12/31/2015       ID1     CLassName   1
12/31/2015       ID1     CLassName   2
12/31/2015       ID1     CLassName   3
12/31/2015       ID1     CLassName   4
12/31/2015       ID1     CLassName   5
12/31/2015       ID1     CLassName   6
12/31/2015       ID1     CLassName   7

Answer 1

The below statement working perfectly

SELECT 
CASE WHEN DATE1 IS NULL 
     THEN 
(SELECT TOP 1 DATE1 FROM Table1 WHERE ID2<T.ID2 
  AND Date1 IS NOT NULL ORDER BY ID2 DESC) ELSE Date1 END AS DATENEW,
*FROM Table1 T

The output like below

        DATENEW             Date1          ID   Class      ID2
        11/30/2015          11/30/2015     ID1  ClassName   1
        11/30/2015          NULL           ID1  ClassName   2
        11/30/2015          NULL           ID1  ClassName   3
        11/30/2015          NULL           ID1  ClassName   4
        12/31/2015          12/31/2015     ID1  ClassName   5
        12/31/2015          NULL           ID1  ClassName   6
        12/31/2015          NULL           ID1  ClassName   7

Answer 2

This question is a bit old, but you can achieve the same thing using the first_value function with SQL Server (starting with 2012 version)

First, you can create a new column that contains an increasing number for each "block" of a non-null date and all the next null values:

WITH CTE AS
(
    SELECT *,
           SUM(CASE WHEN Date1 is NULL then 0 else 1 END) AS block
    FROM your_table
)

This CTE will create something like this (I'm using the column names of Shakeer's answer):

Date1          ID   Class      ID2    block
11/30/2015     ID1  ClassName   1      1
NULL           ID1  ClassName   2      1
NULL           ID1  ClassName   3      1
NULL           ID1  ClassName   4      1
12/31/2015     ID1  ClassName   5      2
NULL           ID1  ClassName   6      2
NULL           ID1  ClassName   7      2

Now, you can use the first_value function to get the first value of each "block":

SELECT *,
        first_value(Date1) OVER (PARTITION BY block ORDER BY ID2) AS NewDate
FROM CTE

I hope this helps.

Answer 3

I think this should work, I am assuming the table and column names since you have not provided these, also assuming id is the column based on which you are ordering the rows

 UPDATE table1 T
    SET T.date1 = (
                     SELECT MAX(T2.date)
                       FROM table1 T2
                      WHERE T2.date IS NOT NULL
                        AND T2.id <= T.id
                  )
  WHERE T.date1 IS NULL