Получить диапазон/количество дней на основе одного поля даты
У меня есть требование, где мне нужно будет получить количество дней, в которых работал сотрудник.
Сценарий 1
EmployeeId role effectiveFrom
1 A 1-Jan-2021
1 B 15-Jan-2021
На январь больше нет доступных ролей для роли A, поэтому количество дней для роли A будет равно 14.
Сценарий 2
EmployeeId role effectiveFrom
1 A 1-Jan-2021
На январь больше нет доступных ролей, поэтому количество дней для роли А будет равно 31, т. е. весь январь. За февраль я ожидаю получить 28, так как роль будет действовать и весь февраль.
Сценарий 3
EmployeeId role effectiveFrom
1 A 1-Jan-2021
1 B 15-Jan-2021
1 A 25-Jan-2021
Чтобы получить количество дней для роли A, логика будет следующей:
- с 1 по 15 - 14 дней.
- С 25 по 31 (31 января) будет 6 дней.
- 14 + 6 = 20 дней
Запрос, который я придумал до сих пор, таков:
SELECT
DATEDIFF(MAX(effectiveFrom),
IF(MIN(effectiveFrom) = MAX(effectiveFrom),
MIN(effectiveFrom),
MIN(effectiveFrom))) + 1 daysWorked
FROM
EmployeeRoles
WHERE grade = 'A'
GROUP BY `employeeId`,effectiveFrom;
что дало бы результат только как 1 день для сценария 1. Может ли кто-нибудь указать мне практический способ обработки сценариев. Я просмотрел циклы, оконные функции, но не знаю, как лучше поступить.
Ответы 1
Когда в сценарии 2 с 1 января до конца месяца 31 день, я бы подозревал, что с 25 января до конца месяца 7 дней, а не 6, как вы пишете в сценарии 3.
Количество дней, используя приведенный выше расчет:
SELECT
employeeID,
grade,
effectiveFrom,
DATEDIFF(COALESCE(LEAD(effectiveFrom)
OVER (PARTITION BY employeeID, grade ORDER By effectiveFrom),
DATE_ADD(LAST_DAY(effectiveFrom),INTERVAL 1 DAY)),
effectiveFrom) as '#Days'
FROM EmployeeRole;
Это можно сгруппировать и суммировать, давая:
SELECT
employeeID,
grade,
SUM(`#Days`)
FROM (
SELECT
employeeID,
grade,
effectiveFrom,
DATEDIFF(COALESCE(LEAD(effectiveFrom)
OVER (PARTITION BY employeeID, grade ORDER By effectiveFrom),
DATE_ADD(LAST_DAY(effectiveFrom),INTERVAL 1 DAY)),
effectiveFrom) as '#Days'
FROM EmployeeRole
) x
GROUP BY
employeeID,
grade;
вывод:
#Days)
1
А
14
1
Б
17
2
А
31
3
А
21
3
Б
10
см.: DBFIDDLE
Обновлено: результаты были неверными, потому что следующая дата EffectiveFrom была определена с использованием OVER (PARTITION BY employeeID ORDER By effectiveFrom). это неправильно, потому что grade тоже следует учитывать.
Я исправил это на OVER (PARTITION BY employeeID, grade ORDER By effectiveFrom)
P.S. Я также исправил это в части выше EDIT!
см.: DBFIDDLE
Это гениально, спасибо! У меня есть вторая часть второго сценария, где я должен получить 28 дней за февраль, поэтому, предполагая, что оценка не показывает изменений с 31 января, я должен получить полное количество дней в месяце, поэтому февраль = 28, март =31,апр=30 и т.д. и т.д. В таблице не будет данных за эти месяцы, в таблице будут только даты EffectiveFrom.
у меня есть данные, что запрос возвращает странный результат. Я обновил скрипку, но должна ли оценка A вступить в силу с 1 июля 22 г., а оценка B — с 13 сентября 22 г., как для одного идентификатора сотрудника, так и для одного идентификатора сотрудника я получаю 74 дня для A (должно быть 31) и 18 для класса B, который правильно. Похоже, что следующий эффективный период следующего класса рассматривается как конец периода действия, тогда как следует учитывать конец месяца.
@Bisoux: Вы сказали: «Я обновил скрипку». Вы заметили, что после каждого запуска URL-адрес меняется? Итак, я не видел вашего обновления, но я думаю, что ваш комментарий был достаточно ясен.
Для данных, которые выглядят как (4,'2022-07-01','A'), (4,'2022-07-02','B'), где класс A действует с 1 июля по 1 июля как другой класс вступает в силу со 2 июля. В этом случае класс A действителен в течение 1 дня (запрос дает 31), а для класса B — 30 дней, что верно.
О, теперь я понимаю, почему я сделал PARTITION BY employeeID ..., потому что это будет правильно на следующий день (из другого класса). Мне нужно отменить это изменение и взять наименьшее значение из следующего EffectiveFrom и endOfthe Month. см.: dbfiddle.uk/xGfC8rZ9 (извините, нет времени редактировать ответ)
вам нужно сравнить две строки, но оператор SELECT может получить доступ только к 1 за раз