Replace Null Value With Previous Available Value in Row SQL Server Query

Function to REPLACE* last previous known value for NULL

Assuming the number you have is always increasing, you can use MAX aggregate over a window:

SELECT dt
     , country
     , cnt
     , MAX(cnt) OVER (PARTITION BY country ORDER BY dt)
  FROM #data

If the number may decrease, the query becomes a little bit more complex as we need to mark the rows that have nulls as belonging to the same group as the last one without a null first:

SELECT dt
     , country
     , cnt
     , SUM(cnt) OVER (PARTITION BY country, partition)
FROM (
    SELECT country
         , dt
         , cnt
         , SUM(CASE WHEN cnt IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY country ORDER BY dt) AS partition
    FROM #data
) AS d
ORDER BY dt

Here's a working demo on dbfiddle, it returns the same data with ever increasing amount, but if you change the number for 08-17 to be lower than that of 08-16, you'll see MAX(...) method producing wrong results.

SQL Server replace NULL value with last value from previous row

How about?

UPDATE mytable
SET date = date2
FROM mytable o
  CROSS APPLY
    (SELECT MAX(date) date2 FROM mytable i WHERE i.id=o.id group by id) ii
where o.date is null

Replace first and last row having null values or missing values with previous/next available value in Postgresql12

Unfortunately, Postgres doesn't have the ignore nulls option on lead() and lag(). In your example, you only need to borrow from the next row. So:

select t.*,
       coalesce(value, lag(value) over (order by id), lead(value) over (order by id)) as expected
from t;

If you had multiple NULLs in a row, then this is trickier. One solution is to define "groups" based on when a value starts or stops. You can do this with a cumulative count of the values -- ascending and descending:

select t.*,
       coalesce(value,
                max(value) over (partition by grp_before),
                max(value) over (partition by grp_after)
               ) as expected
from (select t.*,
             count(value) over (order by id asc) as grp_before,
             count(value) over (order by id desc) as grp_after
      from t
     ) t;

Here is a db<>fiddle.

How to replace null value with value from the next row

below query works in SQL Server:

;WITH CTE_Value
AS (
    SELECT R#, Value
    FROM @value AS T
    WHERE Value IS NOT NULL

    UNION ALL

    SELECT t.r#, c.Value
    FROM @value AS t
    INNER JOIN CTE_Value AS c ON t.r# + 1 = c.r#
    WHERE t.Value IS NULL
    )
SELECT *
FROM CTE_Value

UNION ALL

SELECT v.*
FROM @value AS v
LEFT JOIN CTE_value AS c ON v.r# = c.r#
WHERE c.r# IS NULL
ORDER BY r#

Replace null value with previous value without using ID in SQL Server 2008

Try this query. This will help you get your desired result set. This query is written in SQL Server 2008 R2.

WITH CTE AS
( SELECT id, number FROM tablea)
SELECT A.id, A.number, ISNULL(A.number,B.number) number
FROM CTE A
OUTER APPLY (SELECT TOP 1 *
FROM CTE 
WHERE id < a.id AND number IS NOT NULL
ORDER BY id DESC) B

You can try using LAG and LEAD functions in SQL Server 2012/

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

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

---

Related Topics