Нарастающие итоги. Объединение двух таблиц с нарастающими итогами.

21.10.12

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

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

Скачать файл

ВНИМАНИЕ: Файлы из Базы знаний - это исходный код разработки. Это примеры решения задач, шаблоны, заготовки, "строительные материалы" для учетной системы. Файлы ориентированы на специалистов 1С, которые могут разобраться в коде и оптимизировать программу для запуска в базе данных. Гарантии работоспособности нет. Возврата нет. Технической поддержки нет.

Наименование По подписке [?] Купить один файл
Отчет на СКД с двумя вариантами
.erf 7,48Kb
28
28 Скачать (1 SM) Купить за 1 850 руб.

Что такое нарастающие итоги можно посмотреть здесь: //infostart.ru/public/61295/

Постановка задачи.

Менеджерам компании понадобилось знать, что нужно заказать у поставщиков для клиентов и видеть что уже едет под заказы клиентов. Они захотели использовать метод ФИФО, то есть кто раньше заказал тому и раньше приедет товар, дата отгрузки товаров и дата поставки товаров из заказов не учитывается. Так же считается что все заказы имеют уникальную дату. Операции учета ведутся в Управлении торговлей версии 11.0.9.8.

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

 

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


ВЫБРАТЬ
         Номенклатура
ПОМЕСТИТЬ СпрНоменклатура
ИЗ
         РегистрНакопления.СвободныеОстатки.Остатки
ГДЕ
         ВНаличииОстаток - ВРезервеОстаток < 0
ОБЪЕДИНИТЬ
ВЫБРАТЬ
         Номенклатура
ИЗ
         РегистрНакопления.ЗаказыПоставщикам.Остатки
ИНДЕКСИРОВАТЬ ПО
         Номенклатура;

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

 

2. Далее выбираем остатки заказов поставщиков и клиентов.


ВЫБРАТЬ
         ОстаткиЗаказов.ЗаказПоставщику.Дата КАК Период,
         ОстаткиЗаказов.Номенклатура,
         ОстаткиЗаказов.ЗаказПоставщику,
         ОстаткиЗаказов.КОформлениюОстаток
ПОМЕСТИТЬ ОстаткиЗаказовПоставщиков
ИЗ
         РегистрНакопления.ЗаказыПоставщикам.Остатки(, Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ОстаткиЗаказов
;
ВЫБРАТЬ
         ОстаткиЗаказов.ЗаказКлиента.Дата КАК Период,
         ОстаткиЗаказов.Номенклатура,
         ОстаткиЗаказов.ЗаказКлиента,
         ОстаткиЗаказов.ЗаказаноОстаток
ПОМЕСТИТЬ ОстаткиЗаказовКлиентов
Из
         РегистрНакопления.ЗаказыКлиентов.Остатки(, ЗаказКлиента.Статус НЕ В (ЗНАЧЕНИЕ(Перечисление.СтатусыЗаказовКлиентов.НеСогласован), ЗНАЧЕНИЕ(Перечисление.СтатусыЗаказовКлиентов.Согласован))
                                                                          И Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ОстаткиЗаказов
;

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

 

3. Получаем для остатков заказов поставщиков и клиентов нарастаюище итоги.


ВЫБРАТЬ
         ОстаткиПоЗаказам.Номенклатура,
         ОстаткиПоЗаказам.ЗаказПоставщику,
         ОстаткиПоЗаказам.КОформлениюОстаток,
         СУММА(ОстаткиПоЗаказамКопия.КОформлениюОстаток) КАК СуммаПосле,
         СУММА(ОстаткиПоЗаказамКопия.КОформлениюОстаток) - ОстаткиПоЗаказам.КОформлениюОстаток КАК СуммаДо
ПОМЕСТИТЬ КОформлениюНарастающие
ИЗ
         ОстаткиЗаказовПоставщиков КАК ОстаткиПоЗаказам
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ОстаткиЗаказовПоставщиков КАК ОстаткиПоЗаказамКопия
ПО  ОстаткиПоЗаказам.Номенклатура = ОстаткиПоЗаказамКопия.Номенклатура
И    ОстаткиПоЗаказам.Период >= ОстаткиПоЗаказамКопия.Период
СГРУППИРОВАТЬ ПО
         ОстаткиПоЗаказам.Номенклатура,
         ОстаткиПоЗаказам.ЗаказПоставщику,
         ОстаткиПоЗаказам.КОформлениюОстаток
;
ВЫБРАТЬ
         ОстаткиПоЗаказам.Номенклатура,
         ОстаткиПоЗаказам.ЗаказКлиента,
         ОстаткиПоЗаказам.ЗаказаноОстаток,
         СУММА(ОстаткиПоЗаказамКопия.ЗаказаноОстаток) КАК СуммаПосле,
         СУММА(ОстаткиПоЗаказамКопия.ЗаказаноОстаток) - ОстаткиПоЗаказам.ЗаказаноОстаток КАК СуммаДо
ПОМЕСТИТЬ ОстаткиНарастающие
ИЗ
         ОстаткиЗаказовКлиентов КАК ОстаткиПоЗаказам

ВНУТРЕННЕЕ СОЕДИНЕНИЕ ОстаткиЗаказовКлиентов КАК ОстаткиПоЗаказамКопия
ПО  ОстаткиПоЗаказам.Номенклатура = ОстаткиПоЗаказамКопия.Номенклатура
И    ОстаткиПоЗаказам.Период >= ОстаткиПоЗаказамКопия.Период
СГРУППИРОВАТЬ ПО
         ОстаткиПоЗаказам.Номенклатура,
         ОстаткиПоЗаказам.ЗаказКлиента,
         ОстаткиПоЗаказам.ЗаказаноОстаток
;

 

4. Теперь можно было бы попытаться объединить эти таблицы с помощью Полного, Левого и прочего соединения, но когда таблиц нарастающих итогов больше 2 это уже очень проблематично. Но есть довольно простое решение - достаточно выбрать номенклатуру с уникальными значениями "СуммаПосле" из нарастающих итогов обеих таблиц и потом к этой таблице присоединить все что нужно. 


ВЫБРАТЬ
         ОстаткиНарастающие.Номенклатура,
         ОстаткиНарастающие.СуммаПосле КАК Точка
ПОМЕСТИТЬ ТочкиПересечений
ИЗ
         ОстаткиНарастающие
ОБЪЕДИНИТЬ
ВЫБРАТЬ
         Номенклатура,
         СуммаПосле
ИЗ
         КОформлениюНарастающие
;

 

5. Получим общие нарастающие итоги заказов поставщиков и заказов клиентов.


ВЫБРАТЬ
         ТочкиПересечений.Номенклатура,
         ТочкиПересечений.Точка,

         КОформлениюНарастающие.ЗаказПоставщику,

         ОстаткиНарастающие.ЗаказКлиента,
         ОстаткиНарастающие.ЗаказаноОстаток,
         ОстаткиНарастающие.СуммаПосле,
         ОстаткиНарастающие.СуммаДо

ПОМЕСТИТЬ ОстаткиПоЗаказамНаростающие 
ИЗ
         ТочкиПересечений КАК ТочкиПересечений

ЛЕВОЕ СОЕДИНЕНИЕ КОформлениюНарастающие КАК КОформлениюНарастающие
ПО КОформлениюНарастающие.Номенклатура = ТочкиПересечений.Номенклатура
И    КОформлениюНарастающие.СуммаПосле >= ТочкиПересечений.Точка
И    КОформлениюНарастающие.СуммаДо < ТочкиПересечений.Точка
 
 
ЛЕВОЕ СОЕДИНЕНИЕ ОстаткиНарастающие КАК ОстаткиНарастающие
ПО ОстаткиНарастающие.Номенклатура = ТочкиПересечений.Номенклатура
И    ОстаткиНарастающие.СуммаПосле >= ТочкиПересечений.Точка
И    ОстаткиНарастающие.СуммаДо < ТочкиПересечений.Точка
;

 

6. Следующие 2 запроса формирует решение поставленной задачи с дополнительными полями "ВНаличии", "ВПеремещениях", "ВЗаказахПоставщику" и "НужноЗаказать".


ВЫБРАТЬ
         СпрНоменклатура.Номенклатура,
         IsNull(ОстаткиТоваров.ВНаличииОстаток, 0) КАК ВНаличии,
         IsNull(ОстаткиТоваров.ВРезервеОстаток, 0) КАК ВРезерве,
         IsNull(ТоварыВДвиженииПеремещения.КПоступлениюОстаток, 0) КАК ВПеремещениях,
         IsNull(ТоварыВДвиженииЗаказы.КПоступлениюОстаток, 0) КАК ВЗаказахПоставщику,
         IsNull(ОстаткиТоваров.ВНаличииОстаток, 0)
      + IsNull(ТоварыВДвиженииПеремещения.КПоступлениюОстаток, 0)
      + IsNull(ТоварыВДвиженииЗаказы.КПоступлениюОстаток, 0) КАК Доступно
 
ПОМЕСТИТЬ ТаблицаНаличия
ИЗ
         СпрНоменклатура КАК СпрНоменклатура

ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СвободныеОстатки.Остатки(,Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ОстаткиТоваров
ПО ОстаткиТоваров.Номенклатура = СпрНоменклатура.Номенклатура
 
 
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыКПоступлению.Остатки(, ТипЗначения(ДокументПоступления) <> Тип(Документ.ЗаказПоставщику)
                                                                                                     И Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ СпрНоменклатура)) КАК ТоварыВДвиженииПеремещения
ПО ТоварыВДвиженииПеремещения.Номенклатура = СпрНоменклатура.Номенклатура

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

ЛЕВОЕ СОЕДИНЕНИЕ ТаблицаНаличия КАК ТаблицаНаличия
ПО  ТаблицаНаличия.Номенклатура = ОстаткиПоЗаказам.Номенклатура
И    ТаблицаНаличия.Доступно < ОстаткиПоЗаказам.СуммаПосле
 
 
ГДЕ
        ТаблицаНаличия.Доступно - ТаблицаНаличия.ВРезерве < 0
 
УПОРЯДОЧИТЬ ПО ОстаткиПоЗаказам.Номенклатура, ОстаткиПоЗаказам.Точка
;

В результате получится что-то типа этого очета (с двумя вариантами внутри): 

 

 

Послесловие.

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

 

См. также

SALE! 15%

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

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

10000 руб.

02.09.2020    159428    872    399    

861

Запросы Программист Бесплатно (free)

Увидел cheatsheet по SQL и захотелось нарисовать подобное, но про запросы.

18.10.2024    9875    sergey279    18    

64

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

Столкнулся с интересной ситуацией, которую хотел бы разобрать, ввиду её неочевидности. Речь пойдёт про использование функции запроса АВТОНОМЕРЗАПИСИ() и проблемы, которые могут возникнуть.

11.10.2024    5167    XilDen    36    

80

Запросы Программист Запросы Бесплатно (free)

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

16.08.2024    7901    user1840182    5    

28

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

Рассмотрим быстрый алгоритм поиска дублей с использованием hash функции по набору полей шапки и табличных частей.

08.07.2024    2393    ivanov660    9    

22

Запросы СКД Программист Стажер Система компоновки данных Россия Бесплатно (free)

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

15.05.2024    8684    implecs_team    6    

47

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

Часто поступают задачи по произвольному распределению общих сумм. После распределения иногда пропадают копейки. Суть решения добавить АвтоНомерЗаписи() в ВТ распределения, и далее используя функции МАКСИМУМ или МИНИМУМ можем положить разницу копеек в первую или последнюю строку знаменателя распределения.

11.04.2024    3391    andrey_sag    10    

36
Оставьте свое сообщение