В этой статье мы рассмотрим примеры получения запросом данных по остаткам или оборотам за каждый день в интервале, даже если за этот день не было движений. Основной принцип таких запросов – нам нужна “опорная” таблица с датами за каждый день, и уже с ее помощью мы получаем остатки либо обороты.
Получение запросом дат на каждый день в интервале
Достаточно часто можно встретить решения, опирающиеся на какие-то данные, существующие в базе, например на производственный календарь или регистр курсов валют. Действительно, в большинстве случаев из этих регистров можно достать даты за период. Но это не универсальный подход, ведь данные могут и отсутствовать, а кроме того, есть различные конфигурации – самописные, отраслевые и т.п., в которых таких регистров может и вовсе не быть. Поэтому ниже рассмотрен пример получения дат чисто средствами языка запросов. Для наглядности добавлено вспомогательное поле Счетчик.
ВЫБРАТЬ 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 КОНЕЦ КОНЕЦ) КАК Остаток ИЗ Дни КАК Дни, РегистрНакопления.ОстаткиТоваров.ОстаткиИОбороты(, , День, , ) КАК ОстаткиТоваров СГРУППИРОВАТЬ ПО Дни.Период, ОстаткиТоваров.Товар УПОРЯДОЧИТЬ ПО Товар,Период
Проверим, как это будет выглядеть. Пусть по товару Печенье у нас есть такие движения по регистру Остатки Товаров:
![1С Остатки на каждый день запросом](https://1c.alexcode.ru/wp-content/uploads/2023/11/2023-11-19-1024x302.png)
Сформируем в консоли запрос. Найдем строки с товаром Печенье, и убедимся, что остатки выводятся в том числе и на те даты, когда в физической таблице записей нет.
![1С Остатки на каждый день запросом 2](https://1c.alexcode.ru/wp-content/uploads/2023/11/2023-11-19-1-1024x554.png)
Получение запросом оборотов на каждый день
Обороты на каждый день получаются по похожему же принципу. Мы снова берем декартово произведение двух таблиц, но сумму оборота будем учитывать только в случае, когда период из таблицы Дни строго равен периоду из таблицы оборотов. В остальных случаях сумма оборотов будет равна нулю. Пример решения данной задачи средствами СКД рассмотрен в этой статье.
ВЫБРАТЬ Дни.Период КАК Период, ПродажиОбороты.Товар КАК Товар, Сумма(Выбор когда Дни.Период = ПродажиОбороты.Период Тогда ПродажиОбороты.КоличествоОборот Иначе 0 Конец) КАК КоличествоОборот ИЗ Дни КАК Дни, РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК ПродажиОбороты СГРУППИРОВАТЬ ПО Дни.Период, ПродажиОбороты.Товар УПОРЯДОЧИТЬ ПО Товар, Период
Пусть у нас будет вот такая таблица по оборотному регистру Продажи
![1С Обороты на каждый день запросом](https://1c.alexcode.ru/wp-content/uploads/2023/11/2023-11-19-3-1024x677.png)
Сформируем запрос за период с 01.11.23 по 05.11.23 и убедимся, что в итоговый результат попадают записи в том числе и за те даты, когда продаж не было:
![1С обороты за каждый день запросом](https://1c.alexcode.ru/wp-content/uploads/2023/11/2023-11-19-2-1024x554.png)
Итак, мы рассмотрели примеры формирования остатков на каждый день при помощи запроса, а также оборотов на каждый день. Ключевой момент – нам нужна таблица со всеми датами в заданном интервале. С этой таблицей мы строим декартово произведение. Важно! Левое соединение нам тут не подойдет, т.к. для отсутствующих записей значения измерений будут NULL (в нашем примере у измерения Товар), и мы в дальнейшем не сможем наложить условия, сортировку, соединения и т.п. по этим строкам.