FIll null values from previous records in SQL Server

Question

My table has certain blank values in a column that I need to fill in from previous rows. My source data currently looks like

Row ID                  YEAR    Period  NUMBER  PeriodYear
49  000000000130000000  2014    4       NULL    4/1/2014
50  000000000130000000  2014    3       286.26  3/1/2014
51  000000000130000000  2014    2       NULL    2/1/2014
52  000000000130000000  2014    1       NULL    1/1/2014
53  000000000130000000  2013    12      286.26  12/1/2013
54  000000000130000000  2013    11      NULL    11/1/2013
55  000000000130000000  2013    10      NULL    10/1/2013
56  000000000130000000  2013    9       286.26  9/1/2013
57  000000000130000000  2013    8       NULL    8/1/2013
58  000000000130000000  2013    7       NULL    7/1/2013
59  000000000130000000  2013    6       286.26  6/1/2013
60  000000000130000000  2013    5       NULL    5/1/2013
61  000000000130000000  2013    4       286.26  4/1/2013
62  000000000130000000  2013    3       291.98  3/1/2013
63  000000000130000000  2013    2       NULL    2/1/2013
64  000000000130000000  2013    1       291.98  1/1/2013
65  000000000130000000  2012    12      280.49  12/1/2012
66  000000000130000000  2012    11      280.49  11/1/2012
67  000000000130000000  2012    10      280.49  10/1/2012
68  000000000130000000  2012    9       289.96  9/1/2012
69  000000000130000000  2012    8       NULL    8/1/2012
70  000000000130000000  2012    7       289.96  7/1/2012
71  000000000130000000  2012    6       294.54  6/1/2012
72  000000000130000000  2012    5       NULL    5/1/2012

I want the target to look like:

Row ID                  YEAR    Period  NUMBER  PeriodYear
49  000000000130000000  2014    4       NULL    4/1/2014
50  000000000130000000  2014    3       286.26  3/1/2014
51  000000000130000000  2014    2       286.26  2/1/2014
52  000000000130000000  2014    1       286.26  1/1/2014
53  000000000130000000  2013    12      286.26  12/1/2013
54  000000000130000000  2013    11      286.26  11/1/2013
55  000000000130000000  2013    10      286.26  10/1/2013
56  000000000130000000  2013    9       286.26  9/1/2013
57  000000000130000000  2013    8       286.26  8/1/2013
58  000000000130000000  2013    7       286.26  7/1/2013
59  000000000130000000  2013    6       286.26  6/1/2013
60  000000000130000000  2013    5       286.26  5/1/2013
61  000000000130000000  2013    4       286.26  4/1/2013
62  000000000130000000  2013    3       291.98  3/1/2013
63  000000000130000000  2013    2       291.98  2/1/2013
64  000000000130000000  2013    1       291.98  1/1/2013
65  000000000130000000  2012    12      280.49  12/1/2012
66  000000000130000000  2012    11      280.49  11/1/2012
67  000000000130000000  2012    10      280.49  10/1/2012
68  000000000130000000  2012    9       289.96  9/1/2012
69  000000000130000000  2012    8       289.96  8/1/2012
70  000000000130000000  2012    7       289.96  7/1/2012
71  000000000130000000  2012    6       294.54  6/1/2012
72  000000000130000000  2012    5       294.54  5/1/2012

Any help will be appreciated.

@GiannisParaskevopoulos: do Lead and Lag also work conditionally? So like "give me the first PeriodYear with the same ID which is not null and which has a lower Row than me". — Tim Schmelter
– Tim Schmelter, Commented Sep 19, 2014 at 8:53

Tim Schmelter · Accepted Answer · 2014-09-19 09:04:43Z

2

Use a sub-query:

UPDATE t
SET t.NUMBER = (SELECT TOP 1 t2.PeriodYear
                FROM dbo.TableName t2
                WHERE t2.ID = t.ID
                  AND t2.Row < t.Row
                  AND t2.NUMBER IS NOT NULL)
FROM dbo.TableName t
WHERE t.NUMBER IS NULL

edited Sep 19, 2014 at 9:04

answered Sep 19, 2014 at 8:50

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Felix Pamittan Over a year ago

The column to be updated is number not PeriodYear

Tim Schmelter Over a year ago

@wewesthemenace: thanks, now it's clear because it's formatted.

Nick.Mc · Accepted Answer · 2014-09-19 08:55:10Z

0

Here's one way:

SELECT 1

WHILE @@ROWCOUNT <> 0
BEGIN
    UPDATE Table 
    SET [NUMBER] = Prior.[NUMBER]
    FROM Table Current
    INNER JOIN Table Prior
    ON  Prior.[Row] = Current.[Row] -1
    AND Current.[NUMBER] IS NULL
    AND Prior.[NUMBER] IS NOT NULL
END

answered Sep 19, 2014 at 8:55

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

2 Comments

Mihai Over a year ago

Does @@ROWCOUNT not need an ORDER BY?

Nick.Mc Over a year ago

I'm not sure I understand. Why would @@ROWCOUNT need an order by? it's a scalar, it has no order.

Felix Pamittan · Accepted Answer · 2014-09-19 08:56:08Z

Try this one:

--build sample data

create table #temp_table(
    [Row] int,
    [Id] varchar(200),
    [Year] int,
    [Period] int,
    [Number] decimal(18,2),
    [PeriodYear] date
)
insert into #temp_table
select  49, '000000000130000000',   2014,   4,  NULL,   '4/1/2014'  union   all
select  50, '000000000130000000',   2014,   3,  286.26, '3/1/2014'  union   all
select  51, '000000000130000000',   2014,   2,  NULL,   '2/1/2014'  union   all
select  52, '000000000130000000',   2014,   1,  NULL,   '1/1/2014'  union   all
select  53, '000000000130000000',   2013,   12, 286.26, '12/1/2013' union   all
select  54, '000000000130000000',   2013,   11, NULL,   '11/1/2013' union   all
select  55, '000000000130000000',   2013,   10, NULL,   '10/1/2013' union   all
select  56, '000000000130000000',   2013,   9,  286.26, '9/1/2013'  union   all
select  57, '000000000130000000',   2013,   8,  NULL,   '8/1/2013'  union   all
select  58, '000000000130000000',   2013,   7,  NULL,   '7/1/2013'  union   all
select  59, '000000000130000000',   2013,   6,  286.26, '6/1/2013'  union   all
select  60, '000000000130000000',   2013,   5,  NULL,   '5/1/2013'  union   all
select  61, '000000000130000000',   2013,   4,  286.26, '4/1/2013'  union   all
select  62, '000000000130000000',   2013,   3,  291.98, '3/1/2013'  union   all
select  63, '000000000130000000',   2013,   2,  NULL,   '2/1/2013'  union   all
select  64, '000000000130000000',   2013,   1,  291.98, '1/1/2013'  union   all
select  65, '000000000130000000',   2012,   12, 280.49, '12/1/2012' union   all
select  66, '000000000130000000',   2012,   11, 280.49, '11/1/2012' union   all
select  67, '000000000130000000',   2012,   10, 280.49, '10/1/2012' union   all
select  68, '000000000130000000',   2012,   9,  289.96, '9/1/2012'  union   all
select  69, '000000000130000000',   2012,   8,  NULL,   '8/1/2012'  union   all
select  70, '000000000130000000',   2012,   7,  289.96, '7/1/2012'  union   all
select  71, '000000000130000000',   2012,   6,  294.54, '6/1/2012'  union   all
select  72, '000000000130000000',   2012,   5,  NULL,   '5/1/2012'

select * from #temp_table

update t1
    set t1.number = (select top 1 t2.number from #temp_table t2 where t2.number is not null and t2.row < t1.row order by t2.row desc)
from #temp_table t1
where number is null

select * from #temp_table


drop table #temp_table

Collectives™ on Stack Overflow

FIll null values from previous records in SQL Server

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related