В этой статье мы рассмотрим примеры получения запросом данных по остаткам или оборотам за каждый день в интервале, даже если за этот день не было движений. Основной принцип таких запросов – нам нужна “опорная” таблица с датами за каждый день, и уже с ее помощью мы получаем остатки либо обороты.
Получение запросом дат на каждый день в интервале
Достаточно часто можно встретить решения, опирающиеся на какие-то данные, существующие в базе, например на производственный календарь или регистр курсов валют. Действительно, в большинстве случаев из этих регистров можно достать даты за период. Но это не универсальный подход, ведь данные могут и отсутствовать, а кроме того, есть различные конфигурации – самописные, отраслевые и т.п., в которых таких регистров может и вовсе не быть. Поэтому ниже рассмотрен пример получения дат чисто средствами языка запросов. Для наглядности добавлено вспомогательное поле Счетчик.
ВЫБРАТЬ 0 КАК val ПОМЕСТИТЬ tt1 ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9 ; //////////////////////////////////////////////////////////////////////////////// ВЫБРАТЬ ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, tab1.val + 10 * tab2.val + 100 * tab3.val + 1000 * tab4.val) КАК Период, tab1.val + 10 * tab2.val + 100 * tab3.val + 1000 * tab4.val КАК Счетчик ПОМЕСТИТЬ Дни ИЗ tt1 КАК tab1, tt1 КАК tab2, tt1 КАК tab3, tt1 КАК tab4 ГДЕ ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, tab1.val + 10 * tab2.val + 100 * tab3.val + 1000 * tab4.val) <= &КонецПериода ИНДЕКСИРОВАТЬ ПО Период
В первом запросе пакета мы помещаем во временную таблицу числа от 0 до 9. Во втором запросе мы получаем счетчик путем перемножения чисел на нужную кратность и добавления получившегося числа дней к параметру НачалоПериода. Т.к. получившийся временной ряд может выходить за пределы нужного нам интервала, отсекаем его по условию на КонецПериода. Затем помещаем все во временную таблицу и индексируем по полю Период, с тем чтобы в дальнейшем повысить эффективность соединений / отборов по этому полю.
Легко заметить, что полученный временной ряд имеет ограничение в 10000 дней. Если нужен меньший интервал, например в пределах года, то достаточно будет декартова произведения трех таблиц. Если нужен больший – понадобится добавить столько таблиц, сколько еще нужно разрядов, и скорректировать формулу.
Получение запросом остатков на каждый день
Без использования СКД, исключительно средствами языка запросов, получить остатки на каждый день из виртуальной таблицы Остатков простым способом нельзя. А потому используется следующий подход – берется таблица остатков и оборотов, и остатки для всех строк, кроме первой, считаются как сумма оборотов. При этом связей между таблицей Дни и таблицей остатков и оборотов мы не делаем, таким образом снова используется декартово произведение двух таблиц.
ВЫБРАТЬ Дни.Период КАК Период, ОстаткиТоваров.Товар КАК Товар, СУММА(ВЫБОР КОГДА ОстаткиТоваров.Период = &НачалоПериода ТОГДА ОстаткиТоваров.КоличествоКонечныйОстаток ИНАЧЕ ВЫБОР КОГДА ОстаткиТоваров.Период <= Дни.Период ТОГДА ОстаткиТоваров.КоличествоОборот ИНАЧЕ 0 КОНЕЦ КОНЕЦ) КАК Остаток ИЗ Дни КАК Дни, РегистрНакопления.ОстаткиТоваров.ОстаткиИОбороты(, , День, , ) КАК ОстаткиТоваров СГРУППИРОВАТЬ ПО Дни.Период, ОстаткиТоваров.Товар УПОРЯДОЧИТЬ ПО Товар,Период
Проверим, как это будет выглядеть. Пусть по товару Печенье у нас есть такие движения по регистру Остатки Товаров:
Сформируем в консоли запрос. Найдем строки с товаром Печенье, и убедимся, что остатки выводятся в том числе и на те даты, когда в физической таблице записей нет.
Получение запросом оборотов на каждый день
Обороты на каждый день получаются по похожему же принципу. Мы снова берем декартово произведение двух таблиц, но сумму оборота будем учитывать только в случае, когда период из таблицы Дни строго равен периоду из таблицы оборотов. В остальных случаях сумма оборотов будет равна нулю. Пример решения данной задачи средствами СКД рассмотрен в этой статье.
ВЫБРАТЬ Дни.Период КАК Период, ПродажиОбороты.Товар КАК Товар, Сумма(Выбор когда Дни.Период = ПродажиОбороты.Период Тогда ПродажиОбороты.КоличествоОборот Иначе 0 Конец) КАК КоличествоОборот ИЗ Дни КАК Дни, РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК ПродажиОбороты СГРУППИРОВАТЬ ПО Дни.Период, ПродажиОбороты.Товар УПОРЯДОЧИТЬ ПО Товар, Период
Пусть у нас будет вот такая таблица по оборотному регистру Продажи
Сформируем запрос за период с 01.11.23 по 05.11.23 и убедимся, что в итоговый результат попадают записи в том числе и за те даты, когда продаж не было:
Итак, мы рассмотрели примеры формирования остатков на каждый день при помощи запроса, а также оборотов на каждый день. Ключевой момент – нам нужна таблица со всеми датами в заданном интервале. С этой таблицей мы строим декартово произведение. Важно! Левое соединение нам тут не подойдет, т.к. для отсутствующих записей значения измерений будут NULL (в нашем примере у измерения Товар), и мы в дальнейшем не сможем наложить условия, сортировку, соединения и т.п. по этим строкам.