При разработке отчетов по учету НДФЛ и заработной платы разработчики часто сталкиваются с задачей сопоставления разнородных данных по физическим лицам. Типичный пример — необходимость получить в одной таблице актуальный статус налогоплательщика, сведения о личных стандартных вычетах и вычетах на детей. Сложность заключается в том, что эти данные хранятся в разных периодических регистрах сведений, наборы физических лиц в них не совпадают, а выводить сотрудников, по которым нет никакой информации, не требуется.
Разберем по шагам, почему возникают ошибки при классических соединениях, и рассмотрим несколько надежных вариантов построения запроса.
Проанализируем типичные ошибки, которые допускаются при попытке связать срезы нескольких регистров:
НДФЛСтандартныеВычетыНаДетей, в результат попадут только те люди, у которых зарегистрированы дети. Физические лица, имеющие статус налогоплательщика или личный вычет, но не имеющие детей, будут полностью отсечены.ОБЪЕДИНИТЬ ВСЕ и последующем вызове СГРУППИРОВАТЬ ПО данные часто остаются в разных строках. Выясним причину: если в секцию группировки включены все поля выборки, включая периоды и коды вычетов, строки с NULL не схлопнутся, так как значения в полях различаются.Рассмотрим наиболее наглядный способ: возьмем справочник ФизическиеЛица в качестве базовой таблицы, присоединим к нему левыми соединениями срезы последних каждого регистра и исключим тех людей, по которым нет ни одной записи.
Посмотрим на пример текста запроса:
ВЫБРАТЬ
ФизическиеЛица.Ссылка КАК ФЛ,
Статусы.Период КАК ПериодСтатуса,
Статусы.Статус КАК Статус,
ВычетыЛичные.Период КАК ЛичныйПериод,
ВычетыЛичные.КодВычетаЛичный КАК КодВычетаЛичный,
ВычетыНаДетей.Период КАК ПериодВычетаНаДетей,
ВычетыНаДетей.КодВычета КАК КодВычетаНаДетей,
ВычетыНаДетей.КоличествоДетей КАК КоличествоДетей,
ВычетыНаДетей.ПериодЗавершения КАК ПериодЗавершения
ИЗ
Справочник.ФизическиеЛица КАК ФизическиеЛица
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СтатусФизЛицКакНалогоплательщиковНДФЛ.СрезПоследних(&ДатаКонца, ) КАК Статусы
ПО ФизическиеЛица.Ссылка = Статусы.ФизЛицо
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.НДФЛСтандартныеВычетыФизлиц.СрезПоследних(&ДатаКонца, ) КАК ВычетыЛичные
ПО ФизическиеЛица.Ссылка = ВычетыЛичные.Физлицо
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.НДФЛСтандартныеВычетыНаДетей.СрезПоследних(&ДатаКонца, ) КАК ВычетыНаДетей
ПО ФизическиеЛица.Ссылка = ВычетыНаДетей.Физлицо
ГДЕ
НЕ ФизическиеЛица.ЭтоГруппа
И НЕ ФизическиеЛица.ПометкаУдаления
И (НЕ Статусы.Статус ЕСТЬ NULL
ИЛИ НЕ ВычетыЛичные.КодВычетаЛичный ЕСТЬ NULL
ИЛИ НЕ ВычетыНаДетей.КодВычета ЕСТЬ NULL)
УПОРЯДОЧИТЬ ПО
ФизическиеЛица.Наименование
В секции ГДЕ мы используем связку условий с оператором ИЛИ. Это гарантирует, что строка попадет в отчет, если физлицо фигурирует хотя бы в одном из регистров, а полностью «пустые» записи справочника будут отфильтрованы.
Если справочник физических лиц содержит десятки тысяч элементов, левое соединение со всей таблицей справочника может выполняться медленно. Разберем более производительный подход: сначала соберем во временную таблицу только тех физических лиц, у которых есть записи в интересующих нас регистрах.
Для этого применим оператор ОБЪЕДИНИТЬ (без ключевого слова ВСЕ) — он автоматически удалит дубликаты идентификаторов:
ВЫБРАТЬ
Статусы.ФизЛицо КАК ФизЛицо
ПОМЕСТИТЬ ВТ_НужныеФизЛица
ИЗ
РегистрСведений.СтатусФизЛицКакНалогоплательщиковНДФЛ.СрезПоследних(&ДатаКонца, ) КАК Статусы
ОБЪЕДИНИТЬ
ВЫБРАТЬ
ВычетыЛичные.Физлицо
ИЗ
РегистрСведений.НДФЛСтандартныеВычетыФизлиц.СрезПоследних(&ДатаКонца, ) КАК ВычетыЛичные
ОБЪЕДИНИТЬ
ВЫБРАТЬ
ВычетыНаДетей.Физлицо
ИЗ
РегистрСведений.НДФЛСтандартныеВычетыНаДетей.СрезПоследних(&ДатаКонца, ) КАК ВычетыНаДетей
ИНДЕКСИРОВАТЬ ПО
ФизЛицо
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СписокФЛ.ФизЛицо КАК ФЛ,
Статусы.Период КАК ПериодСтатуса,
Статусы.Статус КАК Статус,
ВычетыЛичные.КодВычетаЛичный КАК КодВычетаЛичный,
ВычетыНаДетей.КодВычета КАК КодВычетаНаДетей,
ВычетыНаДетей.КоличествоДетей КАК КоличествоДетей
ИЗ
ВТ_НужныеФизЛица КАК СписокФЛ
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СтатусФизЛицКакНалогоплательщиковНДФЛ.СрезПоследних(&ДатаКонца, ) КАК Статусы
ПО СписокФЛ.ФизЛицо = Статусы.ФизЛицо
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.НДФЛСтандартныеВычетыФизлиц.СрезПоследних(&ДатаКонца, ) КАК ВычетыЛичные
ПО СписокФЛ.ФизЛицо = ВычетыЛичные.Физлицо
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.НДФЛСтандартныеВычетыНаДетей.СрезПоследних(&ДатаКонца, ) КАК ВычетыНаДетей
ПО СписокФЛ.ФизЛицо = ВычетыНаДетей.Физлицо
УПОРЯДОЧИТЬ ПО
СписокФЛ.ФизЛицо.Наименование
Такой подход избавляет от необходимости сканировать справочник ФизическиеЛица и писать сложные проверки на NULL в блоке условий.
Если требуется объединить срезы регистров без использования соединений, можно использовать объединение с последующей агрегацией. Однако группировать нужно исключительно по физическому лицу, а ко всем остальным ресурсам и реквизитам применять функции МАКСИМУМ:
ВЫБРАТЬ
Статусы.ФизЛицо КАК ФЛ,
Статусы.Статус КАК Статус,
Статусы.Период КАК ПериодСтатуса,
NULL КАК КодВычетаЛичный,
NULL КАК КоличествоДетей
ПОМЕСТИТЬ ВТ_Сводная
ИЗ
РегистрСведений.СтатусФизЛицКакНалогоплательщиковНДФЛ.СрезПоследних(&ДатаКонца, ) КАК Статусы
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВычетыЛичные.Физлицо,
NULL,
NULL,
ВычетыЛичные.КодВычетаЛичный,
NULL
ИЗ
РегистрСведений.НДФЛСтандартныеВычетыФизлиц.СрезПоследних(&ДатаКонца, ) КАК ВычетыЛичные
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ВычетыНаДетей.Физлицо,
NULL,
NULL,
NULL,
ВычетыНаДетей.КоличествоДетей
ИЗ
РегистрСведений.НДФЛСтандартныеВычетыНаДетей.СрезПоследних(&ДатаКонца, ) КАК ВычетыНаДетей
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.ФЛ КАК ФЛ,
МАКСИМУМ(ВТ.Статус) КАК Статус,
МАКСИМУМ(ВТ.ПериодСтатуса) КАК ПериодСтатуса,
МАКСИМУМ(ВТ.КодВычетаЛичный) КАК КодВычетаЛичный,
МАКСИМУМ(ВТ.КоличествоДетей) КАК КоличествоДетей
ИЗ
ВТ_Сводная КАК ВТ
СГРУППИРОВАТЬ ПО
ВТ.ФЛ
Обратите внимание: данный способ корректен только в том случае, если на одного человека в каждом регистре приходится строго одна актуальная запись. Если у физического лица зарегистрировано несколько вычетов на детей (например, вычеты на первого и на второго ребенка имеют разные коды), функция МАКСИМУМ оставит только одно из значений.
Рассмотрим специфические нюансы конфигураций 1С, которые влияют на корректность итоговых данных:
НДФЛСтандартныеВычетыНаДетей измерением является не только физическое лицо, но и КодВычета. Если у сотрудника двое или трое детей, срез последних вернет несколько строк. При левом соединении статус налогоплательщика и личный вычет продублируются для каждого кода детского вычета. В СКД эту проблему лучше всего решать разделением на отдельные наборы данных и связыванием их по полю ФизЛицо.ЕСТЬNULL:
ЕСТЬNULL(Статусы.Статус, ЗНАЧЕНИЕ(Перечисление.СтатусыНалогоплательщиковПоНДФЛ.Резидент)) КАК Статус
СрезПоследних возвращает последнюю запись по дате, даже если срок действия вычета уже истек. Для получения только действующих на дату среза вычетов обязательно проверяйте поле ПериодЗавершения:
ГДЕ
ВычетыНаДетей.ПериодЗавершения ЕСТЬ NULL
ИЛИ ВычетыНаДетей.ПериодЗавершения = ДАТАВРЕМЯ(1, 1, 1)
ИЛИ ВычетыНаДетей.ПериодЗавершения >= &ДатаКонца