Replace NULLs with the last non-NULL value in the result set series (SQL Server 2008 R2)

Question

for SQL Server 2008 R2

I have a result set that looks like this (note [price] is numeric, NULL below is NULL, the result set is ordered using product_id and timestamp)

product timestamp price ------- ---------------- ----- 5678 2008-01-01 12:00 12.34 5678 2008-01-01 12:01 NULL 5678 2008-01-01 12:02 NULL 5678 2008-01-01 12:03 23.45 5678 2008-01-01 12:04 NULL 

I want to convert this to a result set that (essentially) copies a nonzero value from the last previous row to create a result set that looks like this:

 product timestamp price ------- ---------------- ----- 5678 2008-01-01 12:00 12.34 5678 2008-01-01 12:01 12.34 5678 2008-01-01 12:02 12.34 5678 2008-01-01 12:03 23.45 5678 2008-01-01 12:04 23.45 

I find no aggregation / window function that will allow me to do this (again, this is ONLY necessary for SQL Server 2008 R2.)

I was hoping to find an analytic aggregate function that does this for me, something like ...

 LAST_VALUE(price) OVER (PARTITION BY product_id ORDER BY timestamp) 

But I didn't seem to find a way to make the "cumulative last non-zero value" in the window (bind the window to previous lines, not to the entire section)

Besides creating a user-defined table function, is there a built-in function that would do this?

---

UPDATE:

This functionality appears to be available in CTP 'Denali', but not in SQL Server 2008 R2.

LAST_VALUE http://msdn.microsoft.com/en-us/library/hh231517%28v=SQL.110%29.aspx

I just expected it to be available in SQL Server 2008. It is available in Oracle (at least with 10gR2), and I can do something similar in MySQL 5.1 using a local variable.

http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/functions083.htm

Answer 1

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 

Answer 2

Try the following:

 ;WITH SortedData AS ( SELECT ProductID, TimeStamp, Price, ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY TimeStamp DESC) AS 'RowNum' FROM dbo.YourTable ) UPDATE SortedData SET Price = (SELECT TOP 1 Price FROM SortedData sd2 WHERE sd2.RowNum > SortedData.RowNum AND sd2.Price IS NOT NULL) WHERE SortedData.Price IS NULL 

Basically, CTE creates a list sorted by label (descending) - the newest. Whenever NULL is found, the next row containing the NOT NULL price will be found, and this value is used to update the row at the NULL price.

Answer 3

I have a table containing the following data. I want to update all zeros in salary columns with the previous value without accepting a zero value.

Table:

 id name salary 1 A 4000 2 B 3 C 4 C 5 D 2000 6 E 7 E 8 F 1000 9 G 2000 10 G 3000 11 G 5000 12 G 

here is a query that works for me.

 select a.*,first_value(a.salary)over(partition by a.value order by a.id) as abc from ( select *,sum(case when salary is null then 0 else 1 end)over(order by id) as value from test)a 

exit:

 id name salary Value abc 1 A 4000 1 4000 2 B 1 4000 3 C 1 4000 4 C 1 4000 5 D 2000 2 2000 6 E 2 2000 7 E 2 2000 8 F 1000 3 1000 9 G 2000 4 2000 10 G 3000 5 3000 11 G 5000 6 5000 12 G 6 5000 

Answer 4

@Manfred How to get lower as a result for a given input (means fill last_value, not first_value):

  id name salary 1 A 4000 2 B 2000 3 C 2000 4 C 2000 5 D 2000 6 E 1000 7 E 1000 8 F 1000 9 G 2000 10 G 3000 11 G 5000 12 G