SQL Server 2019

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


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
	Jul 25 2020 11:26:55 
	Copyright (C) 2019 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table rota (week int, mon int, tue int, wed int, thu int, fri int, sat int, sun int);
insert into rota (week, mon, tue, wed, thu, fri, sat, sun)
values (1, 2, 2, 2, 2, 2, 2, 2),
       (2, 3, 2, 1, 1, 1, 2, 1),
       (3, 1, 2, 1, 1, 1, 1, 1),
       (4, 1, 1, 1, 1, 2, 1, 1),
       (5, 1, 1, 2, 1, 1, 1, 1);
5 rows affected

create table shifts (shift int, hours decimal (5,1));
insert into shifts(shift, hours) values (1, 8.5), (2, 6), (3, 8);
3 rows affected

declare @rotaWeek int = 1;
declare @dayOfWeek int = 1;
declare @totalDays int = 70;

with days as
(
  select 1 + (7 * (week - 1)) as daynum, mon as shift from rota
  union all
  select 2 + (7 * (week - 1)) as daynum, tue as shift from rota
  union all
  select 3 + (7 * (week - 1)) as daynum, wed as shift from rota
  union all
  select 4 + (7 * (week - 1)) as daynum, thu as shift from rota
  union all
  select 5 + (7 * (week - 1)) as daynum, fri as shift from rota
  union all
  select 6 + (7 * (week - 1)) as daynum, sat as shift from rota
  union all
  select 7 + (7 * (week - 1)) as daynum, sun as shift from rota
)
, cte (daynum, remaining, hours) as
(
  select d.daynum, @totalDays - 1, s.hours
  from days d
  join shifts s on s.shift = d.shift
  where d.daynum = @dayOfWeek + (7 * (@rotaWeek - 1))
  union all                     
  select d.daynum, cte.remaining - 1, cast(cte.hours + s.hours as decimal(5,1))
  from cte
  join days d on d.daynum = cte.daynum % 35 + 1
  join shifts s on s.shift = d.shift
  where cte.remaining >= 1
)
select max(hours)
from cte;


(No column name)


534.0