# Carry forward non-null value with a windowing function? [duplicate]

In SQL server, I often see this referred to as a "smear" and there's blog posts about using a "quirky update" to do it. But how would I do it with windowing functions. For example, if I have:

``````name     date   happiness
----     ----   ---------
John     1/3    20
John     1/4    NULL
John     1/5    NULL
John     1/6    NULL
John     1/7    18
Mary     1/2    35
Mary     1/3    NULL
Mary     1/4    -15
Mary     1/6    NULL
Mary     1/7    0
``````

Is there a way to do something like:

``````SELECT
[name],
[date],
CARRY([happiness]) OVER (PARTITION BY [name] ORDER BY [date])
``````

So that the intermediate results look like:

``````name     date   happiness
----     ----   ---------
John     1/3    20
John     1/4    20
John     1/5    20
John     1/6    20
John     1/7    18
Mary     1/2    35
Mary     1/3    35
Mary     1/4    -15
Mary     1/6    -15
Mary     1/7    0
``````

`CARRY` isn't a real function clearly, just want that functionality.

You may also use `APPLY()` operator to get the `"last"` `not null` `happiness`

``````SELECT *
FROM   [Data] d
CROSS APPLY
(
SELECT TOP 1 happiness
FROM   [Data] x
WHERE  x.[name]  = d.[name]
AND    x.[date] <= d.[date]
AND    x.happiness IS NOT NULL
ORDER BY x.[date] DESC
) h
``````
• Thanks! This is the only solution that provides decent performance when working with multi-billion row tables. Commented Feb 21, 2020 at 20:57

You can use a recursive CTE and COALESCE to return the most recent non-NULL value for Happiness. See this fiddle for a working example.

In the example, I am first using a CTE to create a pseudo ID column for use in the recursive CTE. Then in the recursive CTE, we select the ID = 1 row (the earliest row) as the anchor and recursively select the next row by ID for each name group. The COALESCE function picks the previous value if the current value is NULL for happiness.

Query:

``````;WITH DataC AS(
SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY date) AS ID,
[name],
[date],
[happiness]
FROM Data
), CTE AS (
SELECT ID,
[name],
[date],
[happiness]
FROM DataC
WHERE ID = 1
UNION ALL
SELECT d.ID,
d.[name],
d.[date],
COALESCE(d.[happiness], c.happiness) AS happiness
FROM DataC d
INNER JOIN CTE c ON c.Name = d.Name AND c.ID = d.ID - 1
)

SELECT *
FROM CTE
ORDER BY name, date
``````

Result:

``````ID  name    date                        happiness
-------------------------------------------------
1   John    2020-01-03 00:00:00.000     20
2   John    2020-01-04 00:00:00.000     20
3   John    2020-01-05 00:00:00.000     20
4   John    2020-01-06 00:00:00.000     20
5   John    2020-01-07 00:00:00.000     18
1   Mary    2020-01-01 00:00:00.000     35
2   Mary    2020-01-02 00:00:00.000     35
3   Mary    2020-01-03 00:00:00.000     -15
4   Mary    2020-01-04 00:00:00.000     -15
5   Mary    2020-01-05 00:00:00.000     0
``````
``````SELECT t1.name,
t1.date,
COALESCE(t1.happiness, t2.happiness) happiness
FROM sourcetable t1
LEFT JOIN sourcetable t2 ON t1.name = t2.name
AND t1.date > t2.date
AND t2.happiness IS NOT NULL
AND NOT EXISTS ( SELECT NULL
FROM sourcetable t3
WHERE t1.name = t3.name
AND t1.date > t3.date
AND t3.date > t2.date
AND t3.happiness IS NOT NULL);
``````

fiddle