Как получить остатки по дням на счете 51 в 1С, включая дни без движений

← На главную

В практике разработки на платформе 1С часто возникает задача построения отчетов, показывающих остатки денежных средств или взаиморасчетов на каждый день выбранного периода. Классический пример — получение ежедневных остатков по счету 51 «Расчетные счета». Основная сложность здесь заключается в том, что в днях, когда не было банковских операций, виртуальные таблицы остатков и оборотов не возвращают записей. Нам же необходимо показать актуальный остаток на каждую дату, просто «протянув» его с предыдущего дня.

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

Выявление ошибки: почему задваивались обороты в исходном запросе

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

В блоке объединения таблиц автор использовал левое соединение оборотов с остатками по следующему условию:


ЛЕВОЕ СОЕДИНЕНИЕ РегистрБухгалтерии.Хозрасчетный.Остатки(&ДатаНач, Счет = &Счет51, , ) КАК ХозрасчетныйОстатки
    ПО ХозрасчетныйОбороты.Организация = ХозрасчетныйОстатки.Организация
        И ХозрасчетныйОбороты.Субконто1 = ХозрасчетныйОстатки.Субконто1
        И ХозрасчетныйОбороты.Субконто2 = ХозрасчетныйОстатки.Субконто2

Проблема заключалась в том, что виртуальная таблица Остатки без указания конкретных субконто в параметрах или без группировки по ним возвращает детальные записи в разрезе всех субконто, определенных на счете. При левом соединении таблиц по нескольким субконто, если на какую-то дату по одному из аналитических разрезов остатка не было (или значение субконто было неопределенным/пустым), СУБД формировала избыточные строки (декартово произведение незаполненных аналитик), что приводило к дублированию строк в результирующей выборке и, как следствие, к кратному увеличению итоговой суммы оборота.

Решение проблемы в исходном коде: для исправления этой ошибки автору потребовалось исключить избыточное и некорректное соединение по аналитическим разрезам (субконто), оставив только агрегированные данные по счету в целом. Однако, как справедливо заметили участники обсуждения, такой запрос крайне неоптимален и заставит «плакать» оптимизатор запросов СУБД из-за обилия временных таблиц и сложных условий соединений.

Классическое оптимальное решение: Использование таблицы «ОстаткиИОбороты» и календаря

Чтобы не перегружать сервер СУБД расчетами «вручную» через объединение нескольких разных виртуальных таблиц, в 1С предусмотрена специализированная таблица — ОстаткиИОбороты. С ее помощью можно получить начальные, конечные остатки и обороты за одним обращением к регистру.

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

Получаем данные по движениям: выгружаем остатки и обороты с периодичностью День во временную таблицу.
Формируем календарную сетку: выбираем все даты из производственного календаря за требуемый период.
Связываем сетку с движениями: для каждого дня из календаря находим ближайшую предыдущую дату, в которой было движение по счету (используя условие связи Календарь.Дата >= Движения.Период и группировку по МАКСИМУМ(Движения.Период)).
Получаем итоговый остаток: соединяем календарные дни с полученной точкой актуальности движения и выводим конечный остаток. Если в этот день были движения, выводим реальный оборот; если движений не было — оборот будет равен нулю, а остаток перейдет с предыдущего периода.

Посмотрим на практическую реализацию этого алгоритма в коде запроса:


ВЫБРАТЬ
    ХозрасчетныйОстаткиИОбороты.Период КАК Период,
    ХозрасчетныйОстаткиИОбороты.Счет КАК Счет,
    ХозрасчетныйОстаткиИОбороты.СуммаНачальныйОстатокДт КАК СуммаНачальныйОстатокДт,
    ХозрасчетныйОстаткиИОбороты.СуммаНачальныйОстатокКт КАК СуммаНачальныйОстатокКт,
    ХозрасчетныйОстаткиИОбороты.СуммаОборотДт КАК СуммаОборотДт,
    ХозрасчетныйОстаткиИОбороты.СуммаОборотКт КАК СуммаОборотКт,
    ХозрасчетныйОстаткиИОбороты.СуммаКонечныйОстатокДт КАК СуммаКонечныйОстатокДт,
    ХозрасчетныйОстаткиИОбороты.СуммаКонечныйОстатокКт КАК СуммаКонечныйОстатокКт
ПОМЕСТИТЬ ВТ_ОстаткиИОбороты
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , Счет = &Счет, , ) КАК ХозрасчетныйОстаткиИОбороты
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ДанныеПроизводственногоКалендаря.Дата КАК Дата
ПОМЕСТИТЬ ВТ_Календарь
ИЗ
    РегистрСведений.ДанныеПроизводственногоКалендаря КАК ДанныеПроизводственногоКалендаря
ГДЕ
    ДанныеПроизводственногоКалендаря.ПроизводственныйКалендарь = &ПроизводственныйКалендарь
    И ДанныеПроизводственногоКалендаря.Дата МЕЖДУ &НачалоПериода И &КонецПериода
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_Календарь.Дата КАК Период,
    МАКСИМУМ(ГруппировкаПериода.Период) КАК ПериодСоединения,
    ВЫБОР
        КОГДА ПроверкаОборотов.Период ЕСТЬ NULL
            ТОГДА ЛОЖЬ
        ИНАЧЕ ИСТИНА
    КОНЕЦ КАК ЕстьДвиженияЗаДень
ПОМЕСТИТЬ ВТ_ПроверкаПериодов
ИЗ
    ВТ_Календарь КАК ВТ_Календарь
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОстаткиИОбороты КАК ГруппировкаПериода
        ПО ВТ_Календарь.Дата >= ГруппировкаПериода.Период
        
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОстаткиИОбороты КАК ПроверкаОборотов
        ПО ВТ_Календарь.Дата = ПроверкаОборотов.Period
СГРУППИРОВАТЬ ПО
    ВТ_Календарь.Дата,
    ВЫБОР
        КОГДА ПроверкаОборотов.Период ЕСТЬ NULL
            ТОГДА ЛОЖЬ
        ИНАЧЕ ИСТИНА
    КОНЕЦ
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_ПроверкаПериодов.Период КАК Период,
    ВТ_ОстаткиИОбороты.Счет КАК Счет,
    ВЫБОР
        КОГДА ВТ_ПроверкаПериодов.ЕстьДвиженияЗаДень
            ТОГДА ВТ_ОстаткиИОбороты.СуммаНачальныйОстатокДт
        ИНАЧЕ ВТ_ОстаткиИОбороты.СуммаКонечныйОстатокДт
    КОНЕЦ КАК СуммаНачальныйОстатокДт,
    ВЫБОР
        КОГДА ВТ_ПроверкаПериодов.ЕстьДвиженияЗаДень
            ТОГДА ВТ_ОстаткиИОбороты.СуммаНачальныйОстатокКт
        ИНАЧЕ ВТ_ОстаткиИОбороты.СуммаКонечныйОстатокКт
    КОНЕЦ КАК СуммаНачальныйОстатокКт,
    ВЫБОР
        КОГДА ВТ_ПроверкаПериодов.ЕстьДвиженияЗаДень
            ТОГДА ВТ_ОстаткиИОбороты.СуммаОборотДт
        ИНАЧЕ 0
    КОНЕЦ КАК СуммаОборотДт,
    ВЫБОР
        КОГДА ВТ_ПроверкаПериодов.ЕстьДвиженияЗаДень
            ТОГДА ВТ_ОстаткиИОбороты.СуммаОборотКт
        ИНАЧЕ 0
    КОНЕЦ КАК СуммаОборотКт,
    ВТ_ОстаткиИОбороты.СуммаКонечныйОстатокДт КАК СуммаКонечныйОстатокДт,
    ВТ_ОстаткиИОбороты.СуммаКонечныйОстатокКт КАК СуммаКонечныйОстатокКт
ИЗ
    ВТ_ПроверкаПериодов КАК ВТ_ПроверкаПериодов
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ОстаткиИОбороты КАК ВТ_ОстаткиИОбороты
        ПО ВТ_ПроверкаПериодов.ПериодСоединения = ВТ_ОстаткиИОбороты.Период
УПОРЯДОЧИТЬ ПО
    Период

Этот подход является стандартным и надежным, однако соединение по условию ВТ_Календарь.Дата >= ГруппировкаПериода.Период на больших базах данных с периодами в несколько лет может вызывать замедления из-за операции сканирования таблиц SQL-сервером. Разберем альтернативные, еще более быстрые способы решения этой задачи.

Альтернативное решение: Метод расчета нарастающего итога в запросе

Проанализируем метод расчета остатка через условное суммирование (нарастающий итог). Он позволяет СУБД работать в линейном режиме, выполняя сложение вместо тяжелого поиска максимумов предыдущих дат.

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

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

В тексте запроса этот расчет выглядит очень лаконично:


СУММА(ВЫБОР 
    КОГДА Движения.Период = &НачалоПериода 
        ТОГДА Движения.СуммаКонечныйОстаток 
    ИНАЧЕ ВЫБОР 
        КОГДА Движения.Период <= Календарь.Дата 
            ТОГДА Движения.СуммаОборот 
        ИНАЧЕ 0 
    КОНЕЦ 
КОНЕЦ) КАК СуммаОстаток

Такой подход заменяет медленные операции поиска максимальных дат простым суммированием в СУБД, что существенно повышает скорость построения отчета на крупных базах данных.

Автоматическое решение: Использование возможностей СКД

Если ваша задача — вывести остатки по дням в отчет, создаваемый на базе **Схемы компоновки данных (СКД)**, то вам вообще не нужно писать сложные многоуровневые запросы и использовать календарь. Платформа 1С берет на себя всю рутину по дополнению периодов.

Рассмотрим, как настроить автодополнение периодов в СКД за несколько шагов:

В схеме компоновки напишите простейший запрос к виртуальной таблице Хозрасчетный.ОстаткиИОбороты с периодичностью День или Авто. Никаких соединений с календарем писать не нужно.
Перейдите на вкладку «Настройки» СКД, выберите вашу структуру отчета и найдите группировку по полю периода (например, ПериодДень).
В свойствах этой группировки найдите параметр «Тип дополнения» и укажите значение «День».
В параметрах «Начало периода дополнения» и «Конец периода дополнения» укажите ваши переменные начала и окончания отчета (например, &НачалоПериода и &КонецПериода).

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

Полезный лайфхак: генерация календаря «на лету» без таблиц базы данных

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


ВЫБРАТЬ 0 КАК Х ПОМЕСТИТЬ Единицы ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 
ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 
ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9
;
ВЫБРАТЬ Десятки.Х * 10 + Единицы.Х КАК Число
ПОМЕСТИТЬ Числа
ИЗ Единицы КАК Единицы, Единицы КАК Десятки
;
ВЫБРАТЬ ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, Числа.Число) КАК Дата
ПОМЕСТИТЬ ВТ_Календарь
ИЗ Числа КАК Числа
ГДЕ ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, Числа.Число) <= &КонецПериода

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

Важные нюансы работы с аналитическими разрезами

Обратите внимание: если вам требуется получить остатки по дням не просто по счету в целом, а в разрезе конкретных субконто (например, по Банковским счетам, Контрагентам или Договорам), обычного соединения календаря с остатками будет недостаточно. По тем аналитикам, по которым вообще не было движений в выбранном интервале, данные не отобразятся.

Чтобы избежать этой ошибки, предварительно выделите все уникальные пары «Субконто - Организация», имеющие остатки на начало периода или обороты внутри него, во временную таблицу. Затем выполните декартово (полное) соединение этой таблицы аналитик с таблицей календаря. И только к полученной плотной сетке «Аналитика - Календарный день» левым соединением привязывайте таблицу остатков и оборотов для расчета нарастающих итогов. Это гарантирует абсолютно точный и полный результат по каждому аналитическому разрезу в вашей базе данных 1С.