Остатки с разбивкой по периодам в запросе 1С

Часто перед программистом 1с возникает следующая задача: в запросе требуется взять остатки регистра бухгалтерии или накопления по периодам.

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

Казалось бы сделать это внутри запроса несложно: нужно взять виртуальную таблицу ОстаткиИОбороты, выбрать разбивку на нужные периоды и все. Но такой метод не сработает из-за одной неприятной особенности ОстатковИОборотов, в ней не хранятся остатки по периодам для тех позиций, по которым не было оборотов.

Пример: Контрагент Иванов должен нам 100 рублей на начало года, в январе, феврале и марте его задолженность оставалась неизменной. Берем таблицу Остатки и обороты регистра бухгалтерии Хозрасчетный по счету 62 за первый квартал, в периодичности указываем значение Месяц. Выполняем запрос и получаем следующий результат:

Как мы видим взялись остатки только крайних периодов, а остатки февраля выпали. Что бы решить данную проблему, остатки на каждый период придется рассчитывать.

Ниже приведен запрос для конфигурации Бухгалтерия 2.0 (он также заработает для Бухгалтерии 3.0 если в запросе заменить регистр РегламентированныйПроизводственныйКалендарь на регистр ДанныеПроизводственногоКалендаря) в котором считаются остатки с разбивкой по месяцам по счету 62.

ВЫБРАТЬ РАЗЛИЧНЫЕ
    НАЧАЛОПЕРИОДА(РегламентированныйПроизводственныйКалендарь.ДатаКалендаря, МЕСЯЦ) КАК Месяц
ПОМЕСТИТЬ Календарь
ИЗ
    РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РегламентированныйПроизводственныйКалендарь
ГДЕ
    РегламентированныйПроизводственныйКалендарь.ДатаКалендаря МЕЖДУ &ДатаНач И &ДатаКон
;

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

СГРУППИРОВАТЬ ПО
    Календарь.Месяц,
    ХозрасчетныйОстатки.Организация,
    ХозрасчетныйОстатки.Субконто1,
    ХозрасчетныйОстатки.Субконто2

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

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

СГРУППИРОВАТЬ ПО
    Календарь.Месяц,
    ХозрасчетныйОбороты.Организация,
    ХозрасчетныйОбороты.Субконто1,
    ХозрасчетныйОбороты.Субконто2
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    НАЧАЛОПЕРИОДА(ХозрасчетныйОбороты.Период, МЕСЯЦ) КАК Месяц,
    ХозрасчетныйОбороты.Организация,
    СУММА(ХозрасчетныйОбороты.СуммаОборот) КАК СуммаОборот,
    ХозрасчетныйОбороты.Субконто2 КАК ДоговорКонтрагента,
    ХозрасчетныйОбороты.Субконто1 КАК Контрагент
ПОМЕСТИТЬ ОборотыЗаПериод
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Обороты(&ДатаНач, &ДатаКон, Месяц, Счет В ИЕРАРХИИ (&Счет62), , Организация = &Организация, , ) КАК ХозрасчетныйОбороты

СГРУППИРОВАТЬ ПО
    НАЧАЛОПЕРИОДА(ХозрасчетныйОбороты.Период, МЕСЯЦ),
    ХозрасчетныйОбороты.Организация,
    ХозрасчетныйОбороты.Субконто2,
    ХозрасчетныйОбороты.Субконто1
;

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

СГРУППИРОВАТЬ ПО
    ОстаткиНаНачало.Организация,
    ОстаткиНаНачало.Месяц,
    ОстаткиНаНачало.Контрагент,
    ОстаткиНаНачало.ДоговорКонтрагента
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ОстаткиНаНачало.Организация,
    МАКСИМУМ(ОстаткиНаНачало.СуммаОстатокНаНачало) КАК СуммаОстатокНаНачало,
    ОстаткиНаНачало.Месяц,
    СУММА(ОборотыЗаПериодНаКонец.СуммаОборот) КАК СуммаОборотНаКонец,
    ОстаткиНаНачало.Контрагент,
    ОстаткиНаНачало.ДоговорКонтрагента
ПОМЕСТИТЬ РасчетОстатковНаКонец
ИЗ
    ОстаткиНаНачало КАК ОстаткиНаНачало
        ЛЕВОЕ СОЕДИНЕНИЕ ОборотыЗаПериод КАК ОборотыЗаПериодНаКонец
        ПО ОстаткиНаНачало.Организация = ОборотыЗаПериодНаКонец.Организация
            И (ОборотыЗаПериодНаКонец.Месяц МЕЖДУ НАЧАЛОПЕРИОДА(&ДатаНач, МЕСЯЦ) И КОНЕЦПЕРИОДА(ОстаткиНаНачало.Месяц, МЕСЯЦ))
            И ОстаткиНаНачало.Контрагент = ОборотыЗаПериодНаКонец.Контрагент
            И ОстаткиНаНачало.ДоговорКонтрагента = ОборотыЗаПериодНаКонец.ДоговорКонтрагента

СГРУППИРОВАТЬ ПО
    ОстаткиНаНачало.Организация,
    ОстаткиНаНачало.Месяц,
    ОстаткиНаНачало.Контрагент,
    ОстаткиНаНачало.ДоговорКонтрагента
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    РасчетОстатковНаНачало.Организация КАК Организация,
    РасчетОстатковНаНачало.Месяц КАК Месяц,
    РасчетОстатковНаНачало.СуммаОстатокНаНачало + ЕСТЬNULL(РасчетОстатковНаНачало.СуммаОборотНаНачало, 0) КАК ОстатокНаНачало,
    NULL КАК ОстатокНаКонец,
    РасчетОстатковНаНачало.Контрагент КАК Контрагент,
    РасчетОстатковНаНачало.ДоговорКонтрагента КАК ДоговорКонтрагента
ПОМЕСТИТЬ ОбщиеОстатки
ИЗ
    РасчетОстатковНаНачало КАК РасчетОстатковНаНачало

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

ВЫБРАТЬ
    РасчетОстатковНаКонец.Организация,
    РасчетОстатковНаКонец.Месяц,
    NULL,
    РасчетОстатковНаКонец.СуммаОстатокНаНачало + ЕСТЬNULL(РасчетОстатковНаКонец.СуммаОборотНаКонец, 0),
    РасчетОстатковНаКонец.Контрагент,
    РасчетОстатковНаКонец.ДоговорКонтрагента
ИЗ
    РасчетОстатковНаКонец КАК РасчетОстатковНаКонец
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ОбщиеОстатки.Организация КАК Организация,
    ОбщиеОстатки.Месяц КАК Месяц,
    СУММА(ОбщиеОстатки.ОстатокНаНачало) КАК ОстатокНаНачало,
    СУММА(ОбщиеОстатки.ОстатокНаКонец) КАК ОстатокНаКонец,
    ОбщиеОстатки.Контрагент КАК Контрагент,
    ОбщиеОстатки.ДоговорКонтрагента КАК ДоговорКонтрагента
ИЗ
    ОбщиеОстатки КАК ОбщиеОстатки

СГРУППИРОВАТЬ ПО
    ОбщиеОстатки.Организация,
    ОбщиеОстатки.Месяц,
    ОбщиеОстатки.Контрагент,
    ОбщиеОстатки.ДоговорКонтрагента

УПОРЯДОЧИТЬ ПО
    Организация,
    Контрагент,
    ДоговорКонтрагента,
    Месяц

Разберем приведенный запрос по пакетам:

1. Календарь

   Здесь мы используя регистр РегламентированныйПроизводственныйКалендарь получаем все нужные нам периоды.

2. ОстаткиНаНачало

   Получаем остатки на начало выбранного периода, а так же если остатков не было, а обороты за весь период были, добавляем нулевые остатки.

3. ОборотыЗаПериод

   Получаем обороты за весь период с разбивкой по месяцам.

4. РасчетОстатковНаНачало

   Сводим в одну таблицу начальный остаток и обороты за каждый месяц для расчета начальных остатков по периодам.

5. РасчетОстатковНаКонец

   Сводим в одну таблицу начальный остаток и обороты за каждый месяц для расчета конечных остатков по периодам.

6. ОбщиеОстатки

   Рассчитываем начальные и конечные остатки по периодам путем прибавления оборотов.