В 1С:Зарплата и Управление Персоналом (ЗУП) часто возникает необходимость анализа истории должностей сотрудников. Стандартные механизмы СКД (Система Компоновки Данных) могут быть недостаточны для специфических сценариев, когда сотрудник в течение месяца несколько раз меняет должность, в том числе возвращается на ту же, с которой начинал. Мы сталкиваемся с задачей, где требуется объединить последовательные записи с одинаковой должностью в один период, указав дату начала и дату окончания. Простая группировка по должности с использованием минимальной даты начала и максимальной даты окончания не подходит, так как она объединит разрозненные периоды одной и той же должности, теряя информацию о промежуточных изменениях. Давайте подробно рассмотрим различные подходы к решению этой сложной задачи.
Представим, что у нас есть следующая исходная выборка по дням для одного сотрудника, полученная из информационной базы ЗУП. Каждая строка представляет собой запись о должности сотрудника на определенный день:
ПериодНачало ПериодОкончание Должность
01.02.2023 01.02.2023 23:59:59 Инженер-конструктор II категории
... (дни до 16.02) ...
16.02.2023 16.02.2023 23:59:59 Инженер-конструктор II категории
17.02.2023 17.02.2023 23:59:59 Начальник конструкторского сектора
... (дни до 22.02) ...
22.02.2023 22.02.2023 23:59:59 Начальник конструкторского сектора
23.02.2023 23.02.2023 23:59:59 Инженер-конструктор II категории
... (дни до 28.02) ...
28.02.2023 28.02.2023 23:59:59 Инженер-конструктор II категории
Наша цель — преобразовать эту ежедневную детализацию в укрупненные периоды, где каждая смена должности или возврат к предыдущей должности формирует новый интервал. Желаемый результат должен выглядеть так:
01.02.2023 16.02.2023 23:59:59 Инженер-конструктор II категории
17.02.2023 22.02.2023 23:59:59 Начальник конструкторского сектора
23.02.2023 28.02.2023 23:59:59 Инженер-конструктор II категории
Очевидно, что простая группировка по полю Должность с функциями МИН(ПериодНачало) и МАКС(ПериодОкончание) не сработает, так как она объединит первый и третий периоды "Инженер-конструктор II категории" в один длинный интервал, теряя информацию о промежуточных изменениях. Нам необходимо идентифицировать каждую "серию" одинаковых должностей.
Один из эффективных подходов для решения такой задачи на уровне запроса в 1С — это использование самосоединения (JOIN) таблицы самой с собой для определения моментов изменения должности. Этот метод позволяет нам "заглянуть" в предыдущую запись и сравнить текущую должность с ней.
Разберем шаги такого запроса:
мТаб).
Сначала мы формируем временную таблицу с исходными данными, которые мы хотим сгруппировать. Важно, чтобы эти данные были отсортированы по дате. В реальной задаче ЗУП это будет выборка из регистров сведений, например, РегистрСведений.КадроваяИсторияСотрудников (или аналогичных, зависящих от конфигурации) за определенный период. Для примера мы используем синтетические данные:
ВЫБРАТЬ ДАТАВРЕМЯ(2022, 12, 31) КАК Дата, "ТочкаОтчета" КАК Статус
ПОМЕСТИТЬ мТаб
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ДАТАВРЕМЯ(2023, 1, 1), "А"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ДАТАВРЕМЯ(2023, 1, 2), "А"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ДАТАВРЕМЯ(2023, 1, 3), "А"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ДАТАВРЕМЯ(2023, 1, 4), "А"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ДАТАВРЕМЯ(2023, 1, 5), "Б"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ДАТАВРЕМЯ(2023, 1, 6), "Б"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ДАТАВРЕМЯ(2023, 1, 7), "А"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ДАТАВРЕМЯ(2023, 1, 8), "А"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ДАТАВРЕМЯ(2023, 1, 9), "А"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ДАТАВРЕМЯ(2023, 1, 10), "В"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ДАТАВРЕМЯ(2023, 1, 11), "А"
;
Обратите внимание на первую строку с ДАТАВРЕМЯ(2022, 12, 31) и статусом "ТочкаОтчета". Эта строка очень важна, она служит "точкой отсчета" для первой записи реальных данных. Если ее не будет, первая группа может быть некорректно определена. Ее статус должен отличаться от статуса первой записи в рабочем диапазоне.
м2).
На этом шаге мы используем самосоединение для каждой строки из мТаб, чтобы найти максимальную дату из _всех предыдущих или равных дат_, где Статус (должность) был отличным от текущего Статуса. Это и будет дата, когда "состояние" изменилось в последний раз перед текущей записью.
Мы создадим временную таблицу м2, которая будет содержать исходную дату, статус и найденную "дату предыдущего изменения". Если АВТОНОМЕРЗАПИСИ недоступен или не гарантирует сортировку, мы можем использовать сравнение по полю Дата напрямую, как было предложено в обсуждении.
ВЫБРАТЬ
мТаб.Дата,
мТаб.Статус,
МАКСИМУМ(м11.Дата) КАК ДатаПоследнегоИзменения
ПОМЕСТИТЬ м2
ИЗ
мТаб КАК мТаб
ВНУТРЕННЕЕ СОЕДИНЕНИЕ мТаб КАК м11
ПО мТаб.Дата >= м11.Дата
И (НЕ мТаб.Статус = м11.Статус)
СГРУППИРОВАТЬ ПО
мТаб.Статус,
мТаб.Дата
;
В этом запросе:
мТаб.Дата >= м11.Дата гарантирует, что мы рассматриваем только записи, которые предшествуют текущей или совпадают с ней по дате.(НЕ мТаб.Статус = м11.Статус) фильтрует только те "предыдущие" записи, где должность отличалась от текущей.МАКСИМУМ(м11.Дата) выбирает самую позднюю из этих "отличающихся" предыдущих дат, то есть дату, когда должность сменилась на что-то другое в последний раз перед текущим периодом.Теперь, когда у нас есть дата последнего изменения для каждой строки (которая является "точкой старта" текущего периода), мы можем сгруппировать данные.
Дата начала нового интервала будет на один день больше, чем ДатаПоследнегоИзменения. Дата окончания интервала — это максимальная Дата в рамках данной группы.
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(м2.ДатаПоследнегоИзменения, День, 1) КАК ДатаС,
МАКСИМУМ(м2.Дата) КАК ДатаПо,
м2.Статус
ИЗ
м2 КАК м2
СГРУППИРОВАТЬ ПО
м2.ДатаПоследнегоИзменения,
м2.Статус
УПОРЯДОЧИТЬ ПО
ДатаС
;
Этот запрос эффективно формирует непрерывные интервалы для каждой уникальной последовательности должностей.
Важное замечание: Этот метод работает корректно при условии, что исходные данные в мТаб (или вашем источнике) содержат записи для _каждого дня_ в интересующем диапазоне или, по крайней мере, для дней, когда происходит смена статуса. Если данные разрозненные (есть пропуски дней), потребуется дополнительная предобработка для "заполнения" пропущенных дней, чтобы обеспечить непрерывность. В реальных условиях ЗУП часто используются срезы регистров сведений, которые могут не содержать записи для каждого дня. В таком случае, перед использованием данного запроса, необходимо сформировать непрерывную последовательность дней и заполнить ее соответствующими должностями.
Если объем данных очень большой (например, за год по 500 сотрудникам), или логика определения периодов становится слишком сложной для чистого SQL-запроса, можно рассмотреть программную обработку данных.
Этот подход подразумевает следующие шаги:
Мы формируем начальный запрос в СКД, который выбирает все ежедневные (или по изменению) записи о должностях сотрудника за требуемый период. Результат этого запроса мы получаем в виде таблицы значений.
// Пример получения исходных данных в модуле отчета или при обработке
Запрос = Новый Запрос;
Запрос.Текст = "
ВЫБРАТЬ
ИсторияДолжностей.ПериодНачало КАК Дата,
ИсторияДолжностей.Должность КАК Должность
ИЗ
РегистрСведений.ИсторияДолжностей КАК ИсторияДолжностей
ГДЕ
ИсторияДолжностей.ПериодНачало МЕЖДУ &НачалоПериода И &КонецПериода
И ИсторияДолжностей.Сотрудник = &Сотрудник
УПОРЯДОЧИТЬ ПО
Дата
";
Запрос.УстановитьПараметр("НачалоПериода", НачалоПериода);
Запрос.УстановитьПараметр("КонецПериода", КонецПериода);
Запрос.УстановитьПараметр("Сотрудник", СотрудникОтчета);
ТаблицаИсходныхДанных = Запрос.Выполнить().Выгрузить();
Здесь РегистрСведений.ИсторияДолжностей является гипотетическим примером регистра, хранящего ежедневную историю. В ЗУП 3.1 данные о кадровых перемещениях хранятся в РегистрСведений.КадроваяИсторияСотрудников, РегистрСведений.СостоянияСотрудников и других, и их выборка может потребовать более сложного запроса с использованием виртуальных таблиц, таких как СрезПериода или Движения.
В модуле отчета, например, в обработчике события ПриКомпоновкеРезультата, мы можем взять полученную таблицу значений и пройтись по ней построчно, формируя новую таблицу с требуемой группировкой.
Логика будет следующей:
// Пример программной обработки ТаблицыИсходныхДанных
НоваяТаблицаРезультатов = Новый ТаблицаЗначений;
НоваяТаблицаРезультатов.Колонки.Добавить("ПериодНачало", Новый ОписаниеТипов("Дата"));
НоваяТаблицаРезультатов.Колонки.Добавить("ПериодОкончание", Новый ОписаниеТипов("Дата"));
НоваяТаблицаРезультатов.Колонки.Добавить("Должность", Новый ОписаниеТипов("СправочникСсылка.Должности")); // Или строка, в зависимости от типа поля
ТекущаяДолжность = Неопределено;
ДатаНачалаТекущегоПериода = Неопределено;
// Предполагаем, что ТаблицаИсходныхДанных уже отсортирована по дате
Для Каждого СтрокаИсходныхДанных ИЗ ТаблицаИсходныхДанных Цикл
Если ТекущаяДолжность = Неопределено Тогда // Первая запись или начало обработки
ТекущаяДолжность = СтрокаИсходныхДанных.Должность;
ДатаНачалаТекущегоПериода = СтрокаИсходныхДанных.Дата;
ИначеЕсли СтрокаИсходныхДанных.Должность <> ТекущаяДолжность Тогда // Смена должности
НоваяСтрока = НоваяТаблицаРезультатов.Добавить();
НоваяСтрока.ПериодНачало = ДатаНачалаТекущегоПериода;
// Дата окончания предыдущего периода - это день перед началом нового
НоваяСтрока.ПериодОкончание = ДобавитьКДате(СтрокаИсходныхДанных.Дата, День, -1);
НоваяСтрока.Должность = ТекущаяДолжность;
ТекущаяДолжность = СтрокаИсходныхДанных.Должность;
ДатаНачалаТекущегоПериода = СтрокаИсходныхДанных.Дата;
КонецЕсли;
КонецЦикла;
// Добавляем последний сформированный период после обхода всех строк
Если ТекущаяДолжность <> Неопределено Тогда
НоваяСтрока = НоваяТаблицаРезультатов.Добавить();
НоваяСтрока.ПериодНачало = ДатаНачалаТекущегоПериода;
// Дата окончания для последнего периода - это последняя дата в исходных данных
// или конец периода отчета, в зависимости от требований.
// Пример: КонецМесяца(ДатаНачалаТекущегоПериода) или ТаблицаИсходныхДанных.Последняя().Дата
НоваяСтрока.ПериодОкончание = ТаблицаИсходныхДанных.Последняя().Дата;
НоваяСтрока.Должность = ТекущаяДолжность;
КонецЕсли;
// Теперь НоваяТаблицаРезультатов содержит сгруппированные данные
Важно: При определении ПериодОкончание для последней строки, если нет последующих изменений до конца месяца, необходимо использовать либо дату последней записи в исходных данных, либо конец месяца, в зависимости от требований отчета.
Полученную НоваяТаблицаРезультатов можно передать в СКД одним из двух способов:
ПОМЕСТИТЬ в запросе СКД, а программно заполняем ее данные. Для этого в событии ПриКомпоновкеРезультата (или другом подходящем событии отчета) мы сначала очищаем и затем заполняем временную таблицу данными из НоваяТаблицаРезультатов. Затем основной запрос СКД будет обращаться к этой временной таблице.При работе с временными таблицами в СКД, в модуле объекта отчета мы можем переопределить текст запроса набора данных, чтобы он обращался к нашей временной таблице. Для этого часто используется процедура ЗаполнитьОбщиеИсточникиДанныхОтчета() или прямой вызов УстановитьТекстЗапроса().
Если мы посмотрим на типовой отчет Кадровая история сотрудников в ЗУП, мы увидим, что он уже решает подобные задачи, предоставляя информацию о последовательных периодах работы сотрудника в различных должностях. Анализ запросов и программного кода этого отчета может дать ценные подсказки и готовые решения для вашей задачи, так как он, вероятно, использует сложные запросы и/или программную обработку для формирования корректных интервалов. Изучение структуры его виртуальных таблиц и механизмов группировки в СКД поможет понять, как типовая конфигурация справляется с подобными вызовами.
Для баз данных, поддерживающих продвинутые функции SQL, такие как "оконные" функции (Window Functions), эта задача может быть решена более элегантно и эффективно непосредственно на уровне СУБД (MS SQL, PostgreSQL). Несмотря на то, что запросы в 1С транслируются в SQL, прямое использование всех оконных функций может быть недоступно или неочевидно в конструкторе запросов 1С. Однако понимание этого подхода полезно для общего развития и может быть применено, если есть возможность прямого обращения к СУБД или если 1С в будущем расширит возможности своего конструктора.
Основная идея заключается в том, чтобы для каждой строки определить, когда произошла смена должности по сравнению с предыдущей записью. Для этого нам потребуется определить "групповой идентификатор" (GroupID) для каждой непрерывной серии одинаковых должностей.
Разберем по шагам, как это можно сделать:
Пусть у нас есть таблица ИсторияДолжностей (или аналог в вашей базе) с полями Сотрудник, Дата, Должность. Мы предполагаем, что данные уже отсортированы по Сотрудник и Дата.
Используем функцию LAG() для сравнения текущей должности с должностью из предыдущей строки в рамках одного сотрудника. Если текущая должность отличается от предыдущей, это означает начало нового периода. Для первой записи в группе (сотруднике) мы будем считать, что это всегда начало нового периода.
SELECT
Сотрудник,
Дата,
Должность,
CASE
WHEN Должность <> LAG(Должность, 1, '') OVER (PARTITION BY Сотрудник ORDER BY Дата)
THEN 1
ELSE 0
END AS IsNewPeriodStart
FROM
ИсторияДолжностей
ORDER BY
Сотрудник, Дата;
В этом запросе:
LAG(Должность, 1, '') OVER (PARTITION BY Сотрудник ORDER BY Дата) получает значение Должность из предыдущей строки для того же Сотрудник, отсортированного по Дата. Третий параметр `''` (пустая строка) — это значение по умолчанию, которое будет возвращено, если предыдущей строки нет (т.е. это первая запись для сотрудника). Это гарантирует, что первая запись всегда будет отличаться от ' ' и будет помечена как начало нового периода.CASE ... THEN 1 ELSE 0 END помечает строки, где должность изменилась (или это первая запись), как 1.Теперь мы используем кумулятивную сумму (SUM) по IsNewPeriodStart, чтобы создать уникальный идентификатор для каждой непрерывной группы. Каждое "1" в IsNewPeriodStart увеличивает счетчик, тем самым присваивая новый GroupID каждой новой серии должностей.
WITH Changes AS (
SELECT
Сотрудник,
Дата,
Должность,
CASE
WHEN Должность <> LAG(Должность, 1, '') OVER (PARTITION BY Сотрудник ORDER BY Дата)
THEN 1
ELSE 0
END AS IsNewPeriodStart
FROM
ИсторияДолжностей
)
SELECT
Сотрудник,
Дата,
Должность,
SUM(IsNewPeriodStart) OVER (PARTITION BY Сотрудник ORDER BY Дата) AS GroupID
FROM
Changes
ORDER BY
Сотрудник, Дата;
Теперь для всех последовательных дней с одной и той же должностью у нас будет одинаковый GroupID в рамках каждого сотрудника.
Наконец, мы можем сгруппировать данные по Сотрудник, Должность и созданному GroupID, чтобы получить минимальную дату начала и максимальную дату окончания для каждого такого периода.
WITH Changes AS (
SELECT
Сотрудник,
Дата,
Должность,
CASE
WHEN Должность <> LAG(Должность, 1, '') OVER (PARTITION BY Сотрудник ORDER BY Дата)
THEN 1
ELSE 0
END AS IsNewPeriodStart
FROM
ИсторияДолжностей
),
GroupedData AS (
SELECT
Сотрудник,
Дата,
Должность,
SUM(IsNewPeriodStart) OVER (PARTITION BY Сотрудник ORDER BY Дата) AS GroupID
FROM
Changes
)
SELECT
Сотрудник,
МИН(Дата) AS ПериодНачало,
МАКС(Дата) AS ПериодОкончание,
Должность
FROM
GroupedData
GROUP BY
Сотрудник,
Должность,
GroupID
ORDER BY
Сотрудник, ПериодНачало;
Этот метод является наиболее мощным и элегантным для решения задач такого типа ("gaps and islands problem" – проблема промежутков и островов) в чистом SQL. Он позволяет решить проблему в один проход без необходимости создания множества временных таблиц в таком количестве, как в первом решении.
Ограничения: В конструкторе запросов 1С напрямую не поддерживаются все оконные функции. Если вы работаете с внешней базой данных или используете более низкоуровневые механизмы доступа к данным, этот подход может быть реализован. В большинстве случаев для 1С-разработчика более практичными будут первые два решения, так как они более совместимы со стандартными возможностями платформы 1С:Предприятие.
Мы рассмотрели несколько подходов к группировке должностей в 1С:ЗУП, учитывающих возвращение сотрудника на предыдущую должность. Выбор конкретного метода зависит от объема данных, сложности требуемой логики и предпочтений разработчика.
Всегда полезно также изучить, как подобные задачи решаются в типовых конфигурациях 1С, например, в отчете "Кадровая история сотрудников", который может содержать готовые паттерны для адаптации и понимания лучших практик. Выбрав подходящий подход, вы сможете эффективно формировать отчетность по кадровой истории сотрудников с корректной группировкой периодов.