Расчетный остаток по прогнозным продажам в запросе
Всем привет!
Задача: получить прогнозный остаток на заданную дату, используя в качестве расхода - некую рассчитанную величину (типа среднедевной продажи), а также плановые поступления.
Я пошел по пути расчета остатка на каждый день в заданном периоде, т.к. по другому не придумал, как получить расчетный остаток на заданную дату.
Казалось бы - чего проще. Нарастающий итог в запросе по расходу и приходу, с учетом текущего остатка. Но нет - требуется учесть, что в некоторые периоды, расчетный остаток на складе может быть равен нулю, соответственно, в тот день расхода быть не должно.
И на этом я "сломался". Я не могу придумать, как мне посчитать нарастающий итог по расходу, который зависит как раз от нарастающего итога.
Например:
Товар, Дата, Приход, Расход
Товар1, 1.01.24, 0, 0 (средние продажи у нас 2 единицы, но т.к. товара на остатке нет - значит плановый расход 0)
Товар1, 2.01.24, 4, 2 (товар поступил в кол-ве 4 шт, значит есть плановый расход - средние продажи у нас 2 единицы)
Товар1, 3.01.24, 0, 2 (плановые продажи последних 2 единиц)
Товар1, 4.01.24, 0, 0 (средние продажи у нас 2 единицы, но т.к. товара на остатке нет - значит плановый расход 0)
И т.д.
Запрос писал, но уперся в логику
Задача: получить прогнозный остаток на заданную дату, используя в качестве расхода - некую рассчитанную величину (типа среднедевной продажи), а также плановые поступления.
Я пошел по пути расчета остатка на каждый день в заданном периоде, т.к. по другому не придумал, как получить расчетный остаток на заданную дату.
Казалось бы - чего проще. Нарастающий итог в запросе по расходу и приходу, с учетом текущего остатка. Но нет - требуется учесть, что в некоторые периоды, расчетный остаток на складе может быть равен нулю, соответственно, в тот день расхода быть не должно.
И на этом я "сломался". Я не могу придумать, как мне посчитать нарастающий итог по расходу, который зависит как раз от нарастающего итога.
Например:
Товар, Дата, Приход, Расход
Товар1, 1.01.24, 0, 0 (средние продажи у нас 2 единицы, но т.к. товара на остатке нет - значит плановый расход 0)
Товар1, 2.01.24, 4, 2 (товар поступил в кол-ве 4 шт, значит есть плановый расход - средние продажи у нас 2 единицы)
Товар1, 3.01.24, 0, 2 (плановые продажи последних 2 единиц)
Товар1, 4.01.24, 0, 0 (средние продажи у нас 2 единицы, но т.к. товара на остатке нет - значит плановый расход 0)
И т.д.
Запрос писал, но уперся в логику
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, 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 <= РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ)
;
ВЫБРАТЬ
ВЫРАЗИТЬ(ТЗ.Номенклатура КАК Справочник.Номенклатура) КАК Номенклатура
ПОМЕСТИТЬ ВТ_ТЗ
ИЗ
&ТЗ КАК ТЗ
;
ВЫБРАТЬ
ВТ_ТЗ.Номенклатура,
ВТ_ТЗ.Номенклатура.СПД_ВесьПериод КАК СПД,
ЕСТЬNULL(Рег.ВНаличииОстаток, 0) КАК Остаток
ПОМЕСТИТЬ ВТ_Ном
ИЗ
ВТ_ТЗ
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(, Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ ВТ_ТЗ)) КАК Рег
ПО ВТ_ТЗ.Номенклатура = Рег.Номенклатура
;
ВЫБРАТЬ
Рег.Номенклатура,
Рег.ДатаСобытия,
СУММА(КоличествоИзЗаказовСНеподтвержденнымиОстаток) КАК Количество
ПОМЕСТИТЬ ВТ_Поступ
ИЗ
РегистрНакопления.ГрафикПоступленияТоваров.Остатки(, Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ ВТ_ТЗ) И ДатаСобытия МЕЖДУ &НачалоПериода И &КонецПериода) КАК Рег
СГРУППИРОВАТЬ ПО
Рег.ДатаСобытия,
Рег.Номенклатура
;
ВЫБРАТЬ
ВТ_ТаблДат.Период,
ВТ_Ном.Номенклатура,
ВЫБОР КОГДА ВТ_ТаблДат.Период = &НачалоПериода
ТОГДА ВТ_Ном.Остаток+ЕСТЬNULL(ВТ_Поступ.Количество, 0)
ИНАЧЕ ЕСТЬNULL(ВТ_Поступ.Количество, 0)
КОНЕЦ КАК Приход,
ВТ_Ном.СПД КАК Расход
ПОМЕСТИТЬ ВТ_ТабДвижПред
ИЗ
ВТ_ТаблДат
ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Ном
ПО ИСТИНА
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Поступ
ПО ВТ_ТаблДат.Период = ВТ_Поступ.ДатаСобытия
И ВТ_Ном.Номенклатура = ВТ_Поступ.Номенклатура
;
ВЫБРАТЬ
ВТ_ТабДвижПред.Период,
ВТ_ТабДвижПред.Номенклатура,
ВТ_ТабДвижПред.Приход,
ВТ_ТабДвижПред.Расход,
СУММА(ВТ_ТабДвижПред2.Приход) КАК ПриходСумма,
СУММА(ВТ_ТабДвижПред.Расход) КАК РасходСумма
ПОМЕСТИТЬ ВТ_ТабДвиж
ИЗ
ВТ_ТабДвижПред
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТабДвижПред КАК ВТ_ТабДвижПред2
ПО ВТ_ТабДвижПред2.Номенклатура = ВТ_ТабДвижПред.Номенклатура
И ВТ_ТабДвижПред.Период >= ВТ_ТабДвижПред2.Период
СГРУППИРОВАТЬ ПО
ВТ_ТабДвижПред.Период,
ВТ_ТабДвижПред.Номенклатура,
ВТ_ТабДвижПред.Приход,
ВТ_ТабДвижПред.Расход
УПОРЯДОЧИТЬ ПО
ВТ_ТабДвижПред.Номенклатура,
ВТ_ТабДвижПред.Период
ПоказатьПо теме из базы знаний
Ответы
Подписаться на ответы
Инфостарт бот
Сортировка:
Древо развёрнутое
Свернуть все
(6) Если в лоб и не сильно думать, то можно решить как ниже, НО 100% если подумать, то есть более простое и изящное решение, чем ниже (SQLite).
with vt_post as
(SEL ECT 1 as tovar, DATETIME('2024-04-02T00:00:00.000') as DateX, 1 as kol_post
UNI ON ALL SEL ECT 1, DATETIME('2024-04-04T00:00:00.000'), 7
UNI ON ALL SEL ECT 1, DATETIME('2024-04-05T00:00:00.000'), 3
UNI ON ALL SELECT 1, DATETIME('2024-04-06T00:00:00.000'), 1
UNI ON ALL SELECT 1, DATETIME('2024-04-09T00:00:00.000'), 2
UNI ON ALL SELECT 2, DATETIME('2024-04-04T00:00:00.000'), 3
UNI ON ALL SELECT 2, DATETIME('2024-04-05T00:00:00.000'), 1
UNI ON ALL SELECT 2, DATETIME('2024-04-06T00:00:00.000'), 1
)
,vt_prod as (SELECT 1 as tovar, 2 as kol_prod_plan UNION ALL SEL ECT 2, 1)
,vt as
(SEL ECT
t.tovar as tovar
,t.kol_prod_plan as kol_prod_plan
,DATETIME('2024-03-31T00:00:00.000') as DateX
,0 as kol_post
,0 as ostatok
FR OM vt_prod as t
UNION ALL
SEL ECT
t.tovar as tovar
,t.kol_prod_plan as kol_prod_plan
,DATETIME(DateX, '+1 day')
,ifnull((SEL ECT t1.kol_post FR OM vt_post as t1 WHERE t1.tovar = t.tovar AND t1.DateX = DATETIME(t.DateX, '+1 day') LIMIT 1),0)
,iif(ostatok+ifnull((SEL ECT t1.kol_post FR OM vt_post as t1 WHERE t1.tovar = t.tovar AND t1.DateX = DATETIME(t.DateX, '+1 day') LIMIT 1),0)-t.kol_prod_plan<=0, 0
,ostatok+ifnull((SEL ECT t1.kol_post FR OM vt_post as t1 WH ERE t1.tovar = t.tovar AND t1.DateX = DATETIME(t.DateX, '+1 day') LIM IT 1),0)-t.kol_prod_plan)
FR OM vt as t
WH ERE DateX<DATETIME('2024-04-10T00:00:00.000')
)
SEL ECT
t.tovar, t.kol_prod_plan, t.DateX, t.kol_post
,iif(t.ostatok>0
,t.kol_prod_plan
,t.kol_post+lag(t.ostatok,1,0) over(PARTITION by tovar ORDER by DateX)) as kol_prod
,t.ostatok as ostatok
FR OM vt as t
ORDER by t.tovar, t.DateX
//*************** Результат ************************
tovar kol_prod_plan DateX kol_post kol_prod ostatok
1 2 2024-03-31 00:00:00 0 0 0
1 2 2024-04-01 00:00:00 0 0 0
1 2 2024-04-02 00:00:00 1 1 0
1 2 2024-04-03 00:00:00 0 0 0
1 2 2024-04-04 00:00:00 7 2 5
1 2 2024-04-05 00:00:00 3 2 6
1 2 2024-04-06 00:00:00 1 2 5
1 2 2024-04-07 00:00:00 0 2 3
1 2 2024-04-08 00:00:00 0 2 1
1 2 2024-04-09 00:00:00 2 2 1
1 2 2024-04-10 00:00:00 0 1 0
2 1 2024-03-31 00:00:00 0 0 0
2 1 2024-04-01 00:00:00 0 0 0
2 1 2024-04-02 00:00:00 0 0 0
2 1 2024-04-03 00:00:00 0 0 0
2 1 2024-04-04 00:00:00 3 1 2
2 1 2024-04-05 00:00:00 1 1 2
2 1 2024-04-06 00:00:00 1 1 2
2 1 2024-04-07 00:00:00 0 1 1
2 1 2024-04-08 00:00:00 0 1 0
2 1 2024-04-09 00:00:00 0 0 0
2 1 2024-04-10 00:00:00 0 0 0
ПоказатьПрикрепленные файлы:
Для получения уведомлений об ответах подключите телеграм бот:
Инфостарт бот