Как связать данные регистра оборотов с регистром сведений на каждую дату (срез последних на период)

← На главную

В практике разработки на платформе 1С часто возникает задача, когда необходимо получить данные из регистра накопления (например, обороты за месяц) и дополнить их значениями из периодического регистра сведений, которые были актуальны на момент совершения этих оборотов. Сложность заключается в том, что стандартный механизм СрезПоследних работает только на одну конкретную дату, а нам нужно получить «срез последних» для каждой строки из основной таблицы.

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

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

Представим ситуацию: у нас есть данные об отработанном времени сотрудников, сгруппированные по месяцам. Эти данные хранятся в регистре накопления ОтработанноеВремяПоСотрудникам. Параллельно в системе ведется учет графиков работы в периодическом регистре сведений ГрафикРаботыСотрудников.

Нам необходимо получить итоговую таблицу следующего вида: Сотрудник — Период (Месяц) — График работы (актуальный на этот месяц) — Отработано дней.

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

Классическое решение через вложенный запрос и МАКСИМУМ(Период)

Рассмотрим основной метод решения этой задачи, который часто называют «ручным срезом последних». Суть метода заключается в три этапа:

Получение основной таблицы данных (оборотов).
Поиск максимальной даты в регистре сведений, которая меньше или равна дате из основной таблицы.
Соединение полученной максимальной даты с самим регистром сведений для получения ресурса (в нашем случае — графика).

Разберем программный код, который позволяет это реализовать:


ВЫБРАТЬ
    Таб1.Сотрудник КАК Сотрудник,
    Таб1.ПериодДни КАК Период,
    Таб1.ОплаченоДнейОборот КАК ОплаченоДнейОборот,
    Таб2.ГрафикРаботы КАК ГрафикРаботы
ИЗ
    (ВЫБРАТЬ
        ОтработанноеВремяПоСотрудникамОбороты.Сотрудник КАК Сотрудник,
        ОтработанноеВремяПоСотрудникамОбороты.Период КАК ПериодДни,
        СУММА(ОтработанноеВремяПоСотрудникамОбороты.ОплаченоДнейОборот) КАК ОплаченоДнейОборот,
        МАКСИМУМ(ГрафикРаботыСотрудников.Период) КАК Период
    ИЗ
        РегистрНакопления.ОтработанноеВремяПоСотрудникам.Обороты(
                ,
                &КонецПериода,
                Месяц,
                Организация = &ГоловнаяОрганизация) КАК ОтработанноеВремяПоСотрудникамОбороты
            ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ГрафикРаботыСотрудников КАК ГрафикРаботыСотрудников
            ПО (ГрафикРаботыСотрудников.Период <= ОтработанноеВремяПоСотрудникамОбороты.Период)
                И (ГрафикРаботыСотрудников.Сотрудник = ОтработанноеВремяПоСотрудникамОбороты.Сотрудник)
    
    СГРУППИРОВАТЬ ПО
        ОтработанноеВремяПоСотрудникамОбороты.Сотрудник, 
        ОтработанноеВремяПоСотрудникамОбороты.Период) КАК Таб1
        
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ГрафикРаботыСотрудников КАК Таб2
        ПО Таб1.Период = Таб2.Период
            И Таб1.Сотрудник = Таб2.Сотрудник

Проанализируем этот запрос. Во вложенном запросе Таб1 мы соединяем обороты с физической таблицей регистра сведений. Главное условие здесь: ГрафикРаботыСотрудников.Период <= ОтработанноеВремяПоСотрудникамОбороты.Период. Это позволяет «подтянуть» все записи графика, которые были сделаны ДО или В ДЕНЬ начала месяца. Используя МАКСИМУМ(ГрафикРаботыСотрудников.Period), мы находим именно ту дату изменения графика, которая является ближайшей к нашему периоду «снизу».

Важный нюанс: Срез на начало или на конец периода?

В приведенном выше примере используется ОтработанноеВремяПоСотрудникамОбороты.Период. Поскольку периодичность виртуальной таблицы задана как Месяц, это поле содержит дату начала месяца (например, 01.09.2023 00:00:00). Если график сотрудника изменился в середине месяца, данный запрос его не увидит, так как он ищет актуальное значение именно на 1-е число.

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


ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ГрафикРаботыСотрудников КАК ГрафикРаботыСотрудников
ПО ГрафикРаботыСотрудников.Сотрудник = ОтработанноеВремяПоСотрудникамОбороты.Сотрудник
    И ГрафикРаботыСотрудников.Период <= КОНЕЦПЕРИОДА(ОтработанноеВремяПоСотрудникамОбороты.Период, МЕСЯЦ)

Использование функции КОНЕЦПЕРИОДА гарантирует, что мы найдем последнее изменение графика, произошедшее в течение этого месяца или ранее.

Оптимизация производительности: использование временных таблиц

При больших объемах данных соединение «всех со всеми» по условию <= (неравенство) может привести к существенному замедлению работы SQL-сервера. Проанализируем, как ускорить выполнение задачи.

Рекомендуется сначала выбрать уникальные пары Сотрудник + Дата во временную таблицу, проиндексировать их, и только потом искать максимальные периоды в регистре сведений. Это значительно снижает нагрузку.


// 1. Помещаем уникальные ключи во временную таблицу
ВЫБРАТЬ РАЗЛИЧНЫЕ
    Обороты.Сотрудник КАК Сотрудник,
    КОНЕЦПЕРИОДА(Обороты.Период, МЕСЯЦ) КАК ДатаСреза
ПОМЕСТИТЬ ВТ_Ключи
ИЗ
    РегистрНакопления.ОтработанноеВремяПоСотрудникам.Обороты(...) КАК Обороты
;
ИНДЕКСИРОВАТЬ ПО Сотрудник, ДатаСреза
;

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

// 3. Соединяем с исходными данными и получаем График
// ... финальная выборка

Альтернативный подход: Использование СКД

Если ваша задача реализуется в рамках отчета, стоит вспомнить о мощном механизме Системы Компоновки Данных (СКД). В СКД задача «среза последних на каждую дату» решается автоматически без написания сложных вложенных запросов.

Для этого выполним следующие действия:

Создадим первый набор данных (запрос), который выбирает обороты из регистра.
Создадим второй набор данных (запрос или объект), который выбирает данные из регистра сведений ГрафикРаботыСотрудников.СрезПоследних. В параметрах виртуальной таблицы укажем &ПериодСреза.
На вкладке Связи наборов данных укажем связь от Набора 1 к Набору 2.
В условии связи соединим Сотрудник = Сотрудник, а поле Период из Набора 1 передадим в параметр &ПериодСреза Набора 2.

При таком подходе СКД сама выполнит оптимальный запрос для каждой строки первого набора данных. Это не только упрощает код запроса, но и часто работает быстрее за счет внутренних оптимизаций платформы 1С.

Возможные ошибки и риски

Проанализируем ситуацию, упомянутую в обсуждении: что если в регистре сведений на одну и ту же секунду введено две записи? Такое бывает, если периодичность регистра — «в пределах секунды». В этом случае МАКСИМУМ(Период) вернет одну и ту же дату, и при соединении с физической таблицей регистра строк станет больше (произойдет «размножение» данных).

Для предотвращения этой проблемы, если регистр может содержать несколько записей на один момент времени, необходимо добавлять в группировку и соединение дополнительные уникальные поля, такие как НомерСтроки или Регистратор, также выбирая их через МАКСИМУМ.

Также не забывайте обрабатывать пустые значения. Если для сотрудника не введен график, левое соединение вернет NULL. Рекомендуется использовать функцию ЕСТЬNULL(Таб2.ГрафикРаботы, &ЗначениеПоУмолчанию), чтобы избежать ошибок в дальнейших расчетах.