SQL Populating Null Values

Question

I have a table that has some rows that are populated by a date and some rows that have the date but the rest of the fields are null. Looking for the most efficient sql scripting for filling down missing values for make and sale price, I do not want the other null values to be changed(note price changes). I want the same make and price for the rows that follow that contain null values.

Example of what table looks like:

Sale_Dates  | Make  | Sale_Price | Year | Color
2015-01-01  |  Ford | 20000.00   |2012  | Blue
2015-01-02  |  NULL | NULL       |NULL  | NULL    
2015-01-03  |  NULL | NULL       |NULL  | NULL
2015-01-04  |  Ford | 30000.00   |NULL  | NULL
2015-01-05  |  NULL | NULL       |NULL  | NULL

Example of expected output:

Sale_Dates  | Make  | Sale_Price | Year | Color
2015-01-01  |  Ford | 20000.00   |2012  | Blue
2015-01-02  |  Ford | 20000.00   |NULL  | NULL    
2015-01-03  |  Ford | 20000.00   |NULL  | NULL
2015-01-04  |  Ford | 30000.00   |NULL  | NULL
2015-01-05  |  Ford | 30000.00   |NULL  | NULL

When another user created the table they did a cross join to create the table view that you see in the first section. So I need to work with the output. I tried to use row_number to create a ranking but that is leading nowhere. — User214122114, Jan 15 '15 at 11:49
Okay, you will need to post the actual database structure rather than the `VIEW`, for one thing. Also, where are you getting the missing data from exactly? And do you want to actually add the data stored or just change the view? — Eilidh, Jan 15 '15 at 12:02
Please provide your DBMS and version, plus a table definition. Also: `Sale_Dates`? Why the plural form? — Erwin Brandstetter, Jan 15 '15 at 12:08

score 1 · Answer 1 · edited May 23 '17 at 11:56

Since you mentioned row_number in a comment I assume you have window functions. (Every modern RDBMS has them, except for MySQL.) Then there is a simple solution:

SELECT sale_date
     , max(make)       OVER (PARTITION BY grp) AS make
     , max(sale_price) OVER (PARTITION BY grp) AS sale_price
     , year, color
FROM  (
   SELECT *, count(make) OVER (ORDER BY sale_date) AS grp
   FROM   tbl
   ORDER  BY sale_date
   ) sub;

Assuming either both make and sale_price or NULL or both are NOT NULL.

SQL Fiddle for Postgres.

More explanation:

Retrieve last known value for each column of a row

score 0 · Answer 2 · answered Jan 15 '15 at 12:18

Even though CURSOR have performance drawback, at this point its better you can use CURSOR else you need to check for every NOT NULL value for each record. While using this, you can keep the last NOT NULL value in a variable.

Note : I am posting this answer hoping your database is SQL SERVER.

SAMPLE TABLE

CREATE TABLE #TEMP(Sale_Dates  DATE,MAKE VARCHAR(30),Sale_Price NUMERIC(18,2),[YEAR] INT,[COLOR] VARCHAR(20))

INSERT INTO #TEMP
SELECT '2015-01-01'  ,  'Ford' , 20000.00   ,2012  , 'Blue'
UNION ALL
SELECT '2015-01-02'  ,  NULL , NULL       ,NULL  , NULL    
UNION ALL
SELECT '2015-01-03'  ,  NULL , NULL       ,NULL  , NULL
UNION ALL
SELECT '2015-01-04'  ,  'Maruti' , 30000.00   ,NULL  , NULL
UNION ALL
SELECT '2015-01-05'  ,  NULL , NULL       ,NULL  , NULL

QUERY

DECLARE @Sale_Dates DATE
DECLARE @MAKE VARCHAR(30)
DECLARE @Sale_Price NUMERIC(20,2)=0
DECLARE @Previous_MAKE VARCHAR(30)
DECLARE @Previous_Sale_Price NUMERIC(20,2)=0

-- Here you declare which all columns you need to loop in Cursor
DECLARE CUR CURSOR FOR 
SELECT Sale_Dates,MAKE,Sale_Price
FROM #TEMP
ORDER BY Sale_Dates;

OPEN CUR

-- Loop starts from here and selects each Seq and Amount of each record in each loop 
FETCH NEXT FROM CUR INTO @Sale_Dates,@MAKE,@Sale_Price

WHILE @@FETCH_STATUS = 0
BEGIN

    -- Updates with previous NOT NULL value
    IF(@MAKE IS NULL)
    BEGIN
        UPDATE #TEMP SET MAKE = @Previous_MAKE
        WHERE Sale_Dates = @Sale_Dates
    END

    -- Updates with previous NOT NULL value
    IF(@Sale_Price IS NULL)
    BEGIN
        UPDATE #TEMP SET Sale_Price = @Previous_Sale_Price
        WHERE Sale_Dates = @Sale_Dates
    END

    -- Store previous NOT NULL value
    IF(@MAKE IS NOT NULL)
    BEGIN
        SET @Previous_MAKE =  @MAKE
    END

    -- Store previous NOT NULL value
    IF(@Sale_Price IS NOT NULL)
    BEGIN
        SET @Previous_Sale_Price =  @Sale_Price
    END

    -- Fetches next record and increments the loop
    FETCH NEXT FROM CUR INTO @Sale_Dates,@MAKE,@Sale_Price
END 

CLOSE CUR;
DEALLOCATE CUR;

A window function will perform much better here. – Jan 15 '15 at 12:20 — , Jan 15 '15 at 12:20

SQL Populating Null Values

2 Answers2