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

Что такое нарастающие итоги можно посмотреть здесь: //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

 

УПОРЯДОЧИТЬ ПО ОстаткиПоЗаказам.Номенклатура, ОстаткиПоЗаказам.Точка

;

---

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

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

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

P.S. На правах рекламы. Мы так же разрабатываем отличные сайты. — www.ktc-ua.com.