Select from another table in WHILE loop based on results

Question

Essentially I'm trying to calculate the total amount of hours a user can have based on their shift and number of days entered.

Example: Start shift on 13th September 2020. I know this is Week 1, Sunday (based on other calculations). So I need to take the 2 from Sunday and then 3, 2, 1, 1, 1, 2 from week 2 in the ROTA table.

In total 7 days: Week 1 = Sunday Week 2 = Mon, Tues, Wed, Thurs, Fri, Sat

 ROTA table
+------+-----+-----+-----+-----+-----+-----+-----+
| WEEK | MON | TUE | WED | THU | FRI | SAT | SUN |
+------+-----+-----+-----+-----+-----+-----+-----+
|    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 |
+------+-----+-----+-----+-----+-----+-----+-----+

The numbers above are stored in a Shifts table. So for my 7 days it would be 2, 3, 2, 1, 1, 1, 2 = 51.5 total hours for the 7 days.

SHIFTS Table
+-------+-------+
| SHIFT | HOURS |
+-------+-------+
|     1 | 8.5   |
|     2 | 6     |
|     3 | 8     |
+-------+-------+

I'm doing a WHILE loop to get the Week and columns needed. So for the example above I would only need the SUN column from ROTA table. Next loop will give me MON - SAT.

At first I was trying to merge the two rows together, then I could do some sort of count. So there is 3x SHIFT 2, 1x SHIFT 3 and 3x SHIFT 1. Then I can get the total hours but not sure how to do that.

At the moment when my query has finished I end up with the below two rows:

LOOP 1:
+-----+
| SUN |
+-----+
|   2 |
+-----+

LOOP 2:
+-----+-----+-----+-----+-----+-----+
| MON | TUE | WED | THU | FRI | SAT |
+-----+-----+-----+-----+-----+-----+
|   3 |   2 |   1 |   1 |   1 |   2 |
+-----+-----+-----+-----+-----+-----+

I've stripped my query back a fair bit but this is the gist of it:

WHILE @cnt <= @totalDays
BEGIN

IF @dayOfWeek = 1 SET @columnList = 'SUN' ELSE 
IF @tempTotalDays >= 7 SET @columnList = 'MON, TUE, WED, THU, FRI, SAT, SUN' ELSE
IF @tempTotalDays = 6  SET @columnList = 'MON, TUE, WED, THU, FRI, SAT' ELSE
IF @tempTotalDays = 5  SET @columnList = 'MON, TUE, WED, THU, FRI' ELSE
IF @tempTotalDays = 4  SET @columnList = 'MON, TUE, WED, THU' ELSE
IF @tempTotalDays = 3  SET @columnList = 'MON, TUE, WED' ELSE
IF @tempTotalDays = 2  SET @columnList = 'MON, TUE' ELSE
IF @tempTotalDays = 1  SET @columnList = 'MON'

SET @sqlCommand = 'select '+ @columnList +' from dbo.ROTA
where WEEK = @rotaWeek'

EXEC sp_executesql @sqlCommand, N'@rotaWeek nvarchar(75), @rotaWeek = @rotaWeek

END;
GO

As you can see I'm almost there. I just don't know how to take my results and select the hours from the SHIFTS table. Any help would be appreciated.

Answer 1

The ROTA table is very readable for us humans, but not so much for a DBMS that doesn't know that a week's Sunday is followed by the next week's Monday (or that we consider the values in the table adjacent values in the order mon-tue-wed-thu-fri-sat-sun at all).

You can transform the table into a readable form for the machine, giving the days numbers 1,2,3,4,5,6,7 for week #1, then 8,9,10,11,12,13,14 for week #2, etc. The formula to calculate a day number is then: day_number = day_of_week + (7 * (week - 1)).

The query:

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
)
select sum(s.hours)
from days d
join shifts s on s.shift = d.shift
where d.daynum between @dayOfWeek + (7 * (@rotaWeek - 1))
                   and @dayOfWeek + (7 * (@rotaWeek - 1)) + @totalDays - 1;

Of course, if you changed your data model to match my ad-hoc days view, then the query would reduce to the mere last five lines of above query.

UPDATE:

In the request comments you say you want to continue week #5 with week #1 again. You can use a modulo operation to get from day #35 to day #1 (next_daynum = daynum % 35 + 1). But thus this becomes an iterative process where a ROTA week can even be used more than once in the calculation. Iterations are done with recursive queries in SQL:

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;

(Unfortunately, SQL Server requires the cast in the recursive CTE to match the column's exact data type.)

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=351ef091ddb80acf27e209595e2d3f9e