SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TimeCards 
(
    [Counter] [int] IDENTITY(1,1) NOT NULL,
    EmployeeID nvarchar(50),
    Date DateTime,
    Lunch decimal(10,2),
    TotalTime decimal(10,2)
)

INSERT INTO TimeCards (EmployeeID, Date, Lunch, TotalTime)
VALUES ('1001', '2021-02-04T00:00:00.000', Null, 8) 
, ('136', '2021-02-04T00:00:00.000', Null, 4)
, ('136', '2021-02-04T00:00:00.000', Null, 4)
, ('418', '2021-02-04T00:00:00.000', Null, 5)
, ('418', '2021-02-04T00:00:00.000', Null, 5)
, ('511', '2021-02-04T00:00:00.000', Null, 5)
, ('511', '2021-02-04T00:00:00.000', Null, 6)
, ('456', '2021-02-04T00:00:00.000', 0.5, 6)
, ('456', '2021-02-04T00:00:00.000', Null,  6)
9 rows affected

/*
WITH CTE AS(
    SELECT TC.EmployeeID,
           TC.[Date], --If this is a date, why is it a datetime?
           TC.Lunch,
           TC.TotalTime,
           SUM(TC.TotalTime) OVER (PARTITION BY TC.EmployeeID, TC.Date) AS TotalHours,
           ROW_NUMBER() OVER (PARTITION BY TC.EmployeeID, TC.Date ORDER BY Counter DESC) AS RN
    FROM dbo.TimeCards TC)
UPDATE CTE
SET Lunch = CASE WHEN TotalHours BETWEEN 5.501 AND 11 THEN .5
                 WHEN TotalHours BETWEEN 11.01 AND 16 THEN 1
                 WHEN TotalHours >= 16.01 THEN 1.5
            END 
WHERE TotalHours > 5.5
  AND Lunch IS NULL
  AND RN = 1;
*/
WITH CTE AS(
    SELECT TC.EmployeeID,
           TC.[Date], --If this is a date, why is it a datetime?
           TC.Lunch,
           TC.TotalTime,
           SUM(TC.TotalTime) OVER (PARTITION BY TC.EmployeeID, TC.Date) AS TotalHours,
           MAX(Lunch) OVER (PARTITION BY TC.EmployeeID, TC.Date) AS MaxLunch,
           ROW_NUMBER() OVER (PARTITION BY TC.EmployeeID, TC.Date ORDER BY Counter DESC) AS RN
    FROM dbo.TimeCards TC)
UPDATE CTE
SET Lunch = CASE WHEN TotalHours BETWEEN 5.501 AND 11 THEN .5
                 WHEN TotalHours BETWEEN 11.01 AND 16 THEN 1
                 WHEN TotalHours >= 16.01 THEN 1.5
            END 
WHERE TotalHours > 5.5
  AND MaxLunch IS NULL
  AND RN = 1;
Warning: Null value is eliminated by an aggregate or other SET operation.
4 rows affected

SELECT *
FROM dbo.TimeCards


Counter
EmployeeID
Date
Lunch
TotalTime


1
1001
2021-02-04 00:00:00.000
0.50
8.00

2
136
2021-02-04 00:00:00.000
null
4.00

3
136
2021-02-04 00:00:00.000
0.50
4.00

4
418
2021-02-04 00:00:00.000
null
5.00

5
418
2021-02-04 00:00:00.000
0.50
5.00

6
511
2021-02-04 00:00:00.000
null
5.00

7
511
2021-02-04 00:00:00.000
0.50
6.00

8
456
2021-02-04 00:00:00.000
0.50
6.00

9
456
2021-02-04 00:00:00.000
null
6.00


DROP TABLE dbo.TimeCards;

Counter	EmployeeID	Date	Lunch	TotalTime
1	1001	2021-02-04 00:00:00.000	0.50	8.00
2	136	2021-02-04 00:00:00.000	null	4.00
3	136	2021-02-04 00:00:00.000	0.50	4.00
4	418	2021-02-04 00:00:00.000	null	5.00
5	418	2021-02-04 00:00:00.000	0.50	5.00
6	511	2021-02-04 00:00:00.000	null	5.00
7	511	2021-02-04 00:00:00.000	0.50	6.00
8	456	2021-02-04 00:00:00.000	0.50	6.00
9	456	2021-02-04 00:00:00.000	null	6.00