Перейти к содержимому

Какой запрос строится не sql когда мы обращаемся к таблице остатков

  • автор:

Эффективное обращение к виртуальной таблице «Остатки»

Эффективность обращения к виртуальным таблицам во многом зависит от того, как построено обращение к этой таблице. Стандарт Обращения к виртуальным таблицам описывает общие требования и рекомендации по работе с виртуальными таблицами. В этом стандарте изложены дополнительные рекомендации по повышению эффективности обращения к виртуальной таблице Остатки регистров накопления и бухгалтерии.

При обращении к любой виртуальной таблице платформа 1С:Предприятие генерирует запрос к СУБД, содержащий вложенный запрос. Самым эффективным вложенным запросом для чтения остатков будет чтение хранимой таблицы текущих остатков без применения группировки по измерениям. Платформа 1С:Предприятие сгенерирует такой запрос, если будут соблюдены все перечисленные ниже условия:

Пример.

Регистр накопления ОстаткиТовара содержит два измерения: Склад и Номенклатура , а также ресурс Количество. Необходимо запросом получить список всей номенклатуры, с указанием количества товаров на конкретном складе.

АйТиБложик

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

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

Данную задачу я решил разделить на несколько подзадач, ведь слона легче есть кусками, чем сразу целиком. Получились такие подзадачи:

  1. Получить таблицу с датами на каждый день в пределах задаваемого периода
  2. Получить таблицу остатков и оборотов по каждому товару за период с периодичностью ДЕНЬ
  3. Как-то соединить полученные таблицы в первых двух подзадачах для получения желаемого результата

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

Получившуюся таблицу значений помещаем во временную таблицу запроса с последующим выводом содержимого временной таблицы через основной запрос.

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

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

Немножко пояснений. Допустим, пользователь указал период 01.01.2020 — 31.12.2020 в форме обработки, то есть осуществляется ввод дат без времени. В системе 1С такие даты будут храниться как 01.01.2020 00:00 и 31.12.2020 00:00, время присутствует и оно нулевое, соответственно остатки и обороты мы получим за период 01.01.2020 00:00 — 31.12.2020 00:00, что неправильно, т.к. данные за 31 декабря не попадут в результат запроса. Для этого к концу периода применяется функция КонецДня(), что бы получить остатки и обороты за период 01.01.2020 00:00 — 31.12.2020 23:59, или можно воспользоваться встроенной функцией КОНЕЦПЕРИОДА() языка запросов. Для параметра Периодичность виртуальной таблицы указана периодичность День, что и требуется в задаче, для параметра Метод дополнения периодов указано значение ДвиженияИГраницыПериода, что бы в результате запроса всегда присутствовали периоды на начало и конец заданного периода. Результат запроса представлен на рисунке 1.

Рисунок 1. Остатки и обороты по товару «Стол дуб.» за 2015 год

А вот и самое интересное, решение третьей подзадачи. Соединяем таблицы первой и второй подзадачи таким образом, что бы в результате каждому дню соответствовал необходимый период остатков и оборотов, результат помещаем во временную таблицу (см. рисунок 2).

Рисунок 2. Промежуточная таблица с ключом соединения по товару и периоду

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

Конечный текст пакета запросов:

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

Виртуальные таблицы регистра бухгалтерии

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

!ВАЖНО! Обратите внимание на использование параметра виртуальных таблиц "Субконто". Подробнее о его использовании написано ниже.

Таблица остатков

как следует из названия эта таблица предназначена для получения остатков по ресурсам регистра бухгалтерии в разрезе счетов, измерений, видов субконто и субконто.

Параметры виртуальной таблицы:

  • Период – имеет тип значения Дата, МоментВремени или Граница. На этот момент будут рассчитаны остатки виртуальной таблицы. Если параметр не задан, будут получены актуальные остатки, включающие движения последнего проведенного документа. Физическая организация таблиц позволяет максимально быстро получить остатки за месяц (на начало следующего месяца) и актуальные остатки.

!ВАЖНО! Остатки рассчитываются как входящий остаток на заданный период. Если в качестве периода будет задано КонецДня(&Дата), то при расчете остатков не попадут проводки выполненные в 23:59:59 этой даты. Чтобы учесть эти проводки надо в качестве периода брать начало следующего дня, либо используя границу: Новый Граница(КонецДня(&Дата), ВидГраницы.Включая);

  • УсловиеСчета – содержит конструкцию языка запросов. Позволяет установить фильтр по счету или счетам. Как правило, содержит следующие условия: Счет = (В ИЕРАРХИИ, В) &Счет. Так же в условие счета может быть указано условие по реквизиту счета: Счет.Валютный = ИСТИНА
  • Субконто – имеет тип ПланВидовХарактеристикСсылка.<имя> или содержит массив значений типа ПланВидовХарактеристикСсылка.<имя>. В этот параметр таблицы можно передать ссылку на вид субконто для получения отбора по этому виду или массив видов для установки отбора и упорядочивания видов субконто в результате запроса.
  • Условие – содержит конструкцию языка запросов. Позволяет устанавливать отбор данных виртуальной таблицей по значениям субконто и измерений регистра бухгалтерии.

Пример запроса получения остатка товара по счету товара

ВЫБРАТЬ
Остатки.КоличествоОстаток
ИЗ

РегистрБухгалтерии.ОсновнойРегистрБухгалтерии.Остатки(&Момент, Счет = &СчетТоваров, , Организация = &Организация И Субконто1 = &Номенклатура) КАК Остатки

Поля таблицы:

  • Счет – это поле имеет тип ПланСчетовСсылка.<имя>. Поле позволяет получить остатки, сгруппированные по счетам.
  • Субконто<N> – это поле имеет тип Характеристика.<имя>. Количество полей Субконто зависит от максимального количества субконто на счете плана счетов. Поле позволяет получить остатки, сгруппированные по значениям субконто.
  • <Измерение> – тип этого поля задается при создании измерения регистра. Количество полей зависит от количества измерений, объявленных в регистре бухгалтерии. На поле не влияет свойство измерения Балансовый. Поле позволяет получить остатки, сгруппированные по измерениям регистра.
  • <Ресурс>Остаток – это поле имеет тип Число. Абсолютный остаток (как он хранится в физических таблицах информационной базы) без учета вида счета: дебетовый остаток показывается положительным числом, кредитовый – отрицательным.
  • <Ресурс>ОстатокДт – это поле имеет тип Число. Дебетовый остаток с учетом вида счета. Если счет пассивный, значение этого поля всегда равно нулю. Если счет активный, значение поля равно значению поля Остаток. Если счет активно-пассивный, значение поля равно значению поля Остаток, если Остаток больше или равен нулю. Если Остаток меньше нуля, значит – ноль.
  • <Ресурс>ОстатокКт – это поле имеет тип Число. Кредитовый остаток с учетом вида счета. Если счет активный, значение этого поля всегда равно нулю. Если счет пассивный, равно «— Остаток». Если счет активно-пассивный, значение поля равно нулю, если значение поля Остаток больше или равно нулю. Если значение поля Остаток меньше нуля, значение этого поля равно «- Остаток».
  • <Ресурс>РазвернутыйОстатокДт – это поле имеет тип Число. Развернутый дебетовый остаток. Имеет смысл только при использовании в запросе итогов. Для детальной записи значение этого поля равно значению поля ОстатокДт. Для итоговой записи равно сумме дебетовых остатков всех детальных записей.
  • <Ресурс>РазвернутыйОстатокКт – это поле имеет тип Число. Развернутый кредитовый остаток. Имеет смысл только при использовании в запросе итогов. Для детальной записи значение этого поля равно значению поля ОстатокКт. Для итоговой записи равно сумме кредитовых остатков всех детальных записей.

Таблица оборотов

используется для получения оборотов по счету в разрезе измерений и субконто, а также оборотов между корреспондирующими счетами (для регистра с поддержкой корреспонденции).

Поля таблицы.

  • Счет – это поле имеет тип ПланСчетовСсылка.<имя>. Поле позволяет получить обороты выбранного счета.
  • КорСчет – это поле имеет тип ПланСчетовСсылка.<имя>. Поле позволяет получить обороты выбранного счета, указанного в параметре Счет с выбранным корреспондирующим счетом.
  • Субконто<N> – это поле имеет тип Характеристика.<имя>. Количество полей Субконто зависит от максимального количества субконто на счете плана счетов. Поле позволяет получить обороты, сгруппированные по значениям субконто.
  • КорСубконто<N> – это поле имеет тип Характеристика.<имя>. Количество полей КорСубконто зависит от максимального количества субконто на счете плана счетов. Поле позволяет получить обороты, сгруппированные по значениям субконто корреспондирующего счета.
  • <Измерение> – тип поля задается при создании измерения регистра. Количество полей зависит от количества измерений, объявленных в регистре бухгалтерии. Поле существует как для балансовых, так и для небалансовых измерений.
  • <Измерение>Кор – тип поля задается при создании измерения регистра. Количество полей зависит от количества измерений, объявленных в регистре бухгалтерии. Поле создается только для небалансовых измерений.

Поля создаются в том случае, если в параметрах виртуальной таблицы задана периодичность и она отличается от значения Период:

  • Период – тип Дата,
  • Регистратор – тип ДокументСсылка.<имя>,
  • НомерСтроки – тип Число.
  • <Ресурс>Оборот – это поле имеет тип Число. Разница оборотов выбранного счета (дебетовый оборот минус кредитовый оборот).
  • <Ресурс>ОборотДт – это поле имеет тип Число. Дебетовый оборот счета.
  • <Ресурс>ОборотКт – это поле имеет тип Число. Кредитовый оборот счета.

Для оборотов с корреспонденцией

  • <Ресурс>КорОборот – это поле имеет тип Число. Содержит разницу оборотов (кор. оборот дебета минус кор. оборот кредита) для небалансового ресурса корреспондирующего счета.
  • <Ресурс>КорОборотДт – это поле имеет тип Число. Дебетовый оборот для небалансового ресурса корреспондирующего счета.
  • <Ресурс>КорОборотКт – это поле имеет тип Число. Кредитовый оборот для небалансового ресурса корреспондирующего счета.

Параметры таблицы.

  • НачалоПериода, КонецПериода – имеет тип Дата, МоментВремени или Граница. Период времени, за который будут получены обороты. При использовании в качестве значений НачалоДня() и КонецДня(), в обороты попадут проводки с временем 00:00:00 и 23:59:59 соответственно.
  • Периодичность – содержит конструкцию языка запросов. Позволяет задать дополнительную группировку данных по стандартным периодам. Возможные значения: Период, Год, Полугодие, Квартал, Месяц, Декада, Неделя, День, Час, Минута, Секунда (в качестве группировки выводится начало указанного периода), Регистратор, Запись (группировка по регистратору и номеру строки в наборе записей). Если периодичность не задана или задана как Период, дополнительная группировка не выполняется.
  • УсловиеСчета – содержит конструкцию языка запросов. Позволяет установить фильтр по счету или счетам. Как правило, содержит следующие условия: Счет = (В ИЕРАРХИИ, В) &Счет.
  • Субконто – имеет тип ПланВидовХарактеристикСсылка.<имя> или содержит массив значений типа ПланВидовХарактеристикСсылка.<имя>. В этот параметр таблицы можно передать ссылку на вид субконто для получения отбора по этому виду или массив видов для установки отбора и упорядочивания видов субконто в результате запроса.
  • Условие – содержит конструкцию языка запросов. Позволяет устанавливать отбор данных виртуальной таблицей по значениям субконто и измерений регистра бухгалтерии.

Для регистров с поддержкой корреспонденции:

  • УсловиеКорСчета – содержит конструкцию языка запросов. Позволяет установить фильтр по корреспондирующему счету или счетам. Как правило, содержит следующие условия: КорСчет = (В ИЕРАРХИИ, В) &КорСчет.
  • КорСубконто – имеет тип ПланВидовХарактеристикСсылка.<имя> или содержит массив значений типа ПланВидовХарактеристикСсылка.<имя>. В этот параметр таблицы можно передать ссылку на вид субконто для получения отбора по этому виду или массив видов для установки отбора и упорядочивания видов субконто в результате запроса.

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

ВЫБРАТЬ
Обороты.Субконто1,
Обороты.КоличествоОборотДт,
Обороты.КорСубконто1,
Обороты.КоличествоКорОборотДт
ИЗ
РегистрБухгалтерии.ОсновнойРегистрБухгалтерии.Обороты( , , , Счет = &СчетТоваров, , , КорСчет = &СчетТоваров, ) КАК Обороты

Таблица остатков и оборотов

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

Поля таблицы:

  • Счет – это поле имеет тип ПланыСчетовСсылка.<имя>. Поле содержит счет, для которого получены остатки и обороты.
  • Субконто<N> – это поле имеет тип Характеристика.<имя>, тип этого поля определяется типом субконто. Количество таких полей зависит от максимального количества субконто на счете плана счетов. Поле позволяет получить остатки и обороты, сгруппированные по значениям субконто.
  • <Измерение> – тип этого поля определяется типом измерения регистра накопления. Количество таких полей зависит от количества измерений, объявленных в регистре бухгалтерии. Поле существует как для балансовых, так и для небалансовых измерений.
  • Период, Регистратор, НомерСтроки – эти поля имеют тип соответственно Дата, ДокументСсылка.<имя> и Число. Поля создаются в том случае, если в параметрах виртуальной таблицы задана периодичность и она отличается от Период.
  • <Ресурс>Оборот, <Ресурс>ОборотДт, <Ресурс>ОборотКт – эти поля имеют тип Число. Поля аналогичны полям таблицы оборотов.
  • <Ресурс>НачальныйОстаток, <Ресурс>НачальныйОстатокДт, Ресурс>НачальныйОстатокКт, <Ресурс>НачальныйРазвернутыйОстатокДт, <Ресурс>НачальныйРазвернутыйОстатокКт – эти поля имеют тип Число. Поля аналогичны полям таблицы остатков, рассчитываются на дату начала интервала, указанного в параметре таблицы остатков и оборотов НачалоПериода.
  • <Ресурс>КонечныйОстаток, <Ресурс>КонечныйОстатокДт, <Ресурс>КонечныйОстатокКт, <Ресурс>КонечныйРазвернутыйОстатокДт, <Ресурс>КонечныйРазвернутыйОстатокКт – эти поля имеют тип Число. Поля аналогичны полям таблицы остатков, рассчитываются на дату окончания интервала, указанного в параметре таблицы остатков и оборотов КонецПериода.

Развернутые остатки отличаются от обычных тем, что если на счете активо-пассивном) ведется какая-то аналитика, то отдельно будет отображаться остаток по дебету и отдельно остаток по кредиту. Например обычный остаток по счету = 5, а развернутые остатки по счету равны Сальдо дебет = 2, Сальдо кредит = 7.

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

Счет Контрагент СвернутыйДт СвернутыйКт РазвернутыйДт РазвернутыйКт
Контрагенты Иванов 2 2
Контрагенты Петров 7 7
Счет Контрагент СвернутыйДт СвернутыйКт РазвернутыйДт РазвернутыйКт
Контрагенты 5 2 7
Иванов 2 2
Петров 7 7

Параметры таблицы.

Такие же как для таблицы Обороты плюс

  • МетодДополнения – этот параметр содержит конструкцию языка запросов. Возможны два значения: Движения (в отчет попадаю только периоды, в которых было движение) или ДвиженияИГраницыПериода (Начало периода, периоды с движением и конец периода). Значение по умолчанию – ДвиженияИГраницыПериода. Управляет включением в отчет периодов, не имеющих оборотов, но имеющих остатки.

Важно! Новички очень часто не обращают внимание на параметр МетодДополнения и потом получают сложности при построении отчетов. По умолчанию значение этого параметра равно ДвиженияИГраницыПериода, и если вы используете параметр Периодичность, отличный от значения Период, то этот метод будет добавлять вам в отчет дополнительные строки со значениями на начало и конец периода.

Пример запроса для получения оборотно-сальдовой ведомости:

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

УПОРЯДОЧИТЬ ПО
ОстаткиИОбороты.Счет.Код
ИТОГИ
СУММА(НачОстДт),
СУММА(НачОстКт),
СУММА(ОборотДт),
СУММА(ОборотКт),
СУММА(КонОстДт),
СУММА(КонОстКт)
ПО
Счет ИЕРАРХИЯ

Таблица ДвиженияССубконто

Это таблица, получаемая соединением двух физических таблиц: основной таблицы и таблицы значений с субконто. Отличительной особенностью таблицы является то, что в нее попадают все записи, в том числе и не активные. Используется для отборов проводок.

Параметры таблицы

  • НачалоПериода, КонецПериода – эти параметры имеют тип Дата, МоментВремени или Граница. Период времени, за который будут отобраны проводки.
  • Условие – этот параметр содержит конструкцию языка запросов. Конструкция позволяет установить условие на любое поле виртуальной таблицы.
  • Порядок – этот параметр содержит конструкцию языка запросов. Конструкция позволяет задать упорядочивание проводок.
  • Первые – этот параметр имеет тип Число. С его помощью задается ограничение максимального количества записей.

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

Таблица оборотов Дт Кт

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

Поля виртуальной таблицы

  • Период, Регистратор, НомерСтроки — Тип Дата ДокументСсылка.<имя>, Число. Поля существуют в таблице, только если значение параметра Периодичность задано и отличается от Период
  • СчетДт — тип ПланыСчетовСсылка.<имя>. Дебетуемый счет
  • СчетКт — тип ПланыСчетовСсылка.<имя>. Кредитуемый счет
  • <Измерение> — Определяется типом измерения регистра бухгалтерии Балансовое измерение регистра бухгалтерии
  • <Измерение>Дт, <Измерение>Кт — Небалансовое измерение регистра бухгалтерии создает два поля в таблице
  • СубконтоДт<N>, СубконтоКт<N> — Определяется типом субконто Количество полей зависит от настройки плана счетов
  • <Ресурс>Оборот — Число Оборот балансового ресурса регистра бухгалтерии
  • <Ресурс>ОборотДт, <Ресурс>ОборотКт — Число Обороты небалансового ресурса регистра бухгалтерии

Запрос для получения оборотов между счетами:

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

Эти же данные можно получить используя таблицу Обороты

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

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

Использую таблицу Обороты достаточно одного обращения к таблице:

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

Параметр Субконто

Назначение параметра Субконто:
1) Отбор итогов по виду субконто.

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

ВЫБРАТЬ
Остатки.Субконто1 КАК Товар,
Остатки.Счет КАК Счет,
Остатки.СуммаОстаток КАК Остаток
ИЗ
РегистрБухгалтерии.ОсновнойРегистрБухгалтерии.Остатки(&Дата, , &ВидСубконто, ) КАК Остатки
ИТОГИ
СУММА(Остаток)
ПО
ОБЩИЕ,
Товар

В параметрах запроса &ВидСубконто = ПланыВидовХарактеристик.ВидыСубконто.Номенклатура

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

Если надо отобрать по конкретному значению Субконто, то надо добавить на него условие в параметр "Условие"

2) упорядочивание субконто в результате запроса (в особенности, если запрос выполняется по нескольким счетам).

Допустим есть 2 счета. У одного счета Номенклатура — Субконто1, а у другого счета Субконто 2.

Если в запросе указать просто "Остатки.Субконто1 КАК Товар" без заданного параметра Субконто, то для второго счета будет выведено не Номенклатура, а то, что указано у него как Субконто 1.

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

Например выберем остатки товаров в разрезе складов по счетам "Товары" и "Материалы".

Код Счет Субконто1 Субконто2
1.3 Товары Номенклатура Склады
1.4 Материалы Склады Номенклатура

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

Запрос.УстановитьПараметр("Дата", Дата);
Запрос.УстановитьПараметр("СчетТоваров", ПланыСчетов.ОсновнойПланСчетов.Товары);
Запрос.УстановитьПараметр("СчетМатериалов", ПланыСчетов.ОсновнойПланСчетов.Материалы);
мВидыСубконто = Новый Массив;
мВидыСубконто.Добавить(ПланыВидовХарактеристик.ВидыСубконто.Номенклатура);
мВидыСубконто.Добавить(ПланыВидовХарактеристик.ВидыСубконто.Склады);
Запрос.УстановитьПараметр("ВидыСубконто", мВидыСубконто);

Таким образом, нам удалось «расставить субконто по местам»: первым субконто в нашем запросе является Номенклатура, вторым – Склады. Причем если мы хотим изменить последовательность группировок в запросе (сначала группировать по складам, потом внутри склада – по номенклатурным позициям), то достаточно изменить порядок следования элементов массива, который передается в параметр виртуальной таблицы Субконто.

Виртуальные таблицы остатков и оборотов

Как следует из названия одни предназначены для получения остатков на какую-либо дату, а вторые для получения оборотов за выбранный период. В зависимости от вида регистра накопления платформа 1С:Предприятия формирует разный набор виртуальных таблиц. В этой статье и рассмотрим работу с виртуальными таблицами регистров накопления. Для этого создадим регистр накопления остатков — ТоварыОстатки и регистр накопления оборотов — ТоварыОбороты.

Теперь рассмотрим какие виртуальные таблицы предоставляет платформа для каждого из этих регистров.

Регистр накопления оборотов

Для наглядности откроем конструктор запросов и посмотрим какие таблицы доступны для регистра ТоварыОбороты. Это таблица самого регистра — ТоварыОбороты, которая существует физически в базе данных, и одна виртуальная таблица — ТоварыОбороты.Обороты

Со стандартной таблицей все понятно. Рассмотрим подробнее виртуальную.

Виртуальная таблица Обороты

Эта таблица позволяет получать оброты ресурсов в разрезе измерений. В нашем случае у нас два измерения: Склад и Товар. И один ресурс — Количество
Пусть в нашем регистре есть следующие записи

Вернемся в конструктор запросов и для начала просто выберем из таблицы ТоварыОбороты.Обороты все поля

Соответственно запрос будет выглядеть следующим образом:

Результат выполнения запроса выглядить вот так:

Склад Товар КоличествоОборот
Центральный Ручка 3
Офис Ручка 7
Центральный Карандаш 21
Офис Карандаш 4

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

и в результате у нас останется только две строки

Товар КоличествоОборот
Ручка 10
Карандаш 25

Но как правило за все время существования регистра обороты получать не требуется. В основном они нужны за какой-то конкретный период: месяц, квартал, год и т.д. Плюс еще обычно нужны отборы по измерениям (Товар, Склад). Это достигается использованием параметров виртуальной таблицы. Заполнять параметры удобно из конструктора. По кнопке Параметры виртуальной таблицы открывается диалоговое окно в котором можно прописать все что нам нужно:

После этого наш исходный запрос примет следующий вид

Как видим отличие в том, что в скобках после имени виртуальной таблицы появились параметры, которые необходимо заполнить перед выполнением запроса.
У тех кто только начинает работать с виртуальными таблицам зачастую возникает соблазн вместо использования параметров установить отбор привычным способом:

Делать так категорически нельзя. Это отрицательно скажется на производительности и на вашей оценке при сдаче экзамена 1С:Специалист.

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

После этого наблюдаем, что в полях таблицы появилось поле Период.

Добавив и его в выбранные поля, получим вот такой текст запроса:

Период Склад Товар КоличествоОборот
01.06.2017 0:00:00 Центральный Ручка 3
01.06.2017 0:00:00 Центральный Карандаш -5
01.06.2017 0:00:00 Офис Карандаш 4
01.05.2017 0:00:00 Офис Ручка 7
01.05.2017 0:00:00 Центральный Карандаш 26

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

Регистр накопления остатков

Так же как и с оборотным регистром посмотрим в конструкторе запросов какие же виртуальные таблицы доступны для регистра накопления остатков

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

Виртуальная таблица Обороты

Несмотря на то, что вид регистра — Остатки, мы тем не менее можем получать по нему и обороты. Плюс у нас здесь есть два дополнительных ресурса: Приход и Расход

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

Виртуальная таблица Остатки

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

Рассмотрим небольшой пример. Имеем следующие записи регистра:

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

А после его выполнения получим вот такой результат

Склад Товар КоличествоОстаток
Центральный Ручка 3
Центральный Карандаш 21
Офис Ручка 7
Офис Карандаш 4

Виртуальная таблица ОстаткиИОбороты

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *