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