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

23.01.17

Разработка - Запросы

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

Скачать файлы

Наименование Файл Версия Размер
Получение остатков запросом на каждый день
.epf 7,70Kb
11
.epf 7,70Kb 11 Скачать

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

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

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

Период Номенклатура Количество
01.05.2016 Товар 01 4
11.05.2016 Товар 01 3
16.05.2016 Товар 01 2
31.05.2016 Товар 01 2

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

Период Номенклатура Количество
01.05.2016 Товар 01 4
02.05.2016 Товар 01 4
03.05.2016 Товар 01 4
04.05.2016 Товар 01 4
05.05.2016 Товар 01 4
06.05.2016 Товар 01 4
07.05.2016 Товар 01 4
08.05.2016 Товар 01 4
09.05.2016 Товар 01 4
10.05.2016 Товар 01 4
11.05.2016 Товар 01 3
12.05.2016 Товар 01 3
13.05.2016 Товар 01 3
14.05.2016 Товар 01 3
15.05.2016 Товар 01 3
16.05.2016 Товар 01 2
17.05.2016 Товар 01 2
18.05.2016 Товар 01 2
19.05.2016 Товар 01 2
20.05.2016 Товар 01 2
21.05.2016 Товар 01 2
22.05.2016 Товар 01 2
23.05.2016 Товар 01 2
24.05.2016 Товар 01 2
25.05.2016 Товар 01 2
26.05.2016 Товар 01 2
27.05.2016 Товар 01 2
28.05.2016 Товар 01 2
29.05.2016 Товар 01 2
30.05.2016 Товар 01 2
31.05.2016 Товар 01 2

Посмотрим как мы можем это получить. Вот текст самого запроса:

ВЫБРАТЬ
	ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, aa.a * 1000 + bb.b * 100 + cc.c * 10 + dd.d) КАК Период
ПОМЕСТИТЬ ВТ_ТабДат
ИЗ
	(ВЫБРАТЬ
		0 КАК a
	
	ОБЪЕДИНИТЬ
	
	ВЫБРАТЬ
		1
	
	ОБЪЕДИНИТЬ
	
	ВЫБРАТЬ
		2
	
	ОБЪЕДИНИТЬ
	
	ВЫБРАТЬ
		3
	
	ОБЪЕДИНИТЬ
	
	ВЫБРАТЬ
		4
	
	ОБЪЕДИНИТЬ
	
	ВЫБРАТЬ
		5
	
	ОБЪЕДИНИТЬ
	
	ВЫБРАТЬ
		6
	
	ОБЪЕДИНИТЬ
	
	ВЫБРАТЬ
		7
	
	ОБЪЕДИНИТЬ
	
	ВЫБРАТЬ
		8
	
	ОБЪЕДИНИТЬ
	
	ВЫБРАТЬ
		9) КАК aa
		ПОЛНОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
			0 КАК b
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			1
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			2
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			3
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			4
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			5
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			6
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			7
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			8
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			9) КАК bb
		ПО (ИСТИНА)
		ПОЛНОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
			0 КАК c
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			1
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			2
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			3
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			4
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			5
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			6
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			7
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			8
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			9) КАК cc
		ПО (ИСТИНА)
		ПОЛНОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
			0 КАК d
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			1
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			2
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			3
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			4
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			5
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			6
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			7
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			8
		
		ОБЪЕДИНИТЬ
		
		ВЫБРАТЬ
			9) КАК dd
		ПО (ИСТИНА)
ГДЕ
	aa.a * 1000 + bb.b * 100 + cc.c * 10 + dd.d <= РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ)
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	Номенклатура.Ссылка КАК Номенклатура
ПОМЕСТИТЬ ВТ_ТабТоваров
ИЗ
	Справочник.Номенклатура КАК Номенклатура
ГДЕ
	НЕ Номенклатура.ЭтоГруппа
	И НЕ Номенклатура.ПометкаУдаления
	И Номенклатура.ВидНоменклатуры = ЗНАЧЕНИЕ(Перечисление.ВидыНоменклатуры.Товар)
	И Номенклатура.Ссылка = &ПарамОтборТовар
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	ВТ_ТабДат.Период,
	ВТ_ТабТоваров.Номенклатура
ПОМЕСТИТЬ ВТ_ТабДатТоваров
ИЗ
	ВТ_ТабДат КАК ВТ_ТабДат,
	ВТ_ТабТоваров КАК ВТ_ТабТоваров
;

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

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

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

УПОРЯДОЧИТЬ ПО
	Период

Запрос идет по временным таблицам - как бы даже не исбыточно, но так читается удобнее. По таблицам:

1. Получаем таблицу с дневными периодами - только даты начала каждого дня (это точно не мое!);

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

3. Соединяем  эти две таблицы полным соединением, без указания связи (для педантов: Можно добавить строчку "ПО ИСТИНА" - рещультат будет тот же);

4. Соединяем левым соединением таблицу периодов с номенклатурой с виртуальной таблицей регистра "Остатки и обороты". Периодичность таблицы - день, метод дополнения не указываем. Период выборки задаем тот же, что и для таблицы периодов. Тут основная хитрость в связи. Связываем таблицы по Номенклатуре и Периоду, но период сравниваем не на равенство, а на ПериодТаблицыПериодов >= ПериодТаблицыОстатковИОборотовРегистра

Таким образом например для даты 15.05.2016 мы получим набор строк - все попадающие по условию связи:

Номенклатура Период (таблица периодов) Период (таблица регистра)
Товар 01 15.05.2016 01.05.2016
Товар 01 15.05.2016 11.05.2016

Сами цифирки в этой таблице мы не вытаскиваем - они нам пока не нужны. А эту табличку мы группируем по периоду из таблицы периодов и номенклатуре. Период таблицы регистра получаем максимальный "МАКСИМУМ(ОстаткиТоваровОстаткиИОбороты.Период) КАК ПериодРегистра". Таким образом наша табличка примера схлопывается до одной строки:

Номенклатура Период (таблица периодов) Период (таблица регистра)
Товар 01 15.05.2016 11.05.2016

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

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

Вот такое вот решение задачи. Меня оно устроило - может еще кому пригодится.

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

См. также

SALE! 20%

Infostart Toolkit: Инструменты разработчика 1С 8.3 на управляемых формах

Инструментарий разработчика Роли и права Запросы СКД Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

10000 8000 руб.

02.09.2020    122421    673    389    

716

Для чего используют конструкцию запроса "ГДЕ ЛОЖЬ" в СКД на примере конфигурации 1С:ERP

Запросы СКД Платформа 1С v8.3 Запросы Система компоновки данных 1С:ERP Управление предприятием 2 Бесплатно (free)

В типовых конфигурациях разработчики компании 1С иногда используют в отчетах, построенных на СКД, такую конструкцию, как "ГДЕ ЛОЖЬ". Такая конструкция говорит о том, что данные в запросе не будут получены совсем. Для чего же нужен тогда запрос?

13.02.2024    5757    KawaNoNeko    23    

23

Набор-объект для СКД по тексту или запросу

Запросы СКД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Есть список полей в виде текста, или запрос - закидываем в набор СКД.

1 стартмани

31.01.2024    2007    2    Yashazz    0    

29

Запрос 1С copilot

Инструментарий разработчика Запросы Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Пишем на человеческом языке, что нам надо, и получаем текст запроса на языке 1С. Используются большие языковые модели (LLM GPT) от OpenAI или Яндекс на выбор.

5 стартмани

15.01.2024    6301    31    mkalimulin    25    

50

PrintWizard: поддержка представлений ЗУП в конструкторе

Инструментарий разработчика Запросы Платформа 1С v8.3 Бесплатно (free)

Одной из интересных задач, стоящих в процессе разработки, была поддержка механизма представлений в ЗУП. Но не просто возможность исполнения запросов с ними. Основная проблема была в том, чтобы с ними было удобно работать, а именно: создавать, модифицировать и отлаживать. Кратко о том, что в итоге получилось...

14.12.2023    1750    vandalsvq    7    

29

Объектная модель запроса "Схема запроса" 2

Запросы Платформа 1С v8.3 Запросы Конфигурации 1cv8 Бесплатно (free)

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

06.12.2023    5399    user1923546    26    

43

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

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

11.10.2023    16203    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Константин С. 666 23.01.17 12:47 Сейчас в теме
1) пометочку на какай конфигурации делали
2) в чем особенно вашей разработке, относительно аналогичных примеров)
2. Homyak 19 24.01.17 10:29 Сейчас в теме
(1)
В тексте статьи есть насчет конфигурации - самописная.
Насчет особенностей - таблица остатков по дням собирается одним запросом, никаких предварительных таблиц, никакой дополнительной обработки результата после, не нужны вспомогательные регистры. По сравнению с аналогичными примерами - мне так кажется что получился более удобный и читабельный текст запроса.
3. kvb1 12 25.01.17 14:40 Сейчас в теме
Что мешает использовать СКД без всяких извратов
4. alex-l19041 8 25.01.17 15:10 Сейчас в теме
реальная альтернатива первому запросу - использовать регистр Регламентированный производственный календарь (есть во многих конфигурациях). Там можно добавить условия по типу дня (рабочий, праздничный) Вряд ли кому нужны остатки на праздничный день
5. Homyak 19 26.01.17 10:25 Сейчас в теме
(4)
остатки


Согласен, но не во всех конфигурациях календарь присутствует. В моей не было. А первый запрос - только выглядит страшно, на самом деле ничего сложного.
6. agentesecreto 66 05.07.19 10:11 Сейчас в теме
для тех кто долго въезжает (типа меня): логика первого запроса в том, чтобы получить список дат между началом периода и окончанием периода, точнее говоря - функция f(х) = НачалоПериода+0, НачалоПериода+1, НачалоПериода+2, НачалоПериода+3, и так далее до х=9999, либо до разницы дат
7. agentesecreto 66 05.07.19 10:16 Сейчас в теме
пытаюсь применить данный метод на своей базе, получается странная картина
15.05.19 НачОст 603 Приход 0 Расход 54 КонОст 549 - правильно
16.05.19 НачОст 603 Приход 0 Расход 54 КонОст 549 - ???
17.05.19 НачОст 549 Приход 0 Расход 24 КонОст 525 - правильно
на самом деле 16.05.19 движений по регистру не было! запрос должен был показать НачОст 549 и Расход 0, но вместо этого дублирует состояние по предыдущему дню.
и поэтому вопрос: это косяк запроса? или надо какие-то действия предпринимать для правильных данных, типа пересчета регистра?
9. agentesecreto 66 05.07.19 11:57 Сейчас в теме
(7) короче, поразбиравшись, я понял. запрос в данном виде заточен на получение корректного _конечного_ остатка на каждый день. но если требуются так же _начальные_ остатки и/или _движения_ - то будет _недостаточно_ просто добавить в последний запрос строки "КоличествоПриход КАК КоличествоПриход" и "КоличествоНачальныйОстаток КАК КоличествоНачальныйОстаток", ибо требуется допилить логику отчета для получения этих данных.
8. acanta 05.07.19 10:32 Сейчас в теме
Почему бы в таком случае для получения таблицы с датами не оформить какую нибудь функцтию общего модуля, возвращающую таблицу значений типизированные и не брать её в качестве временной таблицы?
agentesecreto; +1 Ответить
10. mosfod 22 12.08.22 10:48 Сейчас в теме
отличное решение, простое и на поверхности
11. sksoft7 22.12.22 10:10 Сейчас в теме
Спасибо!
Хорошее решение для остатков. Работает!
Оставьте свое сообщение