Сотрудник нанимается в разные отделы. Например, они могут работать в отделе маркетинга и получать оплату от отдела 0001, а также от отдела 0002 в зависимости от отработанного времени. Для этого вопроса часы не имеют значения, так как мне действительно нужно только найти диапазоны дат, когда сотрудник НЕ нанят. Приведенные ниже два сотрудника являются примерами таких.
По запросу должны возвращаться идентификаторы Employee_ID 395961 и 395962, так как оба имеют пробелы в диапазоне дат. 395961 не имеет диапазона дат с 01.04.2022 по 31.05.2022. 395962 не имеет диапазона дат с 01.05.2022 по 31.05.2022. 395963 НЕ имеет разрыва диапазона дат.
Я имел в виду вернуть месяцы, в которые у сотрудника были диапазоны дат, и сгруппировать их по месяцам, так как мы можем получить повторяющиеся результаты. Например, Employee_ID 395961 вернет январь, февраль, март, январь, июнь, июль, август, сен, октябрь, ноябрь, декабрь, и когда мы сгруппируем месяцы, он удалит повторяющийся январь. Затем мы проверяем, какие месяцы отсутствуют, и если что-то возвращается, мы должны рассмотреть возможность возврата этого Employee_ID в качестве результата.
Я действительно НЕ понял, как получить правильный результат, но я провел некоторое исследование, чтобы найти ниже, что может помочь. Я бы не хотел использовать переменные, так как хочу, чтобы этот запрос применялся к тысячам записей.
SELECT DATENAME(MONTH, DATEADD(MONTH, x.number, @Start_Date)) AS MonthName
FROM master.dbo.spt_values x
WHERE x.type = 'P'
AND x.number <= DATEDIFF(MONTH, @Start_Date, @End_Date)
Обновлено: база данных — Microsoft SQL Server 2017.
Также ваши даты действительно хранятся в виде строк?
@BillKarwin Microsoft SQL Server 2017
@RiggsFolly - Нет, это даты в формате CCYY-MM-DD. Предоставленный мной запрос был только примером того, как получить месяцы из диапазона дат... но на самом деле это не то, что мне было нужно. Я даже не знаю, с чего начать, поэтому я просто дал кое-что в качестве ссылки.
Я думаю, что это должно дать вам то, что вы ищете:
WITH cal (date) AS (
SELECT @Start_Date
UNION ALL
SELECT DATEADD(day, 1, date)
FROM cal
WHERE date < @End_Date
)
SELECT e.Employee_ID, COUNT(cal.date) AS Days_Not_Hired
FROM cal
CROSS JOIN (SELECT DISTINCT Employee_ID FROM employees) e
LEFT JOIN employees e2
ON cal.date BETWEEN e2.Start_Date AND e2.End_Date
AND e.Employee_ID = e2.Employee_ID
WHERE e2.Employee_ID IS NULL
GROUP BY e.Employee_ID
OPTION(MAXRECURSION 365);
Это нацелено на SQL Server. Для других диалектов может потребоваться настройка.
Чтобы работать для всех сотрудников за все время, вы можете сделать:
WITH cal (date) AS (
SELECT MIN(Start_Date) FROM employees
UNION ALL
SELECT DATEADD(day, 1, date)
FROM cal
WHERE date < CAST( GETDATE() AS DATE )
)
SELECT e.*, COUNT(cal.date) AS Days_Not_Hired
FROM cal
JOIN (
SELECT
Employee_ID,
MIN(Start_Date) AS Min_Start_Date,
MAX(End_Date) AS Max_End_Date
FROM employees
GROUP BY Employee_ID
) e
ON cal.date BETWEEN e.Min_Start_Date AND e.Max_End_Date
LEFT JOIN employees e2
ON cal.date BETWEEN e2.Start_Date AND e2.End_Date
AND e.Employee_ID = e2.Employee_ID
WHERE e2.Employee_ID IS NULL
GROUP BY e.Employee_ID, e.Min_Start_Date, e.Max_End_Date
OPTION(MAXRECURSION 0);
Я очень редко использую SQL Server, поэтому понятия не имею, как его оптимизатор будет обрабатывать этот запрос.
Используются переменные Start_Date и End_Date. Не подходит для моего сценария @nnichols
Я объявил Start_Date и End_Date 01.01.2022 и 31.12.2022 и получил обратно 1 сотрудника, в то время как он должен был вернуть 2 сотрудников.
Я понял, почему. Моя дата окончания была обновлена до 2022-04-31, когда я нажал CTRL+Z на другом фрагменте кода. Я собираюсь просмотреть еще несколько записей! Вернусь, если у меня возникнут дополнительные вопросы! @nnichols
Очевидно, вы можете запустить его без использования пользовательских переменных — dbfiddle.uk/_sdVjLNj
Если бы я хотел убедиться, что он действительно ищет разрыв в дате между MIN (START_DATE) и MAX (END_DATE) для идентификатора EMPLOYEE ID, а не с января 2022 г. по декабрь 2022 г., что нужно было бы изменить? Например. Если это 01.03.2022 и 31.12.2022, между этими датами MIN/MAX не будет разрыва дат. @nnichols
Эти диапазоны дат будут меняться в зависимости от идентификатора сотрудника, верно? Таким образом, один сотрудник может быть там с января по декабрь, а другой сотрудник может работать только с марта по декабрь. Честно говоря, я никогда не использовал CROSS JOIN, rCTE или CTE в целом. Я планирую провести некоторое исследование и поиграть с ним, чтобы понять, что именно делает запрос. Я больше аналитик, который просматривает данные, чем очищает данные или разрабатывает код. Но я учусь из того, что мне предоставляют разные источники, и я модифицирую то, что мне нужно.
ВЫБЕРИТЕ MIN(START_DATE), MAX(END_DATE) FROM EMPLOYEES GROUP BY EMPLOYEE_ID – это то, о чем я говорю. Речь идёт о 2млн пластинках. @nnichols
Ваш вопрос отмечен тегом mysql, но ссылка на таблицу
master.dbo.spt_values
заставляет меня думать, что вы используете Microsoft SQL Server (поскольку MySQL не поддерживает такие идентификаторы с указанием каталога). Не могли бы вы запроситьSELECT @@version;
и подтвердить, какое программное обеспечение базы данных вы используете?