pt/forecast_api
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
master
forecast_api/route_sql/scale_vd.sql

297 lines
8.4 KiB
MySQL
Raw Permalink Normal View History

prevent div by zero
2019-03-21 16:50:51 -04:00
WITH
recast scale_v to be based on qty and handle -0- qty
2019-04-03 01:42:10 -04:00
target AS (select incr_qty qincr)
,testv AS (
SELECT
start on scale_vd
2020-02-13 02:25:24 -05:00
sum(units) tot
include plan and diff ase baseline iterations
2021-07-16 09:40:34 -04:00
,sum(units) FILTER (WHERE iter IN ('copy','plan','diff')) base
embed the module in the forecast pool instead of reading the json, cross join the newly inserted log file and id
2020-02-13 11:03:42 -05:00
,sum(units) FILTER (WHERE module = 'new basket') newpart
recast scale_v to be based on qty and handle -0- qty
2019-04-03 01:42:10 -04:00
FROM
start on scale_vd
2020-02-13 02:25:24 -05:00
rlarp.osm_pool p
recast scale_v to be based on qty and handle -0- qty
2019-04-03 01:42:10 -04:00
WHERE
-----------------scenario----------------------------
where_clause
-----------------additional params-------------------
AND calc_status||flag <> 'CLOSEDREMAINDER' --exclude short ships when building order adjustments
start on scale_vd
2020-02-13 02:25:24 -05:00
AND order_date <= ship_date
recast scale_v to be based on qty and handle -0- qty
2019-04-03 01:42:10 -04:00
)
,flagv AS (
SELECT
tot
,base
,newpart
,CASE WHEN tot = 0 THEN
CASE WHEN base = 0 THEN
CASE WHEN newpart = 0 THEN
'unclean data. tested -> does not exist'
ELSE
'scale new part'
END
ELSE
'scale copy'
END
ELSE
'scale all'
END flag
FROM
testv
)
--ever need receive a target of -0- qty but value <> 0?
prevent div by zero
2019-03-21 16:50:51 -04:00
,basemix AS (
start on scale_vd
2020-02-13 02:25:24 -05:00
SELECT
fspr
,plnt ---master data
include promo code in base mix
2019-04-03 12:17:54 -04:00
,promo --history date mix
prevent div by zero
2019-03-21 16:50:51 -04:00
,terms
start on scale_vd
2020-02-13 02:25:24 -05:00
,bill_cust_descr --history cust mix
,ship_cust_descr --history cust mix
,dsm
,quota_rep_descr --master data
,director
,billto_group --master data
,shipto_group
prevent div by zero
2019-03-21 16:50:51 -04:00
,chan --master data
start on scale_vd
2020-02-13 02:25:24 -05:00
,chansub
,chan_retail
,part
,part_descr
,part_group
,branding
,majg_descr
,ming_descr
,majs_descr
,mins_descr
,segm
add substance column and move some files out of the route folder that aren't actually being used by routes
2020-02-19 13:25:28 -05:00
,substance
prevent div by zero
2019-03-21 16:50:51 -04:00
,fs_line --master data
,r_currency --history cust mix
,r_rate --master data
,c_currency --master data
,c_rate --master data
start on scale_vd
2020-02-13 02:25:24 -05:00
,sum(coalesce(units,0)) units --history value
,sum(coalesce(value_loc,0)) value_loc --history value
,sum(coalesce(value_usd,0)) value_usd --0
,sum(coalesce(cost_loc,0)) cost_loc --history part mix
,sum(coalesce(cost_usd,0)) cost_usd
prevent div by zero
2019-03-21 16:50:51 -04:00
,calc_status --0
,flag --0
start on scale_vd
2020-02-13 02:25:24 -05:00
,order_date --history date mix
prevent div by zero
2019-03-21 16:50:51 -04:00
,order_month
start on scale_vd
2020-02-13 02:25:24 -05:00
,order_season
,request_date --history date mix
prevent div by zero
2019-03-21 16:50:51 -04:00
,request_month
start on scale_vd
2020-02-13 02:25:24 -05:00
,request_season
,ship_date --history date mix
,ship_month
,ship_season
prevent div by zero
2019-03-21 16:50:51 -04:00
FROM
start on scale_vd
2020-02-13 02:25:24 -05:00
rlarp.osm_pool
prevent div by zero
2019-03-21 16:50:51 -04:00
WHERE
-----------------scenario----------------------------
where_clause
-----------------additional params-------------------
recast scale_v to be based on qty and handle -0- qty
2019-04-03 01:42:10 -04:00
AND CASE (SELECT flag FROM flagv)
WHEN 'scale all' THEN true
include plan and diff ase baseline iterations
2021-07-16 09:40:34 -04:00
WHEN 'scale copy' THEN iter IN ('copy','plan','diff')
embed the module in the forecast pool instead of reading the json, cross join the newly inserted log file and id
2020-02-13 11:03:42 -05:00
WHEN 'scale new part' THEN module = 'new basket'
recast scale_v to be based on qty and handle -0- qty
2019-04-03 01:42:10 -04:00
END
prevent div by zero
2019-03-21 16:50:51 -04:00
AND calc_status||flag <> 'CLOSEDREMAINDER' --exclude short ships when building order adjustments
start on scale_vd
2020-02-13 02:25:24 -05:00
AND order_date <= ship_date
prevent div by zero
2019-03-21 16:50:51 -04:00
GROUP BY
include plan and diff ase baseline iterations
2021-07-16 09:40:34 -04:00
fspr
start on scale_vd
2020-02-13 02:25:24 -05:00
,plnt ---master data
,promo --history date mix
prevent div by zero
2019-03-21 16:50:51 -04:00
,terms
start on scale_vd
2020-02-13 02:25:24 -05:00
,bill_cust_descr --history cust mix
,ship_cust_descr --history cust mix
,dsm
,quota_rep_descr --master data
,director
,billto_group --master data
,shipto_group
prevent div by zero
2019-03-21 16:50:51 -04:00
,chan --master data
start on scale_vd
2020-02-13 02:25:24 -05:00
,chansub
,chan_retail
,part
,part_descr
,part_group
,branding
,majg_descr
,ming_descr
,majs_descr
,mins_descr
,segm
add substance column and move some files out of the route folder that aren't actually being used by routes
2020-02-19 13:25:28 -05:00
,substance
prevent div by zero
2019-03-21 16:50:51 -04:00
,fs_line --master data
,r_currency --history cust mix
,r_rate --master data
,c_currency --master data
,c_rate --master data
,calc_status --0
,flag --0
start on scale_vd
2020-02-13 02:25:24 -05:00
,order_date --history date mix
prevent div by zero
2019-03-21 16:50:51 -04:00
,order_month
start on scale_vd
2020-02-13 02:25:24 -05:00
,order_season
,request_date --history date mix
prevent div by zero
2019-03-21 16:50:51 -04:00
,request_month
start on scale_vd
2020-02-13 02:25:24 -05:00
,request_season
,ship_date --history date mix
,ship_month
,ship_season
prevent div by zero
2019-03-21 16:50:51 -04:00
)
recast scale_v to be based on qty and handle -0- qty
2019-04-03 01:42:10 -04:00
--SELECT * FROM basemix
prevent div by zero
2019-03-21 16:50:51 -04:00
,scale AS (
SELECT
recast scale_v to be based on qty and handle -0- qty
2019-04-03 01:42:10 -04:00
(SELECT qincr::numeric FROM target) incr
start on scale_vd
2020-02-13 02:25:24 -05:00
,(SELECT sum(value_loc *r_rate) FROM basemix) base
,(SELECT qincr FROM target)/(SELECT SUM(units) FROM basemix) factor
prevent div by zero
2019-03-21 16:50:51 -04:00
)
recast scale_v to be based on qty and handle -0- qty
2019-04-03 01:42:10 -04:00
--select * from scale
start on scale_vd
2020-02-13 02:25:24 -05:00
,log AS (
INSERT INTO rlarp.osm_log(doc) SELECT $$replace_iterdef$$::jsonb doc RETURNING *
)
prevent div by zero
2019-03-21 16:50:51 -04:00
,final AS (
start on scale_vd
2020-02-13 02:25:24 -05:00
SELECT
fspr
,plnt ---master data
,promo --history date mix
,terms
,bill_cust_descr --history cust mix
,ship_cust_descr --history cust mix
,dsm
,quota_rep_descr --master data
,director
,billto_group --master data
,shipto_group
,chan --master data
,chansub
,chan_retail
,part
,part_descr
,part_group
,branding
,majg_descr
,ming_descr
,majs_descr
,mins_descr
,segm
add substance column and move some files out of the route folder that aren't actually being used by routes
2020-02-19 13:25:28 -05:00
,substance
start on scale_vd
2020-02-13 02:25:24 -05:00
,fs_line --master data
,r_currency --history cust mix
,r_rate --master data
,c_currency --master data
,c_rate --master data
update all routes to round to 2 decimals for units, value, and cost
2021-04-19 09:20:52 -04:00
,round(units*s.factor ,2) units
,round(value_loc*s.factor,2) value_loc
,round(value_usd*s.factor,2) value_usd
,round(cost_loc*s.factor ,2) cost_loc
,round(cost_usd*s.factor ,2) cost_usd
start on scale_vd
2020-02-13 02:25:24 -05:00
,calc_status --0
,flag --0
,order_date --history date mix
,order_month
,order_season
,request_date --history date mix
,request_month
,request_season
,ship_date --history date mix
,ship_month
,ship_season
,'replace_version' "version"
,'replace_source'||' volume' iter
,log.id
change comment to be the tag
2020-02-18 14:47:46 -05:00
,COALESCE(log.doc->>'tag','') "tag"
change module to be the comment
2020-02-18 15:35:56 -05:00
,log.doc->>'message' "comment"
the module is stored as key type in the json
2020-02-25 15:08:01 -05:00
,log.doc->>'type' module
prevent div by zero
2019-03-21 16:50:51 -04:00
FROM
basemix b
CROSS JOIN scale s
embed the module in the forecast pool instead of reading the json, cross join the newly inserted log file and id
2020-02-13 11:03:42 -05:00
CROSS JOIN log
prevent div by zero
2019-03-21 16:50:51 -04:00
)
--select sum(value_usd), count(*) from final
, ins AS (
start on scale_vd
2020-02-13 02:25:24 -05:00
INSERT INTO rlarp.osm_pool SELECT * FROM final RETURNING *
prevent div by zero
2019-03-21 16:50:51 -04:00
)
,insagg AS (
start on scale_vd
2020-02-13 02:25:24 -05:00
SELECT
---------customer info-----------------
bill_cust_descr
,billto_group
,ship_cust_descr
,shipto_group
,quota_rep_descr
,director
,segm
add substance column and move some files out of the route folder that aren't actually being used by routes
2020-02-19 13:25:28 -05:00
,substance
start on scale_vd
2020-02-13 02:25:24 -05:00
,chan
,chansub
---------product info------------------
,majg_descr
,ming_descr
,majs_descr
,mins_descr
--,brand
--,part_family
,part_group
,branding
--,color
,part_descr
---------dates-------------------------
,order_season
,order_month
,ship_season
,ship_month
,request_season
,request_month
,promo
,version
,iter
routes that make adjustments need to return comment info
2020-02-14 15:52:16 -05:00
,logid
change comment to be the tag
2020-02-18 14:47:46 -05:00
,tag
change module to be the comment
2020-02-18 15:35:56 -05:00
,comment
start on scale_vd
2020-02-13 02:25:24 -05:00
--------values-------------------------
,sum(value_loc) value_loc
,sum(value_usd) value_usd
,sum(cost_loc) cost_loc
,sum(cost_usd) cost_usd
,sum(units) units
FROM
ins
GROUP BY
---------customer info-----------------
bill_cust_descr
,billto_group
,ship_cust_descr
,shipto_group
,quota_rep_descr
,director
,segm
add substance column and move some files out of the route folder that aren't actually being used by routes
2020-02-19 13:25:28 -05:00
,substance
start on scale_vd
2020-02-13 02:25:24 -05:00
,chan
,chansub
---------product info------------------
,majg_descr
,ming_descr
,majs_descr
,mins_descr
--,brand
--,part_family
,part_group
,branding
--,color
,part_descr
---------dates-------------------------
,order_season
,order_month
,ship_season
,ship_month
,request_season
,request_month
,promo
,version
,iter
routes that make adjustments need to return comment info
2020-02-14 15:52:16 -05:00
,logid
change comment to be the tag
2020-02-18 14:47:46 -05:00
,tag
change module to be the comment
2020-02-18 15:35:56 -05:00
,comment
prevent div by zero
2019-03-21 16:50:51 -04:00
)
update all routes to round to 2 decimals for units, value, and cost
2021-04-19 09:20:52 -04:00
SELECT json_agg(row_to_json(insagg)) x from insagg
Reference in New Issue Copy Permalink