SQL Server 2017

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

CREATE TABLE Table1 (
  [Date] DATETIME NOT NULL, 
  [Hour] INT NOT NULL, 
  [AwardStatus] VARCHAR(10)
);
INSERT INTO Table1
 ([Date], [Hour], [AwardStatus])
VALUES
 ('2019-01-01', 1, 'NotAwarded'),      
 ('2019-01-01', 2, 'NotAwarded'),     
 ('2019-01-01', 3, 'Awarded'),
 ('2019-01-01', 4, 'Awarded'),
 ('2019-01-01', 5, 'NotAwarded'),
 ('2019-01-01', 6, 'NotAwarded'),
 ('2019-01-01', 7, 'Awarded'),
 ('2019-01-01', 8, 'NotAwarded'),
 ('2019-01-01', 9, 'Awarded'),
 ('2019-01-01', 10, 'Awarded');
10 rows affected

;with CTE_DATA AS 
(
  select *
  , dense_rank() 
    over (order by cast([Date] as date)) as grp
  , row_number() 
    over (partition by cast([Date] as date) order by [Hour]) as rn
  from Table1
)
, RCTE_AWARDS as
(
  select [Date], [Hour]
  , AwardStatus
  , grp
  , rn
  , 1 as Lvl
  , AwardStatus AS CalcStatus
  from CTE_DATA
  where rn = 1
  
  union all
  
  select t.[Date], t.[Hour]
  , t.AwardStatus
  , t.grp
  , t.rn
  , case
    when (c.lvl < 3)
      or (c.lvl < 4 and c.CalcStatus = 'Awarded')
    then c.lvl+1
    else 1
    end
  , case 
    when (c.lvl = 3 and c.CalcStatus = 'NotAwarded')
      or (c.lvl = 4)
    then t.AwardStatus
    else c.CalcStatus
    end
  from RCTE_AWARDS c
  join CTE_DATA t 
    on t.grp = c.grp
   and t.rn = c.rn + 1
)
select [Date], [Hour], AwardStatus
, CalcStatus AS NewAwardStatus
from RCTE_AWARDS
order by [Date], [Hour]


Date
Hour
AwardStatus
NewAwardStatus


2019-01-01 00:00:00.000
1
NotAwarded
NotAwarded

2019-01-01 00:00:00.000
2
NotAwarded
NotAwarded

2019-01-01 00:00:00.000
3
Awarded
NotAwarded

2019-01-01 00:00:00.000
4
Awarded
Awarded

2019-01-01 00:00:00.000
5
NotAwarded
Awarded

2019-01-01 00:00:00.000
6
NotAwarded
Awarded

2019-01-01 00:00:00.000
7
Awarded
Awarded

2019-01-01 00:00:00.000
8
NotAwarded
NotAwarded

2019-01-01 00:00:00.000
9
Awarded
NotAwarded

2019-01-01 00:00:00.000
10
Awarded
NotAwarded

Date	Hour	AwardStatus	NewAwardStatus
2019-01-01 00:00:00.000	1	NotAwarded	NotAwarded
2019-01-01 00:00:00.000	2	NotAwarded	NotAwarded
2019-01-01 00:00:00.000	3	Awarded	NotAwarded
2019-01-01 00:00:00.000	4	Awarded	Awarded
2019-01-01 00:00:00.000	5	NotAwarded	Awarded
2019-01-01 00:00:00.000	6	NotAwarded	Awarded
2019-01-01 00:00:00.000	7	Awarded	Awarded
2019-01-01 00:00:00.000	8	NotAwarded	NotAwarded
2019-01-01 00:00:00.000	9	Awarded	NotAwarded
2019-01-01 00:00:00.000	10	Awarded	NotAwarded