Refer to the calculated column within the column

Question

Column ORtg_home contains values differ from zeros and zeros. My purpose is to create a new column (X) in which there are only values without zeros. (After the first home game. If it would be a zero then give back the last value which is not zero from ORtg_home).

Team	Game_total	Home_away	Game_home	Game_away	ORtg_avg	ORtg_home	x
ATL	1	away	0	1	100	0	0
ATL	2	home	1	1	101	102	102
ATL	3	away	1	2	104	0	102
ATL	4	away	1	3	106	0	102

I tried the code below:

  SELECT 
      [ORtg_home]
      ,(CASE WHEN [Home_away] = 'home' THEN ([ORtg_home])
    ELSE 
        LAG(ORtg_home, 1) OVER (PARTITION BY Team ORDER BY [Game_total] ASC, Home_away ASC) END) as XXX
  FROM [table1]

This gives 102 for the 3rd game, however the 4th game value in column x is still 0. Teams may vary. Currently table does not contain the date of the game, but it can be added.

CREATE TABLE [test1]
(
      [Team]            VARCHAR(3)
      ,[Game_total]     INT
      ,[Home_away]      VARCHAR(4)
      ,[Game_home]      INT
      ,[Game_away]      INT


      ,[ORtg_avg]       FLOAT
      ,[ORtg_home]      FLOAT
      ,[x]              FLOAT

      )

INSERT INTO [test1]
          [Team], [Game_total], [Home_away], [Game_home], [Game_away, [ORtg_avg] ,[ORtg_home]       
            
VALUES (ATL, 1, 'away', 0, 1, 100, 0)
VALUES (ATL, 2, 'home', 1, 1, 101, 102)
VALUES (ATL, 3, 'away', 1, 2, 104, 0)
VALUES (ATL, 4, 'away', 1, 3, 106, 0)

Answer 1

This would be easier if LAST_VALUE supported the IGNORE_NULLS clause as then you could just convert the zeroes to NULL and use that.

In the absence of this you can use the approach below (fiddle)

WITH T AS
(
SELECT  *, 
        MAX(FORMAT(Game_total,'D10') + FORMAT(NULLIF(ORtg_home,0),'R')) OVER (PARTITION BY Team ORDER BY [Game_total] ASC) AS _x
FROM test1
)
SELECT Team,
       Game_total,
       Home_away,
       Game_home,
       Game_away,
       ORtg_avg,
       ORtg_home,
       COALESCE(SUBSTRING(_x, 11, 100), 0E0) AS x
FROM   T