120 lines
3.2 KiB
MySQL
120 lines
3.2 KiB
MySQL
|
DELETE FROM fc.dcard WHERE app_version = 'app_forecast_name';
|
||
|
|
WITH
|
||
|
|
req AS (SELECT $$app_req$$::jsonb j)
|
||
|
|
,ilog AS (
|
||
|
|
INSERT INTO
|
||
|
|
fc.log (doc)
|
||
|
|
SELECT
|
||
|
|
req.j
|
||
|
|
FROM
|
||
|
|
req
|
||
|
|
RETURNING *
|
||
|
|
)
|
||
|
|
,baseline AS (
|
||
|
|
SELECT
|
||
|
|
null::integer AS id
|
||
|
|
,null::integer AS logid
|
||
|
|
,o."Trans. Date"
|
||
|
|
,o."Post Date"
|
||
|
|
,o."Description"
|
||
|
|
,sum(o."Amount") AS "Amount"
|
||
|
|
,o."Category"
|
||
|
|
,o."Party"
|
||
|
|
,o."Reason"
|
||
|
|
,sum(o.app_units) AS app_units
|
||
|
|
,'app_forecast_name' AS app_version
|
||
|
|
,'baseline' AS app_iter
|
||
|
|
,(SELECT id FROM ilog) AS app_logid
|
||
|
|
FROM
|
||
|
|
fc.dcard o
|
||
|
|
WHERE
|
||
|
|
(
|
||
|
|
--base period orders booked....
|
||
|
|
"Trans. Date" BETWEEN 'app_baseline_from_date'::date AND 'app_baseline_to_date'::date
|
||
|
|
--...or any open orders currently booked before cutoff....
|
||
|
|
OR ("Description" IN (app_openstatus_code) and "Trans. Date" <= 'app_openorder_cutoff'::date)
|
||
|
|
--...or anything that shipped in that period
|
||
|
|
OR ("Post Date" BETWEEN 'app_baseline_from_date'::date AND 'app_baseline_to_date'::date)
|
||
|
|
)
|
||
|
|
--be sure to pre-exclude unwanted items, like canceled orders, non-gross sales, and short-ships
|
||
|
|
GROUP BY
|
||
|
|
o."Trans. Date"
|
||
|
|
,o."Post Date"
|
||
|
|
,o."Description"
|
||
|
|
,o."Category"
|
||
|
|
,o."Party"
|
||
|
|
,o."Reason"
|
||
|
|
UNION ALL
|
||
|
|
SELECT
|
||
|
|
null::integer AS id
|
||
|
|
,null::integer AS logid
|
||
|
|
,o."Trans. Date" + interval '1 year' AS "Trans. Date"
|
||
|
|
,o."Post Date" + interval '1 year' AS "Post Date"
|
||
|
|
,o."Description"
|
||
|
|
,sum(o."Amount") AS "Amount"
|
||
|
|
,o."Category"
|
||
|
|
,o."Party"
|
||
|
|
,o."Reason"
|
||
|
|
,sum(o.app_units) AS app_units
|
||
|
|
,'app_forecast_name' AS app_version
|
||
|
|
,'baseline' AS app_iter
|
||
|
|
,(SELECT id FROM ilog) AS app_logid
|
||
|
|
FROM
|
||
|
|
fc.dcard o
|
||
|
|
LEFT OUTER JOIN fc.perd tdate ON
|
||
|
|
(o."Trans. Date" + interval '1 year' )::date <@ tdate.drange
|
||
|
|
LEFT OUTER JOIN fc.perd pdate ON
|
||
|
|
(o."Post Date" + interval '1 year' )::date <@ pdate.drange
|
||
|
|
WHERE
|
||
|
|
"Trans. Date" BETWEEN 'app_plug_fromdate'::date AND 'app_plug_todate'::date
|
||
|
|
--be sure to pre-exclude unwanted items, like canceled orders, non-gross sales, and short-ships
|
||
|
|
GROUP BY
|
||
|
|
o."Trans. Date"
|
||
|
|
,o."Post Date"
|
||
|
|
,o."Description"
|
||
|
|
,o."Category"
|
||
|
|
,o."Party"
|
||
|
|
,o."Reason")
|
||
|
|
,incr AS (
|
||
|
|
SELECT
|
||
|
|
null::integer AS id
|
||
|
|
,null::integer AS logid
|
||
|
|
,o."Trans. Date" + interval '1 year' AS "Trans. Date"
|
||
|
|
,o."Post Date" + interval '1 year' AS "Post Date"
|
||
|
|
,o."Description"
|
||
|
|
,sum(o."Amount") AS "Amount"
|
||
|
|
,o."Category"
|
||
|
|
,o."Party"
|
||
|
|
,o."Reason"
|
||
|
|
,sum(o.app_units) AS app_units
|
||
|
|
,'app_forecast_name' AS app_version
|
||
|
|
,'baseline' AS app_iter
|
||
|
|
,(SELECT id FROM ilog) AS app_logid
|
||
|
|
FROM
|
||
|
|
baseline o
|
||
|
|
LEFT OUTER JOIN fc.perd tdate ON
|
||
|
|
(o."Trans. Date" + interval '1 year' )::date <@ tdate.drange
|
||
|
|
LEFT OUTER JOIN fc.perd pdate ON
|
||
|
|
(o."Post Date" + interval '1 year' )::date <@ pdate.drange
|
||
|
|
GROUP BY
|
||
|
|
o."Trans. Date"
|
||
|
|
,o."Post Date"
|
||
|
|
,o."Description"
|
||
|
|
,o."Category"
|
||
|
|
,o."Party"
|
||
|
|
,o."Reason"
|
||
|
|
)
|
||
|
|
,ins AS (
|
||
|
|
INSERT INTO
|
||
|
|
fc.dcard
|
||
|
|
SELECT
|
||
|
|
*
|
||
|
|
FROM
|
||
|
|
incr i
|
||
|
|
WHERE
|
||
|
|
i."Trans. Date" >= 'app_first_forecast_date'::date
|
||
|
|
OR i."Post Date" >= 'app_first_forecast_date'::date
|
||
|
|
RETURNING *
|
||
|
|
)
|
||
|
|
SELECT COUNT(*) num_rows FROM ins
|