Выбор из другой таблицы в цикле WHILE на основе результатов

По сути, я пытаюсь рассчитать общее количество часов, которое может иметь пользователь, исходя из их смены и количества введенных дней.

Пример: начало смены 13 сентября 2020 года. Я знаю, что это неделя 1, воскресенье (на основе других расчетов). Так что мне нужно взять 2 из воскресенья, а затем 3, 2, 1, 1, 1, 2 из недели 2 в таблице ROTA.

Всего 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 |
+------+-----+-----+-----+-----+-----+-----+-----+

Приведенные выше числа хранятся в таблице смен. Таким образом, для моих 7 дней это будет 2, 3, 2, 1, 1, 1, 2 = 51,5 полных часа за 7 дней.

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

Я делаю цикл WHILE, чтобы получить неделю и необходимые столбцы. Так что для приведенного выше примера мне понадобится только столбец SUN из таблицы ROTA. Следующий цикл даст мне ПН-СБ.

Сначала я пытался объединить две строки вместе, тогда я мог сделать какой-то подсчет. Итак, есть 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. Любая помощь будет оценена по достоинству.


sql-server tsql dynamic-sql sp-executesql
person Dereck    schedule 20.09.2020    source источник
comment
SQL — это язык, основанный на наборах. Вам не нужны петли. В худшем случае вы можете преобразовать все эти проверки в одно предложение CASE в части SELECT запроса.   -  person Panagiotis Kanavos    schedule 20.09.2020
comment
Спасибо за ответ. Если бы я просто использовал SELECT, как бы он работал, когда мне может понадобиться использовать одну и ту же строку WEEK несколько раз?   -  person Dereck    schedule 20.09.2020
comment
Когда вам придется рассматривать неделю несколько раз? Вы имеете в виду, что за неделей № 5 снова следует неделя № 1, а @totalDays может превышать 35 дней?   -  person Thorsten Kettner    schedule 21.09.2020
comment
Да исправить. Может быть более 35 дней, и после недели № 5 он возвращается к неделе № 1.   -  person Dereck    schedule 21.09.2020
comment
Ладно, мне это было непонятно. Я обновил свой ответ рекурсивным запросом, который делает это.   -  person Thorsten Kettner    schedule 21.09.2020

Ответы (1)


arrow_upward
1
arrow_downward

Таблица ROTA очень удобочитаема для нас, людей, но не настолько для СУБД, которая не знает, что за воскресеньем недели следует понедельник следующей недели (или что мы рассматриваем значения в таблице рядом со значениями в порядке понедельника). вт-ср-чт-пт-сб-вс вообще).

Вы можете преобразовать таблицу в удобочитаемый для машины вид, задав номера дней 1,2,3,4,5,6,7 для недели №1, затем 8,9,10,11,12,13,14 для недели №1. неделя № 2 и т. д. Тогда формула для расчета номера дня: 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=351ef091ddb80acf27e209e

person Thorsten Kettner    schedule 20.09.2020
comment
Спасибо тебе за это. Я попытаюсь включить это в свой первоначальный запрос, чтобы получить точные часы смены. - person Dereck; 21.09.2020