Как в 1С:ЗУП сформировать отчет по начислениям и удержаниям сотрудников с сравнением данных за прошлый месяц?

← На главную

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

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

Перед нами стоит задача сформировать выборку из регистров расчета Начисления и Удержания, которая должна содержать следующую информацию по каждому сотруднику:

Сотрудник: Физическое лицо, которому производились начисления или удержания.
Месяц: Месяц, за который производятся расчеты.
Сумма начислено: Общая сумма начислений за месяц.
Сумма удержано: Общая сумма удержаний за месяц.
Итоговая сумма: Рассчитывается как СуммаНачислено - СуммаУдержано.
Итоговая сумма за прошлый месяц: Соответствующая итоговая сумма за предыдущий календарный месяц.
Сумма увеличилась: Флаг (Да/Нет) показывающий, увеличилась ли итоговая сумма по сравнению с предыдущим периодом.
Итоги по сотруднику: На уровне сотрудника требуется рассчитывать только итоговую сумму.
Сортировка: Месяцы должны быть выведены по убыванию.

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

Шаг 1: Подготовка данных – объединяем начисления и удержания

Для начала нам потребуется собрать данные из регистров расчета Начисления и Удержания в единую временную таблицу. Это необходимо, поскольку начисления и удержания хранятся в разных регистрах, но для анализа их нужно представить вместе. Мы используем оператор ОБЪЕДИНИТЬ ВСЕ (UNION ALL) для построчного добавления записей из одного регистра к записям из другого. При этом мы создаем "пустые" колонки для противоположного типа операции, заполняя их нулями, чтобы структура данных была единообразной.


ВЫБРАТЬ
    Начисления.ФизическоеЛицо КАК ФизическоеЛицо,
    Начисления.Месяц КАК Месяц,
    СУММА(Начисления.Результат) КАК СуммаНачислено,
    0 КАК СуммаУдержано
ПОМЕСТИТЬ втИсходныеДанные
ИЗ
    РегистрРасчета.Начисления КАК Начисления
СГРУППИРОВАТЬ ПО
    Начисления.ФизическоеЛицо,
    Начисления.Месяц

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    Удержания.ФизическоеЛицо,
    Удержания.ПериодРегистрации,
    0,
    Удержания.Результат
ИЗ
    РегистрРасчета.Удержания КАК Удержания

Рассмотрим подробнее, что происходит в этом блоке:

Первая часть запроса: Мы выбираем ФизическоеЛицо, Месяц и СУММА(Начисления.Результат), которая будет нашей СуммаНачислено, из регистра РегистрРасчета.Начисления. Важно заметить, что для поля СуммаУдержано мы искусственно создаем колонку, заполняя ее нулями (0 КАК СуммаУдержано). Это необходимо для того, чтобы обе части оператора ОБЪЕДИНИТЬ ВСЕ имели одинаковый набор и типы полей.
Оператор ОБЪЕДИНИТЬ ВСЕ: Этот оператор объединяет результаты двух запросов, добавляя строки второго запроса в конец первого. При этом он не удаляет дубликаты, что важно для нашей задачи.
Вторая часть запроса: Аналогично, мы выбираем ФизическоеЛицо, ПериодРегистрации (который в регистре удержаний соответствует месяцу) и Удержания.Результат (как СуммаУдержания) из регистра РегистрРасчета.Удержания. Для поля СуммаНачислено здесь также создается колонка, заполненная нулями (0).

В результате, временная таблица втИсходныеДанные будет содержать строки, где либо СуммаНачислено имеет значение, а СуммаУдержано равна нулю, либо наоборот. Это позволит нам на следующем шаге корректно сгруппировать данные.

Шаг 2: Группировка объединенных данных по сотруднику и месяцу

После объединения данных из начислений и удержаний у нас могут быть отдельные строки для одного и того же сотрудника и месяца (например, одна строка с начислениями за январь и одна строка с удержаниями за тот же январь для одного сотрудника). Чтобы получить общую сумму начислений и удержаний за каждый месяц для каждого сотрудника, нам нужно сгруппировать эти данные и просуммировать соответствующие колонки.


ВЫБРАТЬ
    втИсходныеДанные.ФизическоеЛицо КАК ФизическоеЛицо,
    втИсходныеДанные.Месяц КАК Месяц,
    СУММА(втИсходныеДанные.СуммаНачислено) КАК СуммаНачислено,
    СУММА(втИсходныеДанные.СуммаУдержано) КАК СуммаУдержано
ПОМЕСТИТЬ втГруппировка
ИЗ
    втИсходныеДанные КАК втИсходныеДанные
СГРУППИРОВАТЬ ПО
    втИсходныеДанные.ФизическоеЛицо,
    втИсходныеДанные.Месяц

Эта временная таблица втГруппировка теперь содержит агрегированные (суммированные) данные по начислениям и удержаниям для каждого сотрудника за каждый месяц. Например, если в втИсходныеДанные были две строки для сотрудника Иванова за январь (одна с начислениями, другая с удержаниями), то в втГруппировка появится одна строка с общей суммой начислений и общей суммой удержаний за январь для Иванова.

Шаг 3: Расчет итоговой суммы за текущий период

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


ВЫБРАТЬ
    втГруппировка.ФизическоеЛицо КАК ФизическоеЛицо,
    втГруппировка.Месяц КАК Месяц,
    втГруппировка.СуммаНачислено КАК СуммаНачислено,
    втГруппировка.СуммаУдержано КАК СуммаУдержано,
    втГруппировка.СуммаНачислено - втГруппировка.СуммаУдержано КАК ИтоговаяСумма
ПОМЕСТИТЬ втИтогиПоМесяцам
ИЗ
    втГруппировка КАК втГруппировка

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

Шаг 4: Получение итоговой суммы за прошлый месяц и определение динамики

Это ключевой и самый сложный шаг в решении задачи. Для получения данных за предыдущий месяц мы используем прием ЛЕВОЕ СОЕДИНЕНИЕ временной таблицы втИтогиПоМесяцам с самой собой. Это позволяет нам в одной строке иметь данные как за текущий, так и за предыдущий месяц для одного и того же сотрудника.


ВЫБРАТЬ
    втИтогиПоМесяцам.ФизическоеЛицо КАК Сотрудник,
    втИтогиПоМесяцам.Месяц КАК Месяц,
    втИтогиПоМесяцам.СуммаНачислено КАК СуммаНачислено,
    втИтогиПоМесяцам.СуммаУдержано КАК СуммаУдержано,
    втИтогиПоМесяцам.ИтоговаяСумма КАК ИтоговаяСумма,
    ЕСТЬNULL(втИтогиПоМесяцамПрошлыйМесяц.ИтоговаяСумма, 0) КАК ИтоговаяСуммаЗаПрошлыйМесяц,
    ВЫБОР
        КОГДА ЕСТЬNULL(втИтогиПоМесяцамПрошлыйМесяц.ИтоговаяСумма, 0) < втИтогиПоМесяцам.ИтоговаяСумма
            ТОГДА ИСТИНА
        ИНАЧЕ ЛОЖЬ
    КОНЕЦ КАК СуммаУвеличилась
ИЗ
    втИтогиПоМесяцам КАК втИтогиПоМесяцам
        ЛЕВОЕ СОЕДИНЕНИЕ втИтогиПоМесяцам КАК втИтогиПоМесяцамПрошлыйМесяц
        ПО втИтогиПоМесяцам.ФизическоеЛицо = втИтогиПоМесяцамПрошлыйМесяц.ФизическоеЛицо
        И (втИтогиПоМесяцам.Месяц = ДОБАВИТЬКДАТЕ(втИтогиПоМесяцамПрошлыйМесяц.Месяц, МЕСЯЦ, -1))

Давайте подробнее разберем этот блок:

ЛЕВОЕ СОЕДИНЕНИЕ: Мы присоединяем каждую запись из втИтогиПоМесяцам (представляющую данные за текущий месяц и алиасированную как втИтогиПоМесяцам) к записи из этой же таблицы (алиасированной как втИтогиПоМесяцамПрошлыйМесяц), которая соответствует предыдущему месяцу для того же ФизическогоЛица.
Условие соединения (ПО):
- втИтогиПоМесяцам.ФизическоеЛицо = втИтогиПоМесяцамПрошлыйМесяц.ФизическоеЛицо: Очевидно, что данные должны относиться к одному и тому же сотруднику.
- (втИтогиПоМесяцам.Месяц = ДОБАВИТЬКДАТЕ(втИтогиПоМесяцамПрошлыйМесяц.Месяц, МЕСЯЦ, -1)): Это ключевая часть условия. Мы связываем текущий месяц записи с датой, которая на один месяц меньше, чем месяц из записи, представляющей предыдущий период. Функция ДОБАВИТЬКДАТЕ() является универсальным инструментом для манипуляций с датами в 1С. Например, если втИтогиПоМесяцамПрошлыйМесяц.Месяц равен 01.02.2023, то ДОБАВИТЬКДАТЕ(01.02.2023, МЕСЯЦ, -1) вернет 01.01.2023. Таким образом, мы ищем для записи за февраль запись за январь.
ЕСТЬNULL(): Если для какого-либо сотрудника за предыдущий месяц не было данных (например, он только начал работать или в тот месяц не было начислений/удержаний), левое соединение вернет NULL для полей из втИтогиПоМесяцамПрошлыйМесяц. Функция ЕСТЬNULL(втИтогиПоМесяцамПрошлыйМесяц.ИтоговаяСумма, 0) позволяет подставить 0 вместо NULL, что предотвратит ошибки в расчетах и сделает отчет более понятным.
ВЫБОР КОГДА для СуммаУвеличилась: Мы используем условный оператор для определения, увеличилась ли итоговая сумма. Если текущая ИтоговаяСумма больше ИтоговаяСуммаЗаПрошлыйМесяц, то флаг СуммаУвеличилась устанавливается в ИСТИНА, иначе – в ЛОЖЬ.

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

Шаг 5: Добавление итогов по сотруднику и сортировка

Для завершения отчета нам осталось добавить групповые итоги по сотруднику (как было запрошено, только итоговую сумму) и отсортировать месяцы в убывающем порядке. Это делается с помощью секций ИТОГИ ПО и УПОРЯДОЧИТЬ ПО в финальном запросе. Мы обернем результат предыдущего шага во вложенный запрос, чтобы применить к нему эти агрегирующие и сортирующие операторы.


ВЫБРАТЬ
    РезультатЗаМесяц.Сотрудник,
    РезультатЗаМесяц.Месяц,
    РезультатЗаМесяц.СуммаНачислено,
    РезультатЗаМесяц.СуммаУдержано,
    РезультатЗаМесяц.ИтоговаяСумма,
    РезультатЗаМесяц.ИтоговаяСуммаЗаПрошлыйМесяц,
    РезультатЗаМесяц.СуммаУвеличилась
ИЗ
    (ВЫБРАТЬ
        втИтогиПоМесяцам.ФизическоеЛицо КАК Сотрудник,
        втИтогиПоМесяцам.Месяц КАК Месяц,
        втИтогиПоМесяцам.СуммаНачислено КАК СуммаНачислено,
        втИтогиПоМесяцам.СуммаУдержано КАК СуммаУдержано,
        втИтогиПоМесяцам.ИтоговаяСумма КАК ИтоговаяСумма,
        ЕСТЬNULL(втИтогиПоМесяцамПрошлыйМесяц.ИтоговаяСумма, 0) КАК ИтоговаяСуммаЗаПрошлыйМесяц,
        ВЫБОР
            КОГДА ЕСТЬNULL(втИтогиПоМесяцамПрошлыйМесяц.ИтоговаяСумма, 0) < втИтогиПоМесяцам.ИтоговаяСумма
                ТОГДА ИСТИНА
            ИНАЧЕ ЛОЖЬ
        КОНЕЦ КАК СуммаУвеличилась
    ИЗ
        втИтогиПоМесяцам КАК втИтогиПоМесяцам
            ЛЕВОЕ СОЕДИНЕНИЕ втИтогиПоМесяцам КАК втИтогиПоМесяцамПрошлыйМесяц
            ПО втИтогиПоМесяцам.ФизическоеЛицо = втИтогиПоМесяцамПрошлыйМесяц.ФизическоеЛицо
            И (втИтогиПоМесяцам.Месяц = ДОБАВИТЬКДАТЕ(втИтогиПоМесяцамПрошлыйМесяц.Месяц, МЕСЯЦ, -1))
    ) КАК РезультатЗаМесяц
УПОРЯДОЧИТЬ ПО
    РезультатЗаМесяц.Месяц УБЫВ
ИТОГИ
    СУММА(РезультатЗаМесяц.ИтоговаяСумма)
ПО
    РезультатЗаМесяц.Сотрудник

Разберем, что мы добавили:

Вложенный запрос: Весь результат предыдущего запроса (который формирует данные по текущему и предыдущему месяцам) теперь помещен во вложенный запрос с алиасом РезультатЗаМесяц. Это позволяет нам применять к нему агрегацию и сортировку как к единому источнику данных.
УПОРЯДОЧИТЬ ПО РезультатЗаМесяц.Месяц УБЫВ: Эта секция сортирует все строки в отчете по полю Месяц в убывающем порядке, то есть от самого нового месяца к самому старому.
ИТОГИ СУММА(РезультатЗаМесяц.ИтоговаяСумма) ПО РезультатЗаМесяц.Сотрудник: Секция ИТОГИ добавляется в самый конец запроса и позволяет агрегировать данные на более высоком уровне. В нашем случае мы просим систему рассчитать сумму поля ИтоговаяСумма по каждому Сотруднику. 1С автоматически добавит дополнительные строки в результат запроса, содержащие эти итоговые суммы.
Обратите внимание, что требование "На уровне сотрудника рассчитывать только итоговую сумму" достигается тем, что мы указали только СУММА(РезультатЗаМесяц.ИтоговаяСумма) в секции ИТОГИ. Для строк-итогов по сотруднику остальные поля (например, Месяц, СуммаНачислено и т.д.) будут иметь значение NULL или их тип значения по умолчанию, так как для них не указана функция агрегации.

Заключение

Мы успешно построили комплексный запрос, который позволяет получить детальный отчет по начислениям и удержаниям сотрудников с важным аналитическим показателем – сравнением с предыдущим месяцем. Данный подход является универсальным и может быть адаптирован для различных задач анализа данных в регистрах расчетов 1С, а понимание принципов ОБЪЕДИНИТЬ ВСЕ, ЛЕВОЕ СОЕДИНЕНИЕ с самим собой и функции ДОБАВИТЬКДАТЕ() значительно расширит ваши возможности по формированию сложных отчетов в 1С.