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

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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