Иногда требуется запросом получить из периодического регистра сведений данные на несколько дат сразу. Типичный пример — работа с курсами валют. Рассмотрим алгоритм решение данной задачи на примере.
Постановка задачи
В базе создан документ «РеализацияТоваровУслуг», в шапке которого есть реквизит «Валюта». Запросом требуется для каждого документа получить актуальный курс валюты из шапки на дату документа. Хранение
курсов
валют
осуществляется
в
периодическом
регистре
сведений
«КурсыВалют
«.
Решением «в лоб» этой задачи мог бы быть запрос в цикле: получение всех документов с их датами и валютой и в выборке обращение к виртуальной таблице среза последних регистра «КурсыВалют». Но т.к. запрос в цикле — это «плохо», попробуем
реализовать
задачу
одним
запросом
.
Решение
Для решения задачи будем использовать факт, что таблицы в запросе можно соединять не только на равенство полей .
ВЫБРАТЬ РеализацияТоваровУслуг.Ссылка, РеализацияТоваровУслуг.Валюта, МАКСИМУМ(КурсыВалют.Период) КАК Период ПОМЕСТИТЬ ВТПериодыУстановкиКурсов ИЗ Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КурсыВалют КАК КурсыВалют ПО РеализацияТоваровУслуг.Валюта = КурсыВалют.Валюта И РеализацияТоваровУслуг.Дата >= КурсыВалют.Период СГРУППИРОВАТЬ ПО РеализацияТоваровУслуг.Ссылка, РеализацияТоваровУслуг.Валюта; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ ВТПериодыУстановкиКурсов.Ссылка, ВТПериодыУстановкиКурсов.Валюта, КурсыВалют.Курс ИЗ ВТПериодыУстановкиКурсов КАК ВТПериодыУстановкиКурсов ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.КурсыВалют КАК КурсыВалют ПО ВТПериодыУстановкиКурсов.Период = КурсыВалют.Период И ВТПериодыУстановкиКурсов.Валюта = КурсыВалют.Валюта
Порядок действий в запросе:
- Получение для каждого документа периода установки курса валюты.
Документы соединяются с ФИЗИЧЕСКОЙ таблицей «КурсыВалют». Здесь следует обратить внимание на условия соединения. Валюты должны быть равны, а дата документа >= периоду регистра сведений.
В результате такого соединения для каждого документа будет получено множество строк, удовлетворяющих условию: все записи курсов по валюте документа, установленные не позже даты документа.
Завершающим этапом будет группировка строк с получением максимального периода курса. В результате для каждого документа будет получен требуемый период установки курса для нужной валюты (максимальная дата установки курса валюты, но не больше даты документа). Результат помещается во временную таблицу ВТПериодыУстановкиКурсов. - Получение курса. Временная таблица ВТПериодыУстановкиКурсов соединяется с ФИЗИЧЕСКОЙ таблицей «КурсыВалют». Соединение происходит по Валюте документа и периоду установки курса, определенному во второй временной таблице.
В тестовой конфигурации у нас есть периодический регистр сведений "ЦеныНоменклатуры" со следующими исходными данными:
На рисунке представлена также структура метаданных регистра. Как мы видим, регистр содержит измерение "Товар" с типом ссылки на справочник "Товары", а также числовой ресурс "Цена" и реквизит "СтараяЦена".
Допустим, в отчете нам нужно получить срез последних записей для товаров и их цен с условием, что старая цена меньше или равно 50.
Два варианта запроса
Сразу скажу, что рассматривать будем правильный и не правильный варианты.Начнем с последнего. Эту ошибку часто делают начинающие программисты. И так, для отчета был написан следующий запрос:
Запрос = Новый Запрос; Запрос. Текст = " ВЫБРАТЬ | | | | |ИЗ | РегистрСведений. ЦеныНоменклатуры. СрезПоследних КАК ЦеныНоменклатурыСрезПоследних |ГДЕ | ЦеныНоменклатурыСрезПоследних. СтараяЦена < = 50 " ;
Обратите внимание на условие в секции "ГДЕ". В этом и заключается главная ошибка! Этот запрос не вернет ни одной записи, и вот почему: при использовании виртуальных таблиц, в нашем случае "СрезПоследних", сначала выполняется выборка данных из базы по условиям, описанным в виртуальной таблице, а затем выполняются действия, описанные в тексте запроса (группировки, условия в секции "ГДЕ", сортировка и т.д.).
Поэтому в нашем примере запрос и не возвращает результат. Сначала он получает срез последних, а уже после устанавливает условие на реквизит "СтараяЦена". Вот так это выглядит на схеме:
Чтобы правильно решить задачу, условие на реквизит "СтараяЦена" нужно перенести в условия виртуальной таблицы. Вот так будет выглядеть правильный текст запроса:
Запрос = Новый Запрос; Запрос. Текст = " ВЫБРАТЬ ЦеныНоменклатурыСрезПоследних. Период, ЦеныНоменклатурыСрезПоследних. Товар, ЦеныНоменклатурыСрезПоследних. Цена, ЦеныНоменклатурыСрезПоследних. СтараяЦена ИЗ РегистрСведений. ЦеныНоменклатуры. СрезПоследних(, СтараяЦена < = 50 ) КАК ЦеныНоменклатурыСрезПоследних "
Теперь запрос получит правильные данные, поскольку срез последних цен будет получать уже с учетом условия по реквизиту "СтараяЦена".
Результаты
Следует понимать, что описанное выше относится ко всем случаям использования виртуальных таблиц в запросах (для регистров накопления, регистров бухгалтерии, задач и т.д.).
Отсюда также вытекает главное правило по использованию виртуальных таблиц: "используя виртуальную таблицу обязательно устанавливайте параметры отбора непосредственно в виртуальной таблице, иначе запрос будет получать излишние данные, на которые уже потом будут устанавливаться отборы".
Не в первый раз стакиваюсь с ситуацией, когда в запросе требуется найти остатки не на фиксированную дату (параметр - один на весь отчет), а на произвольную, (когда дата остатков в каждой строке своя). Нашел статью, которая оказалась весьма полезна для меня, жаль что поздно, так как первый вариант пришлось придумать самому.
Скопипастил с: http://www.kb.mista.ru/article.php?id=92
Получение актуальных данных из регистра сведений на дату, получаемую в самом запросе
Существует несколько способов получить нужные данные.
1.Непосредственно в запросе (через реальную таблицу регистра)
Способ подходит практически для любой ситуации, и поэтому наиболее универсален. Единственный, пожалуй, минус этого способа - если в отчете пользователю не требуется курс, то запрос быдет выбирать избыточные данные.
Вызов СрезПоследних() можно использовать только с передачей в него заранее готового значения даты, на которую требуется получить значения. Поэтому сабж делается через стыковку нескольких запросов -
основной, к нему стыкуется запрос по регистру сведений с условием по дате и поиском записи с маскимальной датой (периодом).
Для общего развития:
Что есть срез последних в платформе?
В зависимости от периодичности регистра (по времени, по позизии регистратора) ВТ разворачивается в следующий запрос:
1. По времени (год, месяц, ... секунда)
2. По позиции регистратора
В данном случае нужно еще раз обернуть выборку
Все это можно увидеть посмотрев технологический журнал с включенным режимом протоколирования запросов
2.Система компоновки данных (передача набора значений одной таблицы в параметр виртуальной таблицы)
Данный способ подходит для отчетов. Из очевидных плюсов - если курс (или другие данные) не нужны для построения отчета, то СКД не будет их получать. Однако быстродействие такого отчета может оказаться и несколько ниже, чем в первом способе.
Для примера сделаем отчет - список заказов покупателей.
Для этого создадим набор данных "Документы" - запрос:
Для того, чтобы потом успешно свзать наборы данных, в запрос необходимо включить поля "Дата" и "ВалютаДокумента". Чтобы они не появлялись в списке доступных полей, если это необходимо, их можно убрать, установив флажки ограничений в таблице "Поля" схемы компоновки. В остальном запрос вряд ли требует комментариев.
Для того, чтобы получить информацию о курсах валют, добавим второй набор данных-запрос, "Курсы валют":
В этом запросе имеются 2 параметра: "Дата" и "Валюта". Эти параметры будут установлены СКД при соединении наборов. Кроме того, параметр "Дата" указан в выбранных полях - это нужно для соединения таблиц.
Для ненужный полей "Дата" и "Валюта" также устанавливаем флажки ограничений, чтобы они не появлялись в доступных полях. 
Перейдем к соединению наборов. На странице "Связи наборов данных" добавим 2 связи:
1. Источник связи - набор "Документы", приемник - набор "Курсы валют". Выражение источник - "Дата", выражение приемник - "Дата", Параметр - "Дата"
2. Источник связи - набор "Документы", приемник - набор "Курсы валют". Выражение источник - "ВалютаДокумента", выражение приемник - "Валюта", Параметр - "Валюта"
Главное здесь - параметры связи. При соединении наборов данных, если указан параметр, СКД передает в подчиненный набор (в нашем случае - запрос "Курсы валют") параметры, указанные в соединении. Значениями параметров будут значения соответствующих полей набора-источника.
Перейдем к вычисляемым полям. Добавим вычисляемое поле "СуммаВВалютеУпрУчета". Выражение поля - "СуммаДокумента * Курс / Кратность".
Также укажем поля "СуммаДокумента" и "СуммаВВалютеУпрУчета" как ресурсы 
Настроим отчет.
Добавим одну группировку "Детальные записи", в выбранных полях укажем "ЗаказПолкупателя", "Курс" и "Кратность". Добавим ресурсы "СуммаДокумента" и "СуммаВВалютеУпрУчета" 
Можно формировать отчет 
Предположим, что нам поступила задача, по которой заказчик хочет получить отчет по введенным в базу документам "Реализация товаров и услуг", причем на каждый документ необходимо получать цену из регистра сведений "Цены номенклатуры" на дату документа. Отчет пишется для конфигурации "Управление производственным предприятием" версии 1.3.
К записи из таблицы документа присоединяется запись из виртуальной таблицы "ЦеныНоменклатуры.СрезПоследний" по соответствующим условиям периода, типа цены и номенклатуры. Тип цены получаем из одноименного реквизита документа.
Задача имеет несколько способов решения. Рассмотрим два из них: отчет на СКД с помощью двух наборов данных и получение всех необходимых данных в одном запросе. Теперь по порядку.
Использование СКД
Для реализации подобного отчета на СКД создадим в схеме компоновки данных два набора данных. Первый будет получать список документов, второй цены на даты документов по выбранной номенклатуре и типу цен. Связь между двумя наборами данных осуществляется по номенклатуре, периоду (дата документа) и типу цены. Из первого набора необходимо передвать параметры "Номенклатура", "ТипЦены" и "Период" во второй набор.
Первый набор данных содержит следующий запрос:
Запрос выбирает из таблицы документа и табличной части "Товары" поля: "Ссылка", "Дата", "ТипЦен", "Номенклатура". Теперь рассмотрим второй набор данных:
Во втором наборе данных следует обратить внимание на параметры, передаваемые в запрос. В списке выбираемых полей содержатся параметры "Период, "Номенклатура" и "ТипЦены". Эти же параметры используются в параметрах виртуальной таблицы "ЦеныНоменклатуры.СрезПоследних". Выводить данные параметры в список выбираемых полей необходимо, чтобы из первого набора можно было передавать значения этих параметров во второй набор. Осуществляется это с помощью настроек соединения между наборами данных в вкладке конструктора схемы компоновки данных "Связи наборов данных". Перейдем к ее рассмотрению:
Настройка связей между источниками данных осуществляется по поляем выборки в запросе. Во втором наборе данных в поля выборки мы добавили параметры запроса. При установке связей между наборами, если выбрать для связи поле в колонке "Параметр", значение из колонки "Выражение источник" будет передано в "Выражение приемник" и заполнит выбранный параметр соответствующим значением.
Настроив структуру отчета и сформировав его, мы получим следующий результат:
Отчет работает. Перейдем к рассмотрению варианты получения цены на дату документа в одном запросе.
В одном запросе
Создадим новый отчет с аналогичными выходными полями и настройками отчета. Единственное отличие будет заключаться в источниках данных. На этот раз в отчете будет содержаться единственный набор данных, в котором одним запросом выбираются все товары документов и сопоставленные для них цены. Текст запроса выглядит следующим образом:
"ВЫБРАТЬ | ДокументНоменклатураПериод.Документ, | ДокументНоменклатураПериод.Документ.Дата КАК Дата, | ДокументНоменклатураПериод.Документ.ТипЦен КАК ТипЦен, | ДокументНоменклатураПериод.Номенклатура, | ЦеныНоменклатуры.Цена |ИЗ | (ВЫБРАТЬ | РеализацияТоваровУслугТовары.Ссылка КАК Документ, | РеализацияТоваровУслугТовары.Номенклатура КАК Номенклатура, | МАКСИМУМ(ЦеныНоменклатуры.Период) КАК Период | ИЗ | Документ.РеализацияТоваровУслуг.Товары КАК РеализацияТоваровУслугТовары | ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры | ПО РеализацияТоваровУслугТовары.Номенклатура = ЦеныНоменклатуры.Номенклатура | И РеализацияТоваровУслугТовары.Ссылка.Дата >= ЦеныНоменклатуры.Период | И РеализацияТоваровУслугТовары.Ссылка.ТипЦен = ЦеныНоменклатуры.ТипЦен | | СГРУППИРОВАТЬ ПО | РеализацияТоваровУслугТовары.Ссылка, | РеализацияТоваровУслугТовары.Номенклатура) КАК ДокументНоменклатураПериод | ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры | ПО ДокументНоменклатураПериод.Номенклатура = ЦеныНоменклатуры.Номенклатура | И ДокументНоменклатураПериод.Документ.ТипЦен = ЦеныНоменклатуры.ТипЦен | И ДокументНоменклатураПериод.Период = ЦеныНоменклатуры.Период"
Принцип работы запроса заключается в получении к документe ближайшей даты изменения цены для каждой использованной номенклатуры в табличной части, а затем присоединение к полученному результату запроса данных из физической таблицы "Цены номенклатуры" по периоду, номенклатуре и типу цен. На первый взгляд, запрос может показаться сложным, однако на самом деле его синтаксис достаточно простой.
Результат выполнения аналогичен результату предыдущего отчета (см. скриншот выше).
Делайте выводы
Какой вариант выполнения задачи лучше? Тут все зависит от конкретных условий. В некоторых ситуациях отчет из двух наборов данных будет работать быстрее, чем получение всех данных для отчета в одном запросе. В рамках статьи мы не сможем коснуться вопросов производительности для рассмотренных способов решения задачи.
Также стоит помнить, что создание отчетов на основе таблиц документов не рекомендуется методикой разработки от фирмы 1С, так как считается, что информация в документах может быть не достоверной. Все отчеты необходимо строить на основании регистров.