pt
/
forecast_api
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
forecast_api/build/build_pullforward.sql

286 lines
9.1 KiB
PL/PgSQL
Raw Permalink Blame History

/*
you have to be able to isolate the original plan in order for this to work
if you start with a clean 1+11 its not going to work
this script targets the 'plan' iteration and the 'diff' iteration
if plan orders have already been 'diffed' no need to build an additional diff
will have to be side-by-side roll-up of these two iterations
*/
--BEGIN;
--\timing
--CREATE TABLE IF NOT EXISTS rlarp.osmf_stage AS (SELECT * FROM rlarp.osmf_dev) WITH no data;
--will not match actuals exactly due to short-ships
WITH
---these dates will have to be syncronized with the forecast build-----------
tdr AS (
SELECT
-----------intented forecast range-----------------------------------
DATERANGE('2021-06-01','2022-06-01','[)') prange
-----------explicitly call out overlap period------------------------
-----------anythign in this period has to net out to match actuals---
,DATERANGE('2021-06-01','2021-07-31','[]') overlap
,jsonb_build_array('plan','diff') iter
)
------actual orders according to whatever is in the forecast, has to be defined as 'plan' and 'diff'--------
,booked AS materialized(
SELECT
billto_group
,shipto_group
,part_group
,sum(units) units
FROM
rlarp.osm_pool
WHERE
--find actual orders in the overlap range (where orders fall in both target ranges)
order_date <@ (SELECT overlap FROM tdr)
--anything that falls in this range is supposed to already match actuals, so no need to filter for actuals
GROUP BY
billto_group
,shipto_group
,part_group
)
--SELECT * FROM booked
------------plan units according to the 'plan' iteration of the current forecast----------------------------
,planned AS (
SELECT
p.billto_group
,p.shipto_group
,p.part_group
,p.order_date
,p.ship_date
,sum(p.units) FILTER (WHERE iter = 'plan') plan_units
,sum(p.units) FILTER (WHERE iter = 'diff') diff_units
,b.units booked
FROM
rlarp.osm_pool p
INNER JOIN booked b ON
b.billto_group = p.billto_group AND
b.shipto_group = p.shipto_group AND
b.part_group = p.part_group
WHERE
--need to be able to isolate the original budget
--budget orders have already been trued up to match actual, so should not be included in the this isolation
--find actual orders in the overlap range (where orders fall in both target ranges)
p.order_date <@ (SELECT prange FROM tdr)
AND (SELECT iter FROM tdr) ? p.iter
GROUP BY
p.billto_group
,p.shipto_group
,p.part_group
,p.order_date
,p.ship_date
,b.units
)
--SELECT * FROM planned LIMIT 100
-------flag rows by preparing a rolling total in this step------------------
,plan_ranked AS (
SELECT
p.billto_group
,p.shipto_group
,p.part_group
,p.order_date
,p.ship_date
,round(p.plan_units,2) plan_units
,round(SUM(p.plan_units) FILTER (WHERE order_date >= (SELECT upper(overlap) FROM tdr)) OVER (PARTITION BY p.billto_group, p.shipto_group, p.part_group ORDER BY p.order_date, p.ship_date),2) plan_rolling
,round(SUM(p.plan_units) OVER (PARTITION BY p.billto_group, p.shipto_group, p.part_group ),2) plan_units_tot
--,round(SUM(COALESCE(p.plan_units,0) + COALESCE(p.diff_units,0)) OVER (PARTITION BY p.billto_group, p.shipto_group, p.part_group ORDER BY p.order_date, p.ship_date),2) net_units_agg
,round(COALESCE(p.diff_units,0) + coalesce(p.plan_units,0),2) net_units
,round(SUM(COALESCE(p.plan_units,0) + COALESCE(p.diff_units,0)) OVER (PARTITION BY p.billto_group, p.shipto_group, p.part_group),2) net_units_tot
--,round(p.booked,2) booked
FROM
planned p
)
--SELECT * FROM plan_ranked
---flag future budget where up to the extent that actuals are larger than budget, but not more------
,eval AS (
SELECT
p.billto_group
,p.shipto_group
,p.part_group
,p.order_date
,p.ship_date
,p.plan_units
,p.plan_rolling
,p.plan_units_tot
--,p.net_units_agg
,p.net_units_tot
,p.net_units
--,p.booked
-----total current units less plan total units is the excess
-----if the current row contributes to the excess, flag it
-----the last row will likely pull out too much, it will need split probably
,(plan_rolling - plan_units) <= (p.net_units_tot - p.plan_units_tot) flag
,-(p.net_units_tot - p.plan_units_tot) max_possible_reduction
,-CASE
WHEN plan_rolling <= (p.net_units_tot - p.plan_units_tot) THEN plan_units
WHEN plan_rolling > (p.net_units_tot - p.plan_units_tot) AND (plan_rolling - plan_units) <= (p.net_units_tot - p.plan_units_tot) THEN (p.net_units_tot - p.plan_units_tot) - (plan_rolling - plan_units)
END qty
--,CASE WHEN (p.plan_units_agg - p.plan_units) > p.booked AND p.plan_units_agg <= p.booked * 2 THEN true ELSE false END remove
FROM
plan_ranked p
)
--SELECT * FROM eval LIMIT 10000
----------------create a log entry--------------------
,log AS (
INSERT INTO
rlarp.osm_log(doc)
SELECT
$${
"message":"remove future orders to the extent the new forecast totals more than the original budget",
"tag":"1+11",
"type":"build"
}$$::jsonb doc
RETURNING *
)
,remove AS (
SELECT
o.fspr
,o.plnt
,o.promo
,o.terms
,o.bill_cust_descr
,o.ship_cust_descr
,o.dsm
,o.quota_rep_descr
,o.director
,o.billto_group
,o.shipto_group
,o.chan
,o.chansub
,o.chan_retail
,o.part
,o.part_descr
,o.part_group
,o.branding
,o.majg_descr
,o.ming_descr
,o.majs_descr
,o.mins_descr
,o.segm
,o.substance
,o.fs_line
,o.r_currency
,o.r_rate
,o.c_currency
,o.c_rate
-----only keep the percentage of the current row determined by the last step--------
,round(-sum(o.units ),2)*(-e.qty/e.plan_units) units
,round(-sum(o.value_loc),2)*(-e.qty/e.plan_units) value_loc
,round(-sum(o.value_usd),2)*(-e.qty/e.plan_units) value_usd
,round(-sum(o.cost_loc ),2)*(-e.qty/e.plan_units) cost_loc
,round(-sum(o.cost_usd ),2)*(-e.qty/e.plan_units) cost_usd
,o.calc_status
,o.flag
,o.order_date
,o.order_month
,o.order_season
,o.request_date
,o.request_month
,o.request_season
,o.ship_date
,o.ship_month
,o.ship_season
,'1+11' AS version
,'pf' iter
,log.id
,coalescE(log.doc->>'tag','') AS "tag"
,log.doc->>'message' AS "comment"
,log.doc->>'type' module
FROM
rlarp.osm_pool o
INNER JOIN eval e ON
e.billto_group = o.billto_group AND
e.shipto_group = o.shipto_group AND
e.part_group = o.part_group AND
e.order_date = o.order_date AND
e.ship_date = o.ship_date
CROSS JOIN log
WHERE
e.flag
AND e.plan_units <> 0
GROUP BY
o.fspr
,o.plnt
,o.promo
,o.terms
,o.bill_cust_descr
,o.ship_cust_descr
,o.dsm
,o.quota_rep_descr
,o.director
,o.billto_group
,o.shipto_group
,o.chan
,o.chansub
,o.chan_retail
,o.part
,o.part_descr
,o.part_group
,o.branding
,o.majg_descr
,o.ming_descr
,o.majs_descr
,o.mins_descr
,o.segm
,o.substance
,o.fs_line
,o.r_currency
,o.r_rate
,o.c_currency
,o.c_rate
,(-e.qty/e.plan_units) --removal_rate
,o.calc_status
,o.flag
,o.order_date
,o.order_month
,o.order_season
,o.request_date
,o.request_month
,o.request_season
,o.ship_date
,o.ship_month
,o.ship_season
,log.id
,coalescE(log.doc->>'tag','')
,log.doc->>'message'
,log.doc->>'type'
-----some bad forecast swings, if one is removed it's wacky
-----cap at -1,000,000
HAVING
round(-sum(o.value_loc),2) > -1000000
)
INSERT INTO rlarp.osm_pool SELECT * FROM remove;
-------sorted pull-forward scenarios------
--SELECT
-- billto_group,
-- shipto_group,
-- part_group,
-- order_date,
-- ship_date,
-- SUM(value_usd)
--FROM
-- remove
--GROUP BY
-- billto_group,
-- shipto_group,
-- part_group,
-- order_date,
-- ship_date
--ORDER BY
-- sum(value_usd) asc
--LIMIT 1000
-----pull-forward by month-----
--SELECT
-- order_season,
-- order_month,
-- SUM(value_usd)
--FROM
-- remove
--GROUP BY
-- order_season
-- order_month
--ROLLBACK;
Reference in New Issue View Git Blame Copy Permalink