SQL-запрос, который возвращает все отсутствующие месяцы для диапазонов дат в течение года (Microsoft SQL Server)

Сотрудник нанимается в разные отделы. Например, они могут работать в отделе маркетинга и получать оплату от отдела 0001, а также от отдела 0002 в зависимости от отработанного времени. Для этого вопроса часы не имеют значения, так как мне действительно нужно только найти диапазоны дат, когда сотрудник НЕ нанят. Приведенные ниже два сотрудника являются примерами таких.

ID_сотрудника ИД_отдела Дата начала Дата окончания 395961 0001 01.01.2022 31.03.2022 395961 0002 01.01.2022 31.01.2022 395961 0001 01.06.2022 31.10.2022 395961 0001 01.11.2022 31.12.2022 395962 0001 01.01.2022 31.03.2022 395962 0002 01.01.2022 31.01.2022 395962 0002 01.02.2022 30.04.2022 395962 0001 01.06.2022 31.10.2022 395962 0001 01.11.2022 31.12.2022 395963 0001 01.01.2022 31.05.2022 395963 0002 01.06.2022 31.12.2022

По запросу должны возвращаться идентификаторы 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.

Ваш вопрос отмечен тегом mysql, но ссылка на таблицу master.dbo.spt_values заставляет меня думать, что вы используете Microsoft SQL Server (поскольку MySQL не поддерживает такие идентификаторы с указанием каталога). Не могли бы вы запросить SELECT @@version; и подтвердить, какое программное обеспечение базы данных вы используете?

Bill Karwin 21.02.2023 15:53

Также ваши даты действительно хранятся в виде строк?

RiggsFolly 21.02.2023 16:10

@BillKarwin Microsoft SQL Server 2017

MrStrangerDanger 21.02.2023 21:06

@RiggsFolly - Нет, это даты в формате CCYY-MM-DD. Предоставленный мной запрос был только примером того, как получить месяцы из диапазона дат... но на самом деле это не то, что мне было нужно. Я даже не знаю, с чего начать, поэтому я просто дал кое-что в качестве ссылки.

MrStrangerDanger 21.02.2023 21:09
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
4
54
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Я думаю, что это должно дать вам то, что вы ищете:

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

MrStrangerDanger 21.02.2023 21:07

Я объявил Start_Date и End_Date 01.01.2022 и 31.12.2022 и получил обратно 1 сотрудника, в то время как он должен был вернуть 2 сотрудников.

MrStrangerDanger 21.02.2023 21:15

Я понял, почему. Моя дата окончания была обновлена ​​до 2022-04-31, когда я нажал CTRL+Z на другом фрагменте кода. Я собираюсь просмотреть еще несколько записей! Вернусь, если у меня возникнут дополнительные вопросы! @nnichols

MrStrangerDanger 21.02.2023 21:22

Очевидно, вы можете запустить его без использования пользовательских переменных — dbfiddle.uk/_sdVjLNj

nnichols 21.02.2023 21:27

Если бы я хотел убедиться, что он действительно ищет разрыв в дате между MIN (START_DATE) и MAX (END_DATE) для идентификатора EMPLOYEE ID, а не с января 2022 г. по декабрь 2022 г., что нужно было бы изменить? Например. Если это 01.03.2022 и 31.12.2022, между этими датами MIN/MAX не будет разрыва дат. @nnichols

MrStrangerDanger 21.02.2023 21:45

Эти диапазоны дат будут меняться в зависимости от идентификатора сотрудника, верно? Таким образом, один сотрудник может быть там с января по декабрь, а другой сотрудник может работать только с марта по декабрь. Честно говоря, я никогда не использовал CROSS JOIN, rCTE или CTE в целом. Я планирую провести некоторое исследование и поиграть с ним, чтобы понять, что именно делает запрос. Я больше аналитик, который просматривает данные, чем очищает данные или разрабатывает код. Но я учусь из того, что мне предоставляют разные источники, и я модифицирую то, что мне нужно.

MrStrangerDanger 21.02.2023 22:04

ВЫБЕРИТЕ MIN(START_DATE), MAX(END_DATE) FROM EMPLOYEES GROUP BY EMPLOYEE_ID – это то, о чем я говорю. Речь идёт о 2млн пластинках. @nnichols

MrStrangerDanger 21.02.2023 22:07

Другие вопросы по теме