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

Question

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

Hi, welcome to SO. No links to pictures, type the data sample and schema for us to help. See https://stackoverflow.com/help/how-to-ask — Horaciux, Jan 24 '20 at 20:22
I think it could be a solution of your problem https://stackoverflow.com/questions/21784189/lag-with-condition-in-sql-server — Bartłomiej Staszkiewicz, Jan 24 '20 at 20:27
we expect something like this: http://sqlfiddle.com/#!18/2a02b/1 — Horaciux, Jan 24 '20 at 20:30
Added to the original post, thanks horaciux, will make sure that's how I ask questions going forward. — Chemifesto, Jan 24 '20 at 20:35
For each separate record you must investigate all previous record for to calculate what status would be. Records processing order is not defined. So no way to avoid iteration. — Akina, Jan 24 '20 at 21:01
I agree, with Akina, too complex to avoid iteration. You don't have to use a cursor, a while loop would work just setting the min hour/day in variables. I did like the proposed solution's use of lag. I hate that it got a down vote. I never think about the windowing functions I'm always impressed when somebody uses them. — Obie, Jan 24 '20 at 21:24
@Chemifesto . . . Why not just assign groups of 3 and 4, disregarding the current values? — Gordon Linoff, Jan 24 '20 at 21:47

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

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

score 0 · Answer 2 · answered Jan 24 '20 at 22:29

This allows you to do it without using cursor.

 declare @date date
 declare @hour int
 declare @CurrentStatus varchar(50)

 set @CurrentStatus=''

 while exists(select * from Awards where ShouldBe is null)
 begin
     select top 1 @date=[date], @hour=[hour] , @CurrentStatus=AwardStatus
     from Awards 
     where [ShouldBe] is null 
     order by [date],[hour]

     if(@CurrentStatus='Awarded')
     begin
         update top(4) Awards
         set ShouldBe=@CurrentStatus
         where [date]=@date and [hour]>=@hour
     end
     else 
         begin
         update top(3) Awards
         set ShouldBe=@CurrentStatus
         where [date]=@date and [hour]>=@hour
     end

 end

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

2 Answers2