SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL: Filling in missing values with last known.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Umar Strong
Starting Member

8 Posts

Posted - 07/31/2013 :  17:46:25  Show Profile  Reply with Quote
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 - 07/31/2013 :  18:09:11  Show Profile  Reply with Quote
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

Edited by - Umar Strong on 07/31/2013 18:09:42
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000