Is a cursor/recursion the only possible option here? Set based logic based on certain lag conditions

I’m using SQL Server, I have a table with 3 columns (timeseries) data, with date, hour beginning, AwardStatus.

The award status for the most part is randomly generated. There can be two options, Awarded or Not Awarded.

However, the business requirement is that we MUST print ‘NotAwarded’ for 3 consecutive rows if the status is NotAwarded, and 4 consecutive rows if the status is Awarded.

Goal: a new column ShouldBe details.

Once it meets the minimum requirements, then it checks that the current row’s AwardStatus and continues to overwrite the logic.

Question: Is that possible in SQL without any kind of cursor/looping?

The picture in below as an example.

Here’s an example:

AwardStatusMinimum  3       
AwardStatusMaximum  4       

Date    Hour    AwardStatus ShouldBe
--------------------------------------
1/1/2019    1   NotAwarded  NotAwarded
1/1/2019    2   NotAwarded  NotAwarded
1/1/2019    3   Awarded     NotAwarded
1/1/2019    4   Awarded     Awarded
1/1/2019    5   NotAwarded  Awarded
1/1/2019    6   NotAwarded  Awarded
1/1/2019    7   Awarded     Awarded
1/1/2019    8   NotAwarded  NotAwarded
1/1/2019    9   Awarded     NotAwarded
1/1/2019    10  Awarded     NotAwarded

Answer

Since recursion was mentioned.

Here’s a solution that uses a recursive CTE.

Sample data:

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');

Query:

;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]

GO

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    

A test on db<>fiddle here