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