При работе с данными бухгалтерского учета часто возникает задача получить динамику остатков по счетам за несколько периодов (например, по месяцам или годам), но с определенным условием. Сложность появляется, когда это условие нужно наложить не на само субконто, а на один из его реквизитов, хранящийся в связанном справочнике. Например, получить остатки по основным средствам, у которых в дополнительном реквизите указан определенный признак.
Рассмотрим на конкретном примере, как правильно составить такой запрос к виртуальной таблице РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты, проанализируем частые ошибки и предложим оптимальные решения.
Представим, что стоит задача получить конечные остатки по количеству "Коров" на конец каждого года. "Корова" в данном случае — это некий признак, хранящийся в дополнительном реквизите справочника "Основные средства", который выступает в роли Субконто1.
Начинающий разработчик может написать следующий запрос:
ВЫБРАТЬ
СУММА(ХозрасчетныйОстаткиИОбороты.КоличествоКонечныйОстаток) КАК КоличествоКонечныйОстаток,
ХозрасчетныйОстаткиИОбороты.Период КАК Период
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(, , Год, Движения, , , ) КАК ХозрасчетныйОстаткиИОбороты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ОсновныеСредства.ДополнительныеРеквизиты КАК ОсновныеСредстваДополнительныеРеквизиты
ПО (ХозрасчетныйОстаткиИОбороты.Субконто1 = ОсновныеСредстваДополнительныеРеквизиты.Ссылка)
ГДЕ
ОсновныеСредстваДополнительныеРеквизиты.Значение.Наименование ПОДОБНО "%Корова%"
СГРУППИРОВАТЬ ПО
ХозрасчетныйОстаткиИОбороты.Период
При выполнении такого запроса результат, скорее всего, будет некорректным и не совпадет с данными стандартных отчетов. Давайте разберем, почему так происходит.
ОстаткиИОбороты(, , Год, ...). Эти параметры отвечают за НачалоПериода и КонецПериода. Если их не указать, система будет рассчитывать данные за всю историю ведения учета, что почти всегда приводит к неверным результатам и огромной нагрузке на систему. Всегда необходимо явно задавать интервал, за который вы хотите получить данные.ГДЕ. При большом объеме данных это может быть очень медленно. Платформа сначала выполнит ресурсоемкий расчет остатков и оборотов для всех основных средств, а затем отбросит большую часть из них.СУММА(КоличествоКонечныйОстаток) при группировке по периоду является верным, если вы хотите получить итоговую сумму по всем субконто, удовлетворяющим условию. Виртуальная таблица возвращает строки для каждой уникальной комбинации измерений (включая субконто). Если у вас 10 разных основных средств с признаком "Корова", таблица вернет 10 строк для одного периода. Чтобы получить общий остаток, их действительно нужно просуммировать. Однако, если данные все равно не сходятся, причина, скорее всего, в отсутствии границ периода.Первый шаг к правильному результату — исправить очевидные ошибки в запросе. Добавим параметры для периода и сделаем код более читаемым.
Пошаговые действия:
&НачалоПериода и &КонецПериода.МетодДополнения. Значение Движения означает, что в результат попадут только те периоды (годы), в которых были какие-либо движения. Если корова числилась на балансе, но в течение года с ней ничего не происходило, этот год может не попасть в выборку. Чтобы видеть остатки на конец каждого периода в интервале, лучше использовать значение ДвиженияИГраницыПериода.Исправленный и более корректный вариант запроса будет выглядеть так:
ВЫБРАТЬ
ХозрасчетныйОстаткиИОбороты.Период КАК Период,
СУММА(ХозрасчетныйОстаткиИОбороты.КоличествоКонечныйОстаток) КАК КоличествоКонечныйОстаток
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(
&НачалоПериода,
&КонецПериода,
Год,
ДвиженияИГраницыПериода,
, // Условие по счету, если нужно
,
) КАК ХозрасчетныйОстаткиИОбороты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ОсновныеСредства.ДополнительныеРеквизиты КАК ОсновныеСредстваДополнительныеРеквизиты
ПО (ХозрасчетныйОстаткиИОбороты.Субконто1 = ОсновныеСредстваДополнительныеРеквизиты.Ссылка)
ГДЕ
ОсновныеСредстваДополнительныеРеквизиты.Значение.Наименование ПОДОБНО "%Корова%"
И ХозрасчетныйОстаткиИОбороты.Субконто1.ВидСубконто = &ВидСубконтоОсновныеСредства
СГРУППИРОВАТЬ ПО
ХозрасчетныйОстаткиИОбороты.Период
УПОРЯДОЧИТЬ ПО
Период
Важное замечание: мы добавили условие на ВидСубконто. Это хорошая практика, так как на одном счете могут использоваться разные виды субконто, и соединение по полю Субконто1 может дать неверный результат, если там окажется ссылка не на справочник "ОсновныеСредства".
Чтобы запрос работал быстрее, особенно на больших базах, рекомендуется сначала выбрать все необходимые нам субконто во временную таблицу или вложенный запрос, а затем передать этот список в параметры виртуальной таблицы. Это позволит платформе 1С и СУБД рассчитать остатки только для нужных нам аналитических разрезов.
Посмотрим на пример:
ВЫБРАТЬ
ХозрасчетныйОстаткиИОбороты.Период КАК Период,
СУММА(ХозрасчетныйОстаткиИОбороты.КоличествоКонечныйОстаток) КАК КоличествоКонечныйОстаток
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(
&НачалоПериода,
&КонецПериода,
Год,
ДвиженияИГраницыПериода,
,
Субконто1 В
(ВЫБРАТЬ
ОсновныеСредстваДополнительныеРеквизиты.Ссылка
ИЗ
Справочник.ОсновныеСредства.ДополнительныеРеквизиты КАК ОсновныеСредстваДополнительныеРеквизиты
ГДЕ
ОсновныеСредстваДополнительныеРеквизиты.Значение.Наименование ПОДОБНО "%Корова%"),
) КАК ХозрасчетныйОстаткиИОбороты
СГРУППИРОВАТЬ ПО
ХозрасчетныйОстаткиИОбороты.Период
УПОРЯДОЧИТЬ ПО
Период
Этот подход значительно эффективнее. Мы передаем условие отбора напрямую в параметр Условие (шестой параметр) виртуальной таблицы. СУБД сначала выполнит простой вложенный запрос к справочнику, получит список нужных основных средств и только для них будет производить сложный расчет остатков и оборотов.
Чтобы избежать путаницы, проясним несколько ключевых понятий, которые обсуждались на форуме.
Остаток на конец периода vs. Остаток на дату
По своей сути, остаток на конец периода (например, на конец 31 января) — это то же самое, что и остаток на определенный момент времени (на 31.01.XXXX 23:59:59). Разница в инструментах, которые мы используем для их получения:
РегистрБухгалтерии.ИмяРегистра.ОстаткиИОбороты() — возвращает поля ...НачальныйОстаток, ...КонечныйОстаток, ...Оборот. Идеально подходит для получения динамики по периодам. Конечный остаток рассчитывается на конец каждого периода (месяца, года), указанного в параметре Периодичность.РегистрБухгалтерии.ИмяРегистра.Остатки() — возвращает остаток на один конкретный момент времени, который передается в параметре ДатаСреза.Границы периода
При работе с датами в 1С важно помнить о границах. Остаток на конец дня, например, 31.01.2023, — это остаток на момент времени `31.01.2023 23:59:59`. В запросах для точного указания конца дня часто используют функцию КОНЕЦПЕРИОДА(&Дата, "ДЕНЬ"). Это гарантирует, что все движения, сделанные в течение этого дня, будут учтены. Аналогично, остаток на конец 31 января будет равен остатку на начало 1 февраля.
Подводя итог, для получения динамики остатков с отбором по реквизитам субконто всегда используйте виртуальную таблицу ОстаткиИОбороты, обязательно указывайте интервал дат и применяйте отборы наиболее оптимальным способом — через параметры самой таблицы, используя вложенные запросы.