本质上，我试图根据用户的轮班和输入的天数来计算用户可以拥有的总小时数。
示例：2020年9月13日开始轮班。我知道这是第一周，周日(根据其他计算(。所以我需要在ROTA表中从周日开始取2，然后从第2周开始取3，2，1，1，2。
总共7天：第1周=周日第2周=周一、周二、周三、周四、周五、周六
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 |
+------+-----+-----+-----+-----+-----+-----+-----+
上面的数字存储在Shifts表中。因此，对于我的7天来说，这将是2、3、2、1、1、2=这7天的51.5个小时。
SHIFTS Table
+-------+-------+
| SHIFT | HOURS |
+-------+-------+
| 1 | 8.5 |
| 2 | 6 |
| 3 | 8 |
+-------+-------+
我正在做一个WHILE循环来获得所需的周和列。所以对于上面的例子，我只需要ROTA表中的SUN列。下一个循环将给我MON-SAT.
一开始我试图把这两行合并在一起，然后我可以做一些计数。所以有3x SHIFT 2，1x SHIFT 3和3x SHIFT 1。然后我可以得到总小时数，但不确定如何做到这一点。
在我的查询完成的那一刻，我得到了以下两行：
LOOP 1:
+-----+
| SUN |
+-----+
| 2 |
+-----+
LOOP 2:
+-----+-----+-----+-----+-----+-----+
| MON | TUE | WED | THU | FRI | SAT |
+-----+-----+-----+-----+-----+-----+
| 3 | 2 | 1 | 1 | 1 | 2 |
+-----+-----+-----+-----+-----+-----+
我已经把我的查询精简了一点，但这是它的要点：
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
正如你所看到的，我快到了。我只是不知道如何获取我的结果，并从SHIFTS表中选择小时数。如有任何帮助，我们将不胜感激。
ROTA表对我们人类来说可读性很强，但对于一个不知道一周的星期日之后是下一周的周一(或者我们认为表中的值按照mon tue wed thu fri sat sun的顺序与值相邻(的DBMS来说就不那么容易了。
您可以将该表转换为机器可读的形式，为第1周提供第1、2、3、4、5、6、7天的数字，然后为第2周提供第8、9、10、11、12、13、14天的数字等。计算天数的公式为：day_number = day_of_week + (7 * (week - 1))。
查询：
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;
当然，如果您更改了数据模型以匹配我的特别日期视图，那么查询将减少到上述查询的最后五行。
更新：
在请求评论中，你说你想再次从第5周继续到第1周。您可以使用模运算从第35天到第1天(next_daynum = daynum % 35 + 1(。但因此，这就变成了一个迭代过程，ROTA周甚至可以在计算中多次使用。迭代是用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;
(不幸的是，SQL Server要求递归CTE中的强制转换与列的确切数据类型相匹配。(
演示：https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=351ef091ddb80acf27e209595e2d3f9e