Update value from previous row value using CTE

Question

This question is a variation of my older question here. I hope to explain the problem using an example. So

Sample Data
Here is sample data to work with:

DECLARE @Test TABLE (GID      int,             Seq     int, 
                     IsLive   bit,             Eff     date, 
                     Name     varchar(50),     Salary  decimal) 

INSERT INTO @Test VALUES (1, 1, 1, '01-08-2012', 'RTS', NULL)
INSERT INTO @Test VALUES (1, 2, 0, '01-09-2012', 'RTA', NULL)
INSERT INTO @Test VALUES (1, 3, 1, '01-10-2012', 'FSA', NULL)
INSERT INTO @Test VALUES (1, 4, 0, '01-11-2012',  NULL, NULL)
INSERT INTO @Test VALUES (1, 5, 1, '01-12-2012', 'FSA', NULL)
INSERT INTO @Test VALUES (2, 1, 1, '01-08-2012', 'RTS', NULL)
INSERT INTO @Test VALUES (2, 2, 0, '01-09-2012', 'RTA', NULL)
INSERT INTO @Test VALUES (2, 3, 1, '01-10-2012', 'FSA', NULL)
INSERT INTO @Test VALUES (2, 4, 0, '01-11-2012', 'GSM', NULL)
INSERT INTO @Test VALUES (2, 5, 1, '01-12-2012', 'FSA', NULL)
INSERT INTO @Test VALUES (3, 1, 1, '01-01-2012', 'FSA', NULL)
INSERT INTO @Test VALUES (3, 2, 0, '01-02-2012', NULL, NULL)
INSERT INTO @Test VALUES (4, 1, 1, '01-01-2012', NULL, NULL)
INSERT INTO @Test VALUES (4, 2, 0, '01-02-2012', 'FSA', NULL)
INSERT INTO @Test VALUES (4, 3, 0, '01-03-2012', NULL, NULL)
INSERT INTO @Test VALUES (5, 1, 0, '01-01-2012', NULL, NULL)
INSERT INTO @Test VALUES (5, 2, 1, '01-02-2012', 'LSI', NULL)
INSERT INTO @Test VALUES (5, 3, 0, '01-03-2012', NULL, NULL)
INSERT INTO @Test VALUES (6, 1, 1, '01-01-2012', NULL, NULL)
INSERT INTO @Test VALUES (6, 2, 0, '01-02-2012', 'LSI', NULL)
INSERT INTO @Test VALUES (6, 3, 1, '01-03-2012', NULL, NULL)

SELECT * FROM @Test

Following, are two sample result sets. Although the snippet shows inserts the point is to show what acceptable output set would look like:

Sample Output #1
In the data set below when a row has IsLive=0 then the value from its columns must over write the value of the same columns on the rows where IsLive=1 below it skipping NULL values. Ignore any IsLive=1 rows before the first IsLive=0 row.

    INSERT INTO @Test VALUES (1, 1, 1, '01-08-2012', 'RTS', NULL)
    INSERT INTO @Test VALUES (1, 2, 0, '01-09-2012', 'RTA', NULL)
    INSERT INTO @Test VALUES (1, 3, 1, '01-10-2012', 'RTA', NULL)
    INSERT INTO @Test VALUES (1, 4, 0, '01-11-2012',  NULL, NULL)
    INSERT INTO @Test VALUES (1, 5, 1, '01-12-2012', 'RTA', NULL)
    INSERT INTO @Test VALUES (2, 1, 1, '01-08-2012', 'RTS', NULL)
    INSERT INTO @Test VALUES (2, 2, 0, '01-09-2012', 'RTA', NULL)
    INSERT INTO @Test VALUES (2, 3, 1, '01-10-2012', 'RTA', NULL)
    INSERT INTO @Test VALUES (2, 4, 0, '01-11-2012', 'GSM', NULL)
    INSERT INTO @Test VALUES (2, 5, 1, '01-12-2012', 'GSM', NULL)
    INSERT INTO @Test VALUES (3, 1, 1, '01-01-2012', 'FSA', NULL)
    INSERT INTO @Test VALUES (3, 2, 0, '01-02-2012', NULL, NULL)
    INSERT INTO @Test VALUES (4, 1, 1, '01-01-2012', NULL, NULL)
    INSERT INTO @Test VALUES (4, 2, 0, '01-02-2012', 'FSA', NULL)
    INSERT INTO @Test VALUES (4, 3, 0, '01-03-2012', NULL, NULL)
    INSERT INTO @Test VALUES (5, 1, 0, '01-01-2012', NULL, NULL)
    INSERT INTO @Test VALUES (5, 2, 1, '01-02-2012', 'LSI', NULL)
    INSERT INTO @Test VALUES (5, 3, 0, '01-03-2012', NULL, NULL)
    INSERT INTO @Test VALUES (6, 1, 1, '01-01-2012', NULL, NULL)
    INSERT INTO @Test VALUES (6, 2, 0, '01-02-2012', 'LSI', NULL)
    INSERT INTO @Test VALUES (6, 3, 1, '01-03-2012', 'LSI', NULL)

    SELECT * FROM @Test AS FakedOutput_1

Sample Output #2
In the data set below when a row has IsLive=0 then the value from its columns must over write the value of the same columns on the rows where IsLive=1 below it. Columns with NULL values take the value from the previous row.

    INSERT INTO @Test VALUES (1, 1, 1, '01-08-2012', 'RTS', NULL)
    INSERT INTO @Test VALUES (1, 2, 0, '01-09-2012', 'RTA', NULL)
    INSERT INTO @Test VALUES (1, 3, 1, '01-10-2012', 'RTA', NULL)
    -- <- the following row is different from prev
    INSERT INTO @Test VALUES (1, 4, 0, '01-11-2012', 'RTA', NULL) 
    INSERT INTO @Test VALUES (1, 5, 1, '01-12-2012', 'RTA', NULL)
    INSERT INTO @Test VALUES (2, 1, 1, '01-08-2012', 'RTS', NULL)
    INSERT INTO @Test VALUES (2, 2, 0, '01-09-2012', 'RTA', NULL)
    INSERT INTO @Test VALUES (2, 3, 1, '01-10-2012', 'RTA', NULL)
    INSERT INTO @Test VALUES (2, 4, 0, '01-11-2012', 'GSM', NULL)
    INSERT INTO @Test VALUES (2, 5, 1, '01-12-2012', 'GSM', NULL)
    INSERT INTO @Test VALUES (3, 1, 1, '01-01-2012', 'FSA', NULL)
    INSERT INTO @Test VALUES (3, 2, 0, '01-02-2012', 'FSA', NULL)
    INSERT INTO @Test VALUES (4, 1, 1, '01-01-2012', NULL, NULL)
    INSERT INTO @Test VALUES (4, 2, 0, '01-02-2012', 'FSA', NULL)
    INSERT INTO @Test VALUES (4, 3, 0, '01-03-2012', 'FSA', NULL)
    INSERT INTO @Test VALUES (5, 1, 0, '01-01-2012', NULL, NULL)
    INSERT INTO @Test VALUES (5, 2, 1, '01-02-2012', 'LSI', NULL)
    INSERT INTO @Test VALUES (5, 3, 0, '01-03-2012', 'LSI', NULL)
    INSERT INTO @Test VALUES (6, 1, 1, '01-01-2012', NULL, NULL)
    INSERT INTO @Test VALUES (6, 2, 0, '01-02-2012', 'LSI', NULL)
    INSERT INTO @Test VALUES (6, 3, 1, '01-03-2012', 'LSI', NULL)

    SELECT * FROM @Test AS FakedOutput_2

Attempted Solution
Here is what I have come up with so far, but it fails my first test case (GID=1)

;WITH CTE AS ( 
    -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
    SELECT  T.GID, T.SEQ, T.IsLive, NULL cGuid, NULL cSEQ, 
            cast(0 as bit) cIsLive, T.Name, T.Salary 
    FROM    @Test T
    JOIN    @Test S ON T.GID = S.GID AND T.Seq = S.Seq AND S.IsLive = 0
    -- - - - - - - 
      UNION ALL 
    -- - - - - - - 
    SELECT  t.GID, t.SEQ, T.IsLive, c.GID cGID, c.Seq cSEQ, 
             c.IsLive cIsLive, ISNULL(C.Name, T.Name), 
             ISNULL(t.Salary, c.Salary) 
    FROM    CTE c 
    JOIN    @Test t ON    t.GID     = c.GID   AND 
                          t.Seq     > c.Seq   AND 
                          t.IsLive  = 1       AND 
                          c.IsLive  = 0
    -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
) 
--SELECT * FROM CTE ORDER BY CTE.GID, CTE.Seq
UPDATE  t 
SET     Name = c.Name, Salary = c.Salary
FROM    @Test t 
JOIN    CTE c     ON c.GID = t.GID AND c.Seq = t.SEQ
WHERE   C.cIsLive IS NOT NULL

Answer 1

It took me a while to realize that this question is only a minor variation of my previous question posted. Just some time away from the keyboard helped me see the answer! @GarethDs answer contributed toward this as well.

;WITH CTE AS ( 
    SELECT  T.GID, T.SEQ, T.IsLive, Name, Salary 
    FROM    @Test T
    JOIN    ( SELECT   GID, MIN(Seq) Seq 
              FROM     @Test 
              GROUP BY GID     
            ) S ON T.GID = S.GID AND T.Seq = S.Seq

    UNION ALL 

    SELECT t.GID, t.SEQ, T.IsLive,
           CASE WHEN T.IsLive = 0 THEN COALESCE(T.Name, C.Name) 
                ELSE COALESCE(C.Name, T.Name) END,
           CASE WHEN T.IsLive = 0 THEN COALESCE(T.Salary, C.Salary) 
                ELSE COALESCE(C.Salary, T.Salary) END 
    FROM   CTE C
    JOIN   @Test T ON T.GID = C.GID AND T.SEQ = C.SEQ+1 
) 
--SELECT * FROM CTE ORDER BY CTE.GID, CTE.Seq
UPDATE T 
SET    Name   = C.Name,  
       Salary =  C.Salary
FROM   @Test T
JOIN   CTE C ON C.GID = T.GID AND C.Seq = T.SEQ

Answer 2

Using APPLY works for your test case. The following gives the same as your solution 2

SELECT  t1.GID,
        t1.Seq,
        t1.IsLive,
        t1.Eff,
        CASE WHEN t1.IsLive = 0 THEN COALESCE(t1.Name, t3.Name) ELSE COALESCE(t3.Name, t1.Name) END AS Name,
        Salary
FROM    @Test T1
        OUTER APPLY
        (   SELECT  TOP 1 Name
            FROM    @Test T2
            WHERE   T2.GID = T1.GID
            AND     T2.Seq < T1.Seq
            AND     t2.IsLive = 0
            AND     t2.Name IS NOT NULL
            ORDER BY Seq DESC
        ) t3

EDIT

Just noticed that an UPDATE was required:

UPDATE  @Test
SET     Name = CASE WHEN t1.IsLive = 0 THEN COALESCE(t1.Name, t3.Name) ELSE COALESCE(t3.Name, t1.Name) END
FROM    @Test T1
        OUTER APPLY
        (   SELECT  TOP 1 Name
            FROM    @Test T2
            WHERE   T2.GID = T1.GID
            AND     T2.Seq < T1.Seq
            AND     t2.IsLive = 0
            AND     t2.Name IS NOT NULL
            ORDER BY Seq DESC
        ) t3

EDIT 2

I have altered the query inside the apply slightly, now it will try and find the closest row where live = 0 and the name is not null, if there are no rows where live = 1 (as in GID = 4) it will take the closest row where the name is not null:

UPDATE  @Test
SET     Name = CASE WHEN t1.IsLive = 0 THEN COALESCE(t1.Name, t3.Name) ELSE COALESCE(t3.Name, t1.Name) END
FROM    @Test T1
        OUTER APPLY
        (   SELECT  TOP 1 Name
            FROM    @Test T2
            WHERE   T2.GID = T1.GID
            AND     T2.Seq < T1.Seq
            AND     t2.Name IS NOT NULL
            ORDER BY t2.IsLive, Seq DESC
        ) t3