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

Answer 4

Maybe something like the code given below.

Note: Code not tested. Also, this code needs to consider if you need the previous value based on the ID column or any other requirement like that.

CREATE TABLE Table1 (Date1 VARCHAR(10), ID VARCHAR(10), Class VARCHAR(10), ID2 INT);

INSERT INTO Table1 Values ('11/30/2015', 'ID1', 'ClassName', 1);
INSERT INTO Table1 Values (NULL, 'ID1', 'ClassName', 2);
INSERT INTO Table1 Values (NULL, 'ID1', 'ClassName', 3);
INSERT INTO Table1 Values (NULL, 'ID1', 'ClassName', 4);
INSERT INTO Table1 Values ('12/31/2015', 'ID1', 'ClassName', 5);
INSERT INTO Table1 Values (NULL, 'ID1', 'ClassName', 6);
INSERT INTO Table1 Values (NULL, 'ID1', 'ClassName', 7);

CREATE TABLE Table2 (Date1 VARCHAR(10), ID VARCHAR(10), Class VARCHAR(10), ID2 INT);

DECLARE @Date1 VARCHAR(10), @ID1 VARCHAR(10), @Class VARCHAR(10), @ID2 INT;
DECLARE @TempDate1 VARCHAR(10); --set default if first record is null

DECLARE CURSOR MYCUR FOR
SELECT Date1, ID1, Class, ID2
FROM Table1;
OPEN MYCUR
FETCH NEXT FROM MYCUR INTO @Date1, @ID1, @Class, @ID2

WHILE @@FETCH_STATUS = 0
BEGIN

    IF (@Date1 IS NOT NULL)
    BEGIN
        @TempDate1=@Date1
    END

    INSERT INTO Table2 VALUES(@TempDate1, @ID1, @Class, @ID2)

    FETCH NEXT FROM MYCUR INTO @Date1, @ID1, @Class, @ID2
END

CLOSE MYCUR
DEALLOCATE MYCUR

SELECT * FROM Table2

Answer 5

Working with MS Access the approach that worked for me was to add an autonumber (ID) field to establish a fixed order and then use an update statement with a copy of the source table and suitable where condition:

UPDATE Table, Table AS Table_1 SET Table.Field  = [Table_1].[Field]
WHERE (((Table_1.Field) Is Not Null) AND ((Table_1.ID)<[Table].[ID]));

Answer 6

You can use Update statement.

The sample query to generate data:

CREATE TABLE table1 (Date1 date)

insert into Table1 values ('11/30/2015')
insert into Table1 values (NULL)
insert into Table1 values (NULL)
insert into Table1 values ('11/20/2016')
insert into Table1 values (NULL)
insert into Table1 values ('10/12/2017')
insert into Table1 values (NULL)

And the query with an Update Statement,

DECLARE @n date
UPDATE Table1 
SET 
    @n = COALESCE(DATE1, @n),
    DATE1 = COALESCE(DATE1, @n)

 SELECT * from table1

Output:

Date1
2015-11-30
2015-11-30
2015-11-30
2016-11-20
2016-11-20
2017-10-12
2017-10-12