Вы можете использовать функцию recode
из dplyr
.
df <- iris %>%
mutate(Species = recode(Species, setosa = "SETOSA",
versicolor = "VERSICOLOR",
virginica = "VIRGINICA"
)
)
Вот подход к решению вашего вопроса.
Для начала давайте объединим 5 таблиц. Хитрость здесь в том, чтобы LEFT JOIN
из таблицы hleaves
проверить, перекрывает ли интервал отпуска с фильтруемой неделей; классический способ заключается в использовании выражения вроде: l.dateStart < @weekEnd AND l.dateEnd < @weekStart
:
SELECT *
FROM
users u
INNER JOIN teams t ON t.team_id = u.link_team_id
INNER JOIN hreg r ON r.hreg_id = u.link_hreg_id
LEFT JOIN hleave l ON l.user = u.user_id AND l.dateStart < @weekEnd AND l.dateEnd < @weekStart
LEFT JOIN leave_codes lc ON lc.code_id = l.leaveCode
Имея этот набор данных, мы можем теперь использовать условное агрегирование для получения желаемых результатов. Например, чтобы проверить статус во вторник, мы должны сделать:
MAX(
CASE
-- employee on leave
WHEN DATE_ADD(@weekStart, INTERVAL 1 DAY) BETWEEN l.dateStart AND l.dateEnd THEN lc.codeName
-- employee on schedule for that day
WHEN 2 BETWEEN h.weekStart AND h.weekEnd THEN 'working'
-- employee not on schedule for that day
ELSE 'not working'
END
) AS Tuesday
Вы можете повторять это выражение для каждого дня недели, увеличивая счетчики. Вот запрос с понедельника по среду:
SELECT
u.firstName,
MAX(
CASE
WHEN @weekStart BETWEEN l.dateStart AND l.dateEnd THEN lc.codeName
WHEN 2 BETWEEN h.weekStart AND h.weekEnd THEN 'working'
ELSE 'not working'
END
) AS Monday,
MAX(
CASE
WHEN DATE_ADD(@weekStart, INTERVAL 1 DAY) BETWEEN l.dateStart AND l.dateEnd THEN lc.codeName
WHEN 2 BETWEEN h.weekStart AND h.weekEnd THEN 'working'
ELSE 'not working'
END
) AS Tuesday,
MAX(
CASE
WHEN DATE_ADD(@weekStart, INTERVAL 2 DAY) BETWEEN l.dateStart AND l.dateEnd THEN lc.codeName
WHEN 3 BETWEEN h.weekStart AND h.weekEnd THEN 'working'
ELSE 'not working'
END
) AS Wednesday
-- other days go here
FROM
users u
INNER JOIN teams t ON t.team_id = u.link_team_id
INNER JOIN hreg r ON r.hreg_id = u.link_hreg_id
LEFT JOIN hleave l ON l.user = u.user_id AND l.dateStart < @weekEnd AND l.dateEnd < @weekStart
LEFT JOIN leave_codes lc ON lc.code_id = l.leaveCode
GROUP BY
u.user_id,
u.firstName