Роман Беляев

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

Задача

Есть список номенклатуры. Как мы получили этот список не так важно, важно, что он у нас есть. Необходимо, по пунктам:

1. Получить список продаж для каждой позиции номенклатуры, который будет включать в себя:
2. Дату продажи.
3. Сумму продажи.
4. Количество.
5. На каждую дату продажи получить закупочную цены этой самой номенклатуры из регистра сведений "Цены номенклатуры".
6. Проведя простые математические операции выяснить:
7. Сумму в закупке проданной номенклатуры.
8. Сумму продаж(уже получено в п.1).
9. Прибыль по каждой конкретно взятой номенклатуре.

Зачем вы изобретаете велосипед?

Следует отметить, что пример учебный и информацию о прибыли для нужной номенклатуры можно легко получить из отчетов которые уже есть в типовой конфигурации. Но сам по себе отчет очень ярко показывает саму идею обозначенную в заголовке. А реализацию используйте как вам удобно.

Суть реализации

Так как мы говорим об запросах, то как такового кода здесь не будет, будут только схемы и тексты запросов с демонстрацией ожидаемого результата. Техническая сторона вопроса подробно описана здесь собственно на этом можно и закончить. Если вы прочитали статью и у вас не осталось вопросов, дальше можно не читать. Если вам сложно понять мысль написанную там, давайте продолжим. Идея заключается в соединении таблицы регистра сведений с самой собой. Для реализации нам понадобится таблица с датами, этой таблицей у нас будет таблица продаж. Вот простой запрос, выбирающий продажи, которые мы положим во временную таблицу, я развернул периодичность до секунд так, что в целом я думаю, можно было и не пользоваться виртуальной таблицей, но ее использование в СКД, дало мне возможность, в случае необходимости ограничить продажи интервалом дат, не трогая запрос.

ВЫБРАТЬ
    ПродажиОбороты.Номенклатура,
    ПродажиОбороты.Период,
    ПродажиОбороты.КоличествоОборот,
    ПродажиОбороты.СтоимостьОборот
ПОМЕСТИТЬ Продажи
ИЗ
    РегистрНакопления.Продажи.Обороты(, , Секунда, Номенклатура В (&СписокНоменклатуры)) КАК ПродажиОбороты

Получаем что то подобное:

Номенклатура	Период	КоличествоОборот	СтоимостьОборот
Товар3	16.09 19:01:54	1	180
Товар3	20.09 19:00:44	1	180
Товар2	21.09 16:57:28	1	270
Товар2	22.09 18:57:37	1	270
Товар3	23.09 18:57:36	1	170
Товар1	24.09 18:56:35	1	540
Товар2	25.09 19:00:51	1	270
Товар2	26.09 19:00:39	1	270
Товар3	27.09 18:59:15	1	170
Товар2	29.09 11:16:32	3	579,03

Положим его во временную таблицу, он нам пригодится еще не раз. Теперь наша задача получить последнюю дату из регистра для каждой номенклатуры. Для этого добавляем еще один пакет запроса в конструкторе и в новом запросе делаем вложенный запрос, в котором соединяем нашу таблицу продаж и регистр сведений внутренним соединением по номенклатуре и периоду. Соединение по периоду должно быть <= то есть нам нужны все записи регистра цен для каждой номенклатуры у которой дата меньше или равно даты продажи конкретной номенклатуры. Выглядеть, это должно приблизительно таким образом:

ВЫБРАТЬ
        Продажи.Номенклатура КАК Номенклатура,
        МАКСИМУМ(ЦеныНоменклатуры.Период) КАК Период,
        Продажи.Период КАК ПериодПродажа
    ИЗ
        Продажи КАК Продажи
            ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
            ПО Продажи.Номенклатура = ЦеныНоменклатуры.Номенклатура
                И Продажи.Период >= ЦеныНоменклатуры.Период
    ГДЕ
        ЦеныНоменклатуры.ТипЦен = &ЗакупочныйТипЦен

    СГРУППИРОВАТЬ ПО
        Продажи.Номенклатура,
        Продажи.Период

Обратите внимание, мы группируем записи регистра сведений, получая максимальное значение даты из регистра. Выбираем все. а забираем только последнее. Логика следующая, вот результат запроса до группировки:

Номенклатура	Период	Период продажа
Товар1	06.09	05.10 16:58:34
Товар1	01.10	05.10 16:58:34
Товар2	06.09	05.10 16:58:34
Товар2	01.10	05.10 16:58:34
Товар3	29.08	05.10 16:58:34
Товар3	01.10	05.10 16:58:34
Товар1	06.09	07.10 18:57:25
Товар1	01.10	07.10 18:57:25
Товар3	29.08	09.10 18:56:38
Товар3	01.10	09.10 18:56:38
Товар1	06.09	13.10 19:00:27
Товар1	01.10	13.10 19:00:27

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

Номенклатура	Период	Период продажа
Товар1	01.10	05.10 16:58:34
Товар2	01.10	05.10 16:58:34
Товар3	01.10	05.10 16:58:34
Товар1	01.10	07.10 18:57:25
Товар3	01.10	09.10 18:56:38
Товар1	01.10	13.10 19:00:27

В результате мы получили одну запись на каждую продажу(если на дату продажи, было значение в регистре)

Связываем таблицу дат с регистром.

Теперь пришло время сделать то, ради чего было затеяно столько подготовительных действий. Мы сделаем внутреннее соединение регистра с результатом вложенного запроса(результат запроса, записи из этого же регистра) по номенклатуре и периоду и как следствие сможем получить цену для каждой даты продажи. Суть заключается в том, что у нас в регистре может быть только одна запись с таким периодом и номенклатурой как в результате вложенного запроса. И она там точно есть(мы же данные получили из этого регистра). Вот как выглядит запрос в сборе.

ВЫБРАТЬ
    ПродажиОбороты.Номенклатура,
    ПродажиОбороты.Период,
    ПродажиОбороты.КоличествоОборот,
    ПродажиОбороты.СтоимостьОборот
ПОМЕСТИТЬ Продажи
ИЗ
    РегистрНакопления.Продажи.Обороты(, , Секунда, Номенклатура В (&СписокНоменклатуры)) КАК ПродажиОбороты
;

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

    СГРУППИРОВАТЬ ПО
        Продажи.Номенклатура,
        Продажи.Период) КАК ТаблицаЦен
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
        ПО ТаблицаЦен.Номенклатура = ЦеныНоменклатуры.Номенклатура
            И ТаблицаЦен.Период = ЦеныНоменклатуры.Период
ГДЕ
    ЦеныНоменклатуры.ТипЦен = &ЗакупочныйТипЦен

Помещаем результат в таблицу ЦеныПериоды и собственно у нас почти все готово. Теперь у нас есть таблица, в которой на каждую дату продажи лежит дата закупки. Соединяем таблицу продажи с таблицей ЦеныПериоды левым соединением, вычесть, сложить умножить и получить нужные поля, проблемы составить не должно. Последний штрих: если продажа и закупка были по одной цене и их было 10 нет смысла выводить каждую позицию, сгруппируем таблицу продаж по полю период с функцией "Максимум". Если нужно знать сколько продаж сгруппировалось в строке, можно еще раз сгруппировать это же поле с функцией "Количество различных". И так вот итоговый запрос:

ВЫБРАТЬ
    ПродажиОбороты.Номенклатура,
    ПродажиОбороты.Период,
    ПродажиОбороты.КоличествоОборот,
    ПродажиОбороты.СтоимостьОборот
ПОМЕСТИТЬ Продажи
ИЗ
    РегистрНакопления.Продажи.Обороты(, , Секунда, Номенклатура В (&СписокНоменклатуры)) КАК ПродажиОбороты
;

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

    СГРУППИРОВАТЬ ПО
        Продажи.Номенклатура,
        Продажи.Период) КАК ТаблицаЦен
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
        ПО ТаблицаЦен.Номенклатура = ЦеныНоменклатуры.Номенклатура
            И ТаблицаЦен.Период = ЦеныНоменклатуры.Период
ГДЕ
    ЦеныНоменклатуры.ТипЦен = &ЗакупочныйТипЦен
;

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

Вот результат его работы если его положить в СКД: