Остатки на каждый день в запросе

Публикация № 518415

Программирование - Практика программирования

Остатки остатков запрос запросом товара товар товаров каждый на день получение по каждому дню продукта продукции

Запрос формирует остатки товаров на каждый день в пределах выбранного периода.

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

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

Полный код запроса

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

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

Буду рад конструктивной критике в комментариях, а также предложениям по улучшению запроса.

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

	Наименование	Файл	Версия	Размер
	ОстаткиНаКаждыйДень .epf 7,82Kb 05.08.16 18	.epf		7,82Kb	18	Скачать

См. также

Отчет по оборачиваемости товаров

ВСТАВИТЬ В Справочник.Номенклатура (Код, Наименование) ЗНАЧЕНИЯ ("001", "Новый товар")

Коннектор: удобный HTTP-клиент для 1С:Предприятие 8

Заполняем по шаблону (по умолчанию)

Нечеткий поиск одним запросом

Работа с публикациями "Инфостарт"

Как выполнить отчет на СКД через COM и получить данные отчета?

Позиционирование в помещении с помощью нейросети по сигналу Wi-Fi. Интерактивная карта склада в 1С с показом позиции

1С: Предприятие + корпоративный чат, как наладить оперативные уведомления за 10 минут

Как нарисовать граф на 1С

Работа с данными выбора

Быстрое определение интервалов в запросе

Простой способ индексирования интервалов

Бесплатная проверка контрагентов в ФНС (общий модуль с алгоритмом). На примере выводим статус в список справочника контрагентов

Хранение файлов в томах на диске (для УПП 1.3)

БСП: Дополнительная обработка (Регламенты), примеры от простого к сложному

Некоторая работа с данными через COM

Работа со схемой запроса

Расширение возможностей печати: Вывод произвольного нижнего и верхнего колонтитула

Подсистема "Планировщик" (версия 1.1)

Печатная форма, сделанная как расширение конфигурации для БП 3.0. Новые возможности БСП

Специальные предложения

Комментарии

Избранное Подписка

1. lrs 27 27.04.16 16:15 Сейчас в теме

Указанный запрос не работает!
Виртуальную таблицу Остатков и оборотов Вы дополняете отсутствующими датами, но остатки на эти даты не дополняете!! Переделывайте)

2. arakelyan 306 27.04.16 23:39 Сейчас в теме

(1) lrs, Благодарю за комментарий. Скажите пожалуйста в какой конфигурации Вы использовали запрос? Мне очень интересна причина, по которой запрос у Вас не работает, но у меня пока недостаточно информации чтобы её понять. За прикрепленные снимки экрана тройная благодарность.

3. lrs 27 28.04.16 11:02 Сейчас в теме

Все очевидно, вы соединяете таблицы по дате, но за даты, за которые не было движений остаток отсутствует, поэтому в итоговой таблице у вас дырки (т.е. по сути нулевые остатки, которые на самом деле не нулевые)
Для примера смотрите картинку, где слева ведомомсть по товарам на складах с разворотом по дням, а справа итог выполнения вашего запроса. (например, 2,3го числа конечный остаток соответствует остатку за 1е число, но ваша таблица показывает 0 за эти дни...)
PS. Чтоб быстрее разобраться, сделайте отбор по одной номенклатуре, по одному складу. И выберите ту позицию для анализа, по которой хотябы один день не было движений.

Прикрепленные файлы:

4. arakelyan 306 28.04.16 12:31 Сейчас в теме

(3) lrs, понял что Вы имели в виду. Когда тестировал у себя такой проблемы не возникало. В приложении снимок моей ведомости и консоли, в запросе разумеется ничего не менял. Движение товаров также отсутствует за некоторые даты, но строк со значением NULL не появляется. Благодарю Вас за обратную связь, постараюсь разобраться с данной проблемой.

Прикрепленные файлы:

5. lrs 27 28.04.16 14:55 Сейчас в теме

(4) В итоге вы уже пришли ни к остаткам на каждый день, а просто к виртуальной таблице остатков и оборотов. Вы все дальше от решения поставленной задачи))
Даже на титульной картинке у вас пропущены даты (в основном это выходные), когда не было движений)))
Пропущенных дат в итоговой таблице Вы не видите, потому что делаете отбор по основному поставщику, обращаясь а элементам, которые отсутствуют в построенной таблице, либо перенесите отбор в условия таблицы остатков и оборотов (Вот сюда: РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(КОНЕЦПЕРИОДА(&Дата1), КОНЕЦПЕРИОДА(&Дата2), День, , Номенклатура.ОсновнойПоставщик = &ОсновнойПоставщик)), или уберите его на момент проверки..

6. arakelyan 306 28.04.16 18:49 Сейчас в теме

(5) lrs, запрос был переосмыслен. Новый запрос возвращает остатки на каждый день даже если не было движений.

7. schthaxe 27.01.17 01:01 Сейчас в теме

Добрый день! Решил переделать Ваш отчет так чтобы он показывал остатки по взаимозачетам на каждый месяц.
Регистр - ВзаиморасчетыСКонтрагентамиПоДокументамРасчетов, Периодичность - Регистратор.
Таблицу календаря беру Кросс-запросом.
В итоге всё получилось, но выяснился прискорбный глюк, который я не смог устранить -
Были движения по документу на НачалоПериода, то есть КонечныйОстаток и Оборот не равны нулю. То есть допустим НачалоПериода = 01.06.2016 00:00:00, и в точности в это же время прошла оплата по реализации ну скажем за апрель, то есть есть скажем ПКО или платежка за 01.06.2016 00:00:00.
В этом случае у нас получается на 01.06.2016 00:00:00 два остатка которые 1С почему то складывает! Итог получается больше на размер остатка на НачалоПериода! Заставить 1С разделить остатки мне не удалось, поэтому я просто сдвинул НачалоПериода на секунду.
В прикрепленной таблице - пример без группировки на суммы.

Прикрепленные файлы:

Проблема с отчетом.xls

8. arakelyan 306 27.01.17 11:27 Сейчас в теме

(7) Здравствуйте, если Вам не сложно пришлите пожалуйста запрос, который получился в итоге. Я попробую у себя его исполнить и найти причину.

9. schthaxe 27.01.17 13:49 Сейчас в теме

|ВЫБРАТЬ
		|	Движения.Контрагент,
		|	Движения.ДоговорКонтрагента,
		|	Движения.ДокументРасчетовСКонтрагентом,
		|	ДНИ.ДатаКалендаря КАК ДатаКалендаря,
		|	СУММА(ВЫБОР
		|			КОГДА Движения.Период <= &НачалоПериода
		|				ТОГДА Движения.СуммаВзаиморасчетовКонечныйОстаток
		|			ИНАЧЕ ВЫБОР
		|					КОГДА Движения.Период <= ДНИ.ДатаКалендаря
		|						ТОГДА Движения.СуммаВзаиморасчетовОборот
		|					ИНАЧЕ 0
		|				КОНЕЦ
		|		КОНЕЦ) КАК СуммаВзаиморасчетовКонечныйОстаток
		|ПОМЕСТИТЬ тВзаиморасчеты
		|ИЗ
		|	ДНИ КАК ДНИ
		|		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ВзаиморасчетыСКонтрагентамиПоДокументамРасчетов.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Регистратор, , {(ДоговорКонтрагента).*, (Сделка).*, (Контрагент).*, (Организация).*}) КАК Движения
		|		ПО (Движения.Период <= ДНИ.ДатаКалендаря)
		|
		|СГРУППИРОВАТЬ ПО
		|	Движения.Контрагент,
		|	Движения.ДоговорКонтрагента,
		|	ДНИ.ДатаКалендаря,
		|	Движения.ДокументРасчетовСКонтрагентом
		|;

Причина то понятна, я вам ее уже описал. В принципе наверно ее можно как то отфильтровывать отдельным запросом на началопериода... 
вот таким например - если данный запрос не пуст, значит беда.
ВЫБРАТЬ
	Вз.СуммаВзаиморасчетовНачальныйОстаток,
	Вз.СуммаВзаиморасчетовОборот,
	Вз.ДокументРасчетовСКонтрагентом
ИЗ
	РегистрНакопления.ВзаиморасчетыСКонтрагентамиПоДокументамРасчетов.ОстаткиИОбороты(&НачалоПериода, &НачалоПериода, , , ) КАК Вз
ГДЕ
	Вз.СуммаВзаиморасчетовНачальныйОстаток <> 0
	И Вз.СуммаВзаиморасчетовОборот <> 0

Показать

10. arakelyan 306 27.01.17 14:57 Сейчас в теме

(9) Прогнал запрос у себя. Да есть такое дело. Решение Вы уже описали. Если нужны остатки взаиморасчетов по дням например с 2.01.2017 по 1.27.2017 тогда параметр НачалоПериода устанавливаем на конец предыдущего дня, то есть 1.01.2017 23:59:59. В таком случае в результирующей таблице мы будем видеть корректные остатки начиная с 2.01.2017.

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

СУММА(ВЫБОР
            КОГДА Движения.Период = &НачалоПериода
                ТОГДА Движения.КоличествоКонечныйОстаток
            ИНАЧЕ ВЫБОР
                    КОГДА Движения.Период <= ДНИ.ДатаКалендаря
                        ТОГДА Движения.КоличествоОборот
                    ИНАЧЕ 0
                КОНЕЦ
        КОНЕЦ)

Показать

В итоге у меня всё работает правильно и насколько я понял у Вас тоже, верно?

11. schthaxe 28.01.17 14:13 Сейчас в теме

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

12. amazonka 24 17.11.17 11:49 Сейчас в теме

интересный подход

13. arakelyan 306 17.11.17 15:17 Сейчас в теме

(12)Не могу не согласиться)

14. palsergeich 16.01.18 23:12 Сейчас в теме

Спрашивают этот запрос на каждом 2м собеседовании. Или его или срез последних на каждый день.
Задача интересная, но чисто с точки зрения алгоритмики.
Но нужно понимать что на подзапросе вылететь в Out of memory - ну очень просто.
А есть еще вопрос со звездочкой: а что делать если нам постоянно надо обращаться к остаткам (оборотам) на каждый день (например реальная задача - план закупок для большой сети который актуализируется чуть ли не раз в 10 минут).
Правильный ответ: Храните их расчитанными. Ибо как не получай эти данные - или запрос в цикле (хардкод в коде, неявные запросы в цикле при помощи СКД) или гигантские промежуточные результаты. Нельзя получить быстро, что не хранится. Да минусы у этого подхода: 1) рост базы 2) отставание актуальности данных на частоту выполнения регламентного задания. Плюс другой - при частом выполнении данного запроса будет очень сильная нагрузка на базу. И если база большая, или не большая, но период большой, то время выполнения данного запроса будет или не удовлетворительным или начнутся технологические проблемы.
Из своей практики: В реальной жизни данный запрос получается объединением данных за прошлые числа из расчитанного регистра и данные вирт таблиц за сегодня. Все работает более чем быстро.