replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)

by

You can try the following:

* Updated **

-- Test Data
DECLARE @YourTable TABLE(Product INT, Timestamp DATETIME, Price NUMERIC(16,4))

INSERT INTO @YourTable
SELECT 5678, '20080101 12:00:00', 12.34
UNION ALL
SELECT 5678, '20080101 12:01:00', NULL
UNION ALL
SELECT 5678, '20080101 12:02:00', NULL
UNION ALL
SELECT 5678, '20080101 12:03:00', 23.45
UNION ALL
SELECT 5678, '20080101 12:04:00', NULL

;WITH CTE AS
(
    SELECT *
    FROM @YourTable
)

-- Query
SELECT A.Product, A.Timestamp, ISNULL(A.Price,B.Price) Price
FROM CTE A
OUTER APPLY (   SELECT TOP 1 *
                FROM CTE 
                WHERE Product = A.Product AND Timestamp < A.Timestamp
                AND Price IS NOT NULL
                ORDER BY Product, Timestamp DESC) B

--Results
Product Timestamp   Price
5678    2008-01-01 12:00:00.000 12.3400
5678    2008-01-01 12:01:00.000 12.3400
5678    2008-01-01 12:02:00.000 12.3400
5678    2008-01-01 12:03:00.000 23.4500
5678    2008-01-01 12:04:00.000 23.4500

More Related Contents:

  1. Using GROUP BY with FIRST_VALUE and LAST_VALUE
  2. Function to Calculate Median in SQL Server
  3. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  4. Create a view with ORDER BY clause
  5. How to run the same query on all the databases on an instance?
  6. SQL Server: Difference between PARTITION BY and GROUP BY
  7. SQL Update with row_number()
  8. How to write UTF-8 characters using bulk insert in SQL Server?
  9. Is a single SQL Server statement atomic and consistent?
  10. Connecting to MS SQL Server with Windows Authentication using Python?
  11. determining the character set of a table / database?
  12. How to query for Xml values and attributes from table in SQL Server?
  13. A connection was successfully established with the server, but then an error occurred during the login process. (Error Number: 233)
  14. Should every User Table have a Clustered Index?
  15. cross apply xml query performs exponentially worse as xml document grows
  16. SQL Server Configuration Manager cannot be found
  17. Compute MD5 hash of a UTF8 string
  18. Sql Server replication requires the actual server name to make a connection to the server
  19. Convert a string with ‘YYYYMMDDHHMMSS’ format to datetime
  20. Convert from DateTime to INT
  21. SQL Server 2008 Spatial: find a point in polygon
  22. Local and global temporary tables in SQL Server
  23. SQL Server add auto increment primary key to existing table
  24. SQL Server: UPDATE a table by using ORDER BY
  25. EF5: Cannot attach the file ‘{0}’ as database ‘{1}’
  26. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  27. Transpose rows and columns with no aggregate
  28. How to reuse calculated columns avoiding duplicating the sql statement
  29. Unable to find the requested .Net Framework Data Provider. It may not be installed. – when following mvc3 asp.net tutorial
  30. How to choose the clustered index in SQL Server?

Leave a Comment