Как в 1С СКД получить крайнюю дату всех последовательно идущих состояний отсутствия сотрудника, исключая 'Работа' и 'Увольнение'?

Программист 1С v8.3 (Управляемые формы) 1С:Зарплата и Управление Персоналом Управленческий учет
← На главную

При работе с данными о состояниях сотрудников в 1С:Зарплата и Управление Персоналом часто возникает задача аналитического характера: необходимо определить общую продолжительность или крайнюю дату непрерывного периода отсутствия сотрудника. Например, если сотрудник сначала был в основном отпуске, затем сразу же в дополнительном, а потом взял отпуск за свой счет, и все эти события следуют друг за другом без перерыва или с перерывом не более одного дня, нам нужна дата окончания последнего из этих событий как крайняя дата общего периода отсутствия. Если же между событиями возникает "окно" в несколько дней, это означает, что цепочка прерывается, и крайняя дата должна относиться к предыдущей непрерывной последовательности.

Рассмотрим подробнее эту задачу и предложим несколько подходов к ее решению в Системе Компоновки Данных (СКД) 1С, включая как метод, предложенный на форуме, так и более универсальный подход к решению проблемы "дыр и островов" (Gaps and Islands).

Постановка задачи и исходные данные

Наши исходные данные содержатся в регистре сведений СостоянияСотрудников. Этот регистр хранит информацию о различных состояниях сотрудника на определенные даты, с указанием периода действия:

Нам необходимо сформировать отчет, который для каждого сотрудника определит:

При этом, существуют следующие важные условия:

  1. Мы учитываем только состояния отсутствия (отпуск, больничный, отпуск за свой счет и т.п.). Состояния Работа и Увольнение должны быть исключены.
  2. Последовательность считается непрерывной, если дата начала следующего события является следующим днем после даты окончания предыдущего события.
  3. Если между датой окончания одного события и датой начала следующего события проходит более одного дня, последовательность считается разорванной. В этом случае мы должны получить крайнюю дату предыдущей непрерывной цепочки.

Пример, иллюстрирующий условие:

Эта задача является классическим примером проблемы "дыр и островов" (Gaps and Islands) в SQL, которая требует определения непрерывных интервалов в данных, разделенных "дырами" (разрывами).

Метод "Дыры и Острова" (Gaps and Islands) в СКД

Для решения задачи "дыр и островов" в языке запросов 1С, который не поддерживает напрямую оконные функции (такие как LAG, LEAD, ROW_NUMBER() OVER (...)), мы обычно используем комбинацию временных таблиц и самосоединений. Общая идея состоит в следующем:

  1. Подготовка исходных данных: Выбираем все релевантные записи и фильтруем ненужные состояния.
  2. Определение начала каждой "группы" (острова): Для каждой записи определяем, является ли она началом новой непрерывной последовательности. Это происходит, когда текущая дата начала события не следует непосредственно за датой окончания предыдущего события.
  3. Присвоение идентификатора группе: Каждой такой непрерывной последовательности событий (острову) присваивается уникальный идентификатор.
  4. Агрегация данных по группам: Группируем данные по сотруднику и этому идентификатору группы, чтобы найти минимальную дату начала и максимальную дату окончания для каждого объединенного периода.

Рассмотрим два подхода к реализации, основываясь на обсуждении в форуме и более надежной реализации метода "дыр и островов".

Подход 1: Решение, предложенное и подтвержденное на форуме

На форуме было предложено решение, которое автор темы подтвердил как рабочее. Оно использует технику самосоединения и специфический расчет для создания идентификатора группы. Давайте проанализируем его шаги.

Шаг 1: Получение исходных состояний (Временная таблица "Дано")

На первом этапе мы выбираем все состояния сотрудников из регистра СостоянияСотрудников, которые соответствуют нашим критериям: для выбранных сотрудников, после заданной даты и исключая состояния, которые мы не хотим учитывать (например, 'Работа' и 'Увольнение').


ПОМЕСТИТЬ Дано
ВЫБРАТЬ
    СостоянияСотрудников.Сотрудник КАК Сотрудник,
    СостоянияСотрудников.Период КАК НачалоПериода,
    СостоянияСотрудников.ДействуетДо КАК ДействуетДо,
    СостоянияСотрудников.Состояние КАК Состояние
ИЗ
    РегистрСведений.СостоянияСотрудников КАК СостоянияСотрудников
ГДЕ
    СостоянияСотрудников.Сотрудник В(&Сотрудник)
    И СостоянияСотрудников.Период > &НачалоПериода // Параметр &НачалоПериода определяет начало отчетного периода
    И НЕ СостоянияСотрудников.Состояние В (&ИсключаемыеСостояния) // Здесь &ИсключаемыеСостояния = {ЗНАЧЕНИЕ(Перечисление.СостоянияСотрудника.Работа), ЗНАЧЕНИЕ(Перечисление.СостоянияСотрудника.Увольнение)}
;

Пояснения:

Шаг 2: Расчет кумулятивной длительности (Временная таблица "ЕщёДано")

На этом шаге мы используем самосоединение таблицы "Дано" для вычисления кумулятивной суммы длительностей всех предыдущих и текущих периодов для каждого сотрудника. Это значение сохраняется в поле Разность.


ПОМЕСТИТЬ ЕщёДано
ИЗ
    Дано КАК Дано
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Дано КАК Слева
        ПО (Слева.НачалоПериода <= Дано.НачалоПериода)
           И Дано.Сотрудник = Слева.Сотрудник
СГРУППИРОВАТЬ ПО
    Дано.НачалоПериода,
    Дано.ДействуетДо,
    Дано.Сотрудник
ВЫБРАТЬ
    Дано.Сотрудник КАК Сотрудник,
    Дано.НачалоПериода КАК НачалоПериода,
    Дано.ДействуетДо КАК Конец,
    СУММА(РАЗНОСТЬДАТ(Слева.НачалоПериода, Слева.ДействуетДо, ДЕНЬ) + 1) КАК Разность
;

Пояснения:

Шаг 3: Агрегация по группам и получение крайних дат

На последнем шаге мы используем вычисленное поле Разность для создания уникального идентификатора группы и агрегируем данные, чтобы получить минимальную дату начала и максимальную дату окончания для каждой такой группы.


ВЫБРАТЬ
    Дано.Сотрудник КАК Сотрудник,
    МИНИМУМ(Дано.НачалоПериода) КАК НачалоОбъединенногоПериода,
    МАКСИМУМ(Дано.Конец) КАК КрайняяДатаОбъединенногоПериода
ИЗ
    ЕщёДано КАК Дано
СГРУППИРОВАТЬ ПО
    ДОБАВИТЬКДАТЕ(Дано.Конец, ДЕНЬ, -Дано.Разность), // Этот расчет формирует идентификатор группы
    Дано.Сотрудник
;

Пояснения:

Несмотря на то, что это решение было подтверждено автором как рабочее, из нашего анализа следует, что оно не полностью соответствует первоначальному условию о разрыве более чем в один день. Возможно, в реальных данных автора такие ситуации не встречались, или было допущено некоторое упрощение требований. Если же строгое соблюдение условия "разрыв > 1 дня" критически важно, нам потребуется более детальная реализация метода "дыр и островов".

Подход 2: Более надежное решение "Дыры и Острова" с учетом разрывов

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

Шаг 1: Подготовка и фильтрация исходных данных (Временная таблица "ВТ_ИсходныеДанные")

Как и в первом подходе, мы выбираем все необходимые состояния, фильтруем по сотруднику и периоду, исключаем состояния "Работа" и "Увольнение". Важно отсортировать данные по сотруднику и дате начала для корректного определения предыдущих состояний.


ПОМЕСТИТЬ ВТ_ИсходныеДанные
ВЫБРАТЬ
    СостоянияСотрудников.Сотрудник КАК Сотрудник,
    СостоянияСотрудников.Период КАК ДатаНачала,
    СостоянияСотрудников.ДействуетДо КАК ДатаОкончания
ИЗ
    РегистрСведений.СостоянияСотрудников КАК СостоянияСотрудников
ГДЕ
    СостоянияСотрудников.Сотрудник В(&Сотрудник)
    И СостоянияСотрудников.Состояние <> ЗНАЧЕНИЕ(Перечисление.СостоянияСотрудника.Работа)
    И СостоянияСотрудников.Состояние <> ЗНАЧЕНИЕ(Перечисление.СостоянияСотрудника.Увольнение)
    // Дополнительные фильтры по периоду отчета
    И (СостоянияСотрудников.Период МЕЖДУ &НачалоПериода И &ОкончаниеПериода
        ИЛИ СостоянияСотрудников.ДействуетДо МЕЖДУ &НачалоПериода И &ОкончаниеПериода
        ИЛИ (СостоянияСотрудников.Период <= &НачалоПериода И СостоянияСотрудников.ДействуетДо >= &ОкончаниеПериода)) // Учитываем периоды, пересекающиеся с отчетным
УПОРЯДОЧИТЬ ПО
    Сотрудник, ДатаНачала;

Шаг 2: Определение точек разрыва (Временная таблица "ВТ_СТочкамиРазрыва")

На этом шаге мы для каждой записи определяем, является ли она началом новой непрерывной группы. Для этого мы находим дату окончания предыдущего по времени состояния того же сотрудника и сравниваем ее с датой начала текущего состояния.


ПОМЕСТИТЬ ВТ_СТочкамиРазрыва
ВЫБРАТЬ
    Т1.Сотрудник КАК Сотрудник,
    Т1.ДатаНачала КАК ДатаНачала,
    Т1.ДатаОкончания КАК ДатаОкончания,
    ВЫБОР
        КОГДА Т1.ДатаНачала > ДОБАВИТЬКДАТЕ(
            ЕСТЬNULL(
                (ВЫБРАТЬ МАКСИМУМ(Т2.ДатаОкончания)
                 ИЗ ВТ_ИсходныеДанные КАК Т2
                 ГДЕ Т2.Сотрудник = Т1.Сотрудник
                   И Т2.ДатаНачала < Т1.ДатаНачала), // Находим все состояния, которые начались строго ДО текущего
                ДАТАВРЕМЯ(1,1,1) // Минимальная дата для первого состояния сотрудника
            ), ДЕНЬ, 1)
        ТОГДА 1 // Если текущее событие начинается более чем через 1 день после предыдущего, это разрыв
        ИНАЧЕ 0 // Иначе это продолжение предыдущей группы
    КОНЕЦ КАК ПризнакРазрыва
ИЗ
    ВТ_ИсходныеДанные КАК Т1
УПОРЯДОЧИТЬ ПО
    Сотрудник, ДатаНачала;

Пояснения:

Шаг 3: Расчет кумулятивного счетчика разрывов для получения ИД группы (Временная таблица "ВТ_СГруппами")

Теперь, когда у нас есть флаг ПризнакРазрыва, мы можем создать уникальный идентификатор для каждой группы. Для этого мы подсчитываем, сколько раз ПризнакРазрыва = 1 встретился до текущей записи (включительно) для каждого сотрудника. Этот кумулятивный счетчик будет служить НомерГруппы.


ПОМЕСТИТЬ ВТ_СГруппами
ВЫБРАТЬ
    Т1.Сотрудник КАК Сотрудник,
    Т1.ДатаНачала КАК ДатаНачала,
    Т1.ДатаОкончания КАК ДатаОкончания,
    СУММА(Т2.ПризнакРазрыва) КАК НомерГруппы
ИЗ
    ВТ_СТочкамиРазрыва КАК Т1
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_СТочкамиРазрыва КАК Т2
        ПО Т1.Сотрудник = Т2.Сотрудник
           И Т2.ДатаНачала <= Т1.ДатаНачала // Суммируем все ПризнакРазрыва до текущей записи
СГРУППИРОВАТЬ ПО
    Т1.Сотрудник,
    Т1.ДатаНачала,
    Т1.ДатаОкончания
УПОРЯДОЧИТЬ ПО
    Сотрудник, ДатаНачала;

Пояснения:

Шаг 4: Агрегация по группам для получения крайних дат (Финальная выборка)

Теперь, когда каждая запись отнесена к своей уникальной группе (по Сотрудник и НомерГруппы), мы можем легко агрегировать данные, чтобы найти минимальную дату начала и максимальную дату окончания для каждой такой непрерывной последовательности.


ВЫБРАТЬ
    ВТ_СГруппами.Сотрудник КАК Сотрудник,
    МИНИМУМ(ВТ_СГруппами.ДатаНачала) КАК НачалоОбъединенногоПериода,
    МАКСИМУМ(ВТ_СГруппами.ДатаОкончания) КАК КрайняяДатаОбъединенногоПериода
ИЗ
    ВТ_СГруппами КАК ВТ_СГруппами
СГРУППИРОВАТЬ ПО
    ВТ_СГруппами.Сотрудник,
    ВТ_СГруппами.НомерГруппы
УПОРЯДОЧИТЬ ПО
    Сотрудник, НачалоОбъединенногоПериода;

Пояснения:

Пример трассировки для Сотрудника Б с разрывом (01.10-10.10, 12.10-15.10) с использованием надежного подхода:

Использование в СКД

Для использования любого из предложенных запросов в СКД, вам необходимо выполнить следующие шаги:

  1. Откройте схему компоновки данных вашего отчета.
  2. В закладке "Наборы данных" создайте новый "Набор данных - Запрос".
  3. Вставьте текст выбранного запроса в окно конструктора запросов.
  4. Настройте параметры запроса (&Сотрудник, &НачалоПериода, &ОкончаниеПериода, &ИсключаемыеСостояния) на закладке "Параметры" СКД, при необходимости задав им значения по умолчанию или установив их как пользовательские параметры.
  5. На закладке "Ресурсы" добавьте поля НачалоОбъединенногоПериода и КрайняяДатаОбъединенногоПериода, если вы хотите их использовать как ресурсы (например, для вычисления длительности).
  6. На закладке "Поля" убедитесь, что все необходимые поля доступны для использования в отчете.
  7. На закладке "Настройки" сформируйте структуру отчета, используя полученные поля.

Важные нюансы и тестирование

  1. Параметры периода: Тщательно продумайте, как параметры &НачалоПериода и &ОкончаниеПериода должны влиять на выборку данных. Запрос должен учитывать события, которые начались до &НачалоПериода, но продолжаются внутри отчетного интервала, или заканчиваются после &ОкончаниеПериода, но начались внутри.
  2. Производительность: Запросы с большим количеством самосоединений могут быть ресурсоемкими, особенно на больших объемах данных. Обязательно тестируйте запрос на реальных данных и при необходимости оптимизируйте его (например, добавьте индексы, если это возможно, или рассмотрите возможность предрасчета данных в регистре накопления, если задача повторяющаяся).
  3. Пограничные случаи: Всегда проверяйте решение на различных пограничных сценариях:
    • Один период отсутствия.
    • Два последовательных периода.
    • Два периода, разделенных ровно одним днем работы (который исключен).
    • Два периода, разделенных более чем одним днем.
    • Перекрывающиеся периоды (хотя регистр сведений СостоянияСотрудников обычно не позволяет создавать перекрывающиеся записи для одного сотрудника).
    • Периоды, которые начинаются или заканчиваются точно на границах отчетного периода.

Заключение

Мы рассмотрели задачу получения крайней даты последовательно идущих состояний отсутствия сотрудника, исключая "Работу" и "Увольнение", с учетом условия о прерывании последовательности при наличии разрыва более одного дня. Был представлен запрос с форума, который, как выяснилось, объединяет все нерабочие периоды сотрудника в один, независимо от промежуточных разрывов. Для строгого соблюдения условия о "разрыве > 1 дня" был предложен более надежный и универсальный запрос, реализующий метод "дыр и островов" путем определения точек разрыва и нумерации групп. Выбор подхода зависит от ваших точных требований к логике объединения периодов и специфики ваших данных.

← На главную