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.
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