SQL: Filling in missing values with last known.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL: Filling in missing values with last known.

Author

Topic

Umar Strong
Starting Member

8 Posts

Posted - 2013-07-31 : 17:46:25

Is it possible to write a query to fill in the missing value of a column with the last known value.

For example, the following dataset has values for the years 2010,11,12,15 & 16; but does have values for the year 2013,14 & 18.

Year Value
---- ------
2010 A
2011 B
2012 C
2013
2014
2015 P
2016 Q
2018

My requirement is to fill in the missing values with the last known values - i.e.,

Year Value
---- ------
2010 A
2011 B
2012 C
2013 C
2014 C
2015 P
2016 Q
2018 Q

Can this be done using a SQL? I have tried using self join to the table i.e., INNER JOIN and OUTER APPLY. But I guess I am not writing the query correctly.

Umar Strong
Starting Member

8 Posts

Posted - 2013-07-31 : 18:09:11

I just solved it using APPLY.

DDL/DML:
CREATE TABLE DBO.TEMP_RND ([YEAR] INT, [VALUE] VARCHAR(1))
INSERT INTO DBO.TEMP_RND VALUES (2010, 'A')
INSERT INTO DBO.TEMP_RND VALUES (2011, 'B')
INSERT INTO DBO.TEMP_RND VALUES (2012, 'C')
INSERT INTO DBO.TEMP_RND VALUES (2013, NULL)
INSERT INTO DBO.TEMP_RND VALUES (2014, NULL)
INSERT INTO DBO.TEMP_RND VALUES (2015, 'P')
INSERT INTO DBO.TEMP_RND VALUES (2016, 'Q')
INSERT INTO DBO.TEMP_RND VALUES (2018, NULL)

SQL:

SELECT
  a.[YEAR],
  COALESCE(a.[VALUE], b.[VALUE]) AS FILLED_IN_VALUE
FROM
  TEMP_RND a
  OUTER APPLY (
    SELECT TOP 1 x.[VALUE]
    FROM TEMP_RND x
    WHERE
      a.[VALUE] IS NULL
      AND x.[VALUE] IS NOT NULL
      AND x.[YEAR] < a.[YEAR]
    ORDER BY x.[YEAR] DESC
    ) b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources