MS SQL : Need to get row wise value from cell for similar rows, Code not working correctly

I have 100s of excel sheets(CSV files) I need to use for the historical load. I am going to load all the CSVs to a stage table using ETL.

I am trying to get the row-wise value from the cell for similar rows. Please see the snapshot in the link.

Here is a sample creation of table script, else please refer to above link for full sample data

DROP TABLE #c
CREATE TABLE #C (col varchar(255) ,rowid int,col1 varchar(255))
INSERT INTO #C values('Wednesday, March 06, 2019 - Weather: TY: 15‹C', 1,NULL)
INSERT INTO #C values('a', 2,NULL)
INSERT INTO #C values('b', 3,NULL)
INSERT INTO #C values('c', 4,NULL)


INSERT INTO #C values('Thursday, March 07, 2019 - Weather: TY: 12‹C', 5,NULL)
INSERT INTO #C values('a', 6,NULL)
INSERT INTO #C values('b', 7,NULL)
INSERT INTO #C values('c', 8,NULL)

INSERT INTO #C values('Friday, March 08, 2019 - Weather: TY: 12‹C', 9,NULL)
INSERT INTO #C values('a', 10,NULL)
INSERT INTO #C values('b', 11,NULL)
INSERT INTO #C values('c', 12,NULL)

snapshot of the output here: black tick is correct, red crosses are wrong

I got the 1st three sections correct but others I got wrong. the red cross sections are wrong as I need to get the date in the row section.

ALTER TABLE dbo.[sql GET VALUES] ADD rowId INT IDENTITY(1, 1)
ALTER TABLE dbo.[sql GET VALUES] ADD RequiredColumn VARCHAR(255) NULL

UPDATE a
SET requiredColumn = column1
FROM dbo.[sql GET VALUES] a
WHERE column1 LIKE '%Sunday%'
      OR column1 LIKE '%Monday%'
      OR column1 LIKE '%tuesday%'
      OR column1 LIKE '%wednesday%'
      OR column1 LIKE '%thursday%'
      OR column1 LIKE '%friday%'
      OR column1 LIKE '%saturday%'

IF OBJECT_ID('Tempdb.dbo.#temptable') IS NOT NULL
BEGIN
    DROP TABLE tempdb.dbo.#temptable
END

SELECT rowID,
       CASE
           WHEN CONVERT(VARCHAR(255), requiredcolumn) IS NOT NULL THEN
               CONVERT(VARCHAR(255), requiredcolumn)
           ELSE
       (
           SELECT MAX(requiredcolumn)
           FROM dbo.[sql GET VALUES]
           WHERE rowID <= t.rowID - 1
       )
       END AS requiredcolumn
INTO #temptable
FROM dbo.[sql GET VALUES] t

UPDATE a
SET a.requiredcolumn = i.requiredcolumn
-- select a.requiredcolumn, i.requiredcolumn,* 
FROM #temptable i
    INNER JOIN dbo.[sql GET VALUES] a
        ON i.rowID = a.rowID

SELECT *
FROM [sql GET VALUES]

I need to get the date info to the row for each row with date value along the column.

Answer

Using a CTE and a double use of the window function for MAX works for this.

And then it can be done in 1 update statement.

In the SQL, the first MAX calculates the highest previous/current Id with a valid column1 value.
The second MAX uses that calculated id to get the valid column1 value.

Sample data:

IF OBJECT_ID('tempdb..#C', 'U') IS NOT NULL DROP TABLE #C; 
CREATE TABLE #C (rowid int primary key identity(1,1), column1 VARCHAR(255), RequiredColumn VARCHAR(255));

insert into #C (column1) values
('Wednesday, March 06, 2019 - Weather: TY: 15‹C'),
('a'),
('b'),
('c'),
('Thursday, March 07, 2019 - Weather: TY: 12‹C'),
('d'),
('e'),
('f'),
('Friday, March 08, 2019 - Weather: TY: 12‹C'),
('g'),
('h'),
('i');

Update & select

;WITH CTE AS
(
    select *, 
     max(case when rowid = MaxPrevId then column1 end) over (partition by MaxPrevId) as column1calc
    from
    (
        select rowid, column1, RequiredColumn,
        max(case 
            when column1 LIKE '%sunday%'
              OR column1 LIKE '%monday%'
              OR column1 LIKE '%tuesday%'
              OR column1 LIKE '%wednesday%'
              OR column1 LIKE '%thursday%'
              OR column1 LIKE '%friday%'
              OR column1 LIKE '%saturday%' 
            then rowid 
            end) over (order by rowid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as MaxPrevId
        from #C
    ) q
)
UPDATE CTE 
SET RequiredColumn = column1calc;

select rowid, RequiredColumn 
from #C
order by rowid;

Returns:

rowid   RequiredColumn
1       Wednesday, March 06, 2019 - Weather: TY: 15‹C
2       Wednesday, March 06, 2019 - Weather: TY: 15‹C
3       Wednesday, March 06, 2019 - Weather: TY: 15‹C
4       Wednesday, March 06, 2019 - Weather: TY: 15‹C
5       Thursday, March 07, 2019 - Weather: TY: 12‹C
6       Thursday, March 07, 2019 - Weather: TY: 12‹C
7       Thursday, March 07, 2019 - Weather: TY: 12‹C
8       Thursday, March 07, 2019 - Weather: TY: 12‹C
9       Friday, March 08, 2019 - Weather: TY: 12‹C
10      Friday, March 08, 2019 - Weather: TY: 12‹C
11      Friday, March 08, 2019 - Weather: TY: 12‹C
12      Friday, March 08, 2019 - Weather: TY: 12‹C