CREATE MATERIALIZED VIEW rlarp.cust_review_basis AS ( WITH act as ( SELECT bill_dba ,ship_dba ,dsm ,COALESCE(sum(sales_usd) filter (WHERE sseas = 2023 AND version = 'Actual'),0) sales23 ,COALESCE(sum(sales_usd) filter (WHERE sseas = 2024 AND version = 'Actual'),0) sales24 ,COALESCE(sum(sales_usd) filter (WHERE version = 'Actual' AND ostatus LIKE 'Open%' AND rseas <= 2024),0) salesop ,COALESCE(sum(pounds) filter (WHERE sseas = 2023 AND version = 'Actual'),0) pounds23 ,COALESCE(sum(pounds) filter (WHERE sseas = 2024 AND version = 'Actual'),0) pounds24 ,COALESCE(sum(pounds) filter (WHERE version = 'Actual' AND ostatus LIKE 'Open%' AND rseas <= 2024),0) poundsop ,COALESCE(sum(qty) filter (WHERE sseas = 2023 AND version = 'Actual'),0) qty23 ,COALESCE(sum(qty) filter (WHERE sseas = 2024 AND version = 'Actual'),0) qty24 ,COALESCE(sum(qty) filter (WHERE version = 'Actual' AND ostatus LIKE 'Open%' AND rseas <= 2024),0) qtyop ,COALESCE(sum(pallets) filter (WHERE sseas = 2023 AND version = 'Actual'),0) pallets23 ,COALESCE(sum(pallets) filter (WHERE sseas = 2024 AND version = 'Actual'),0) pallets24 ,COALESCE(sum(pallets) filter (WHERE version = 'Actual' AND ostatus LIKE 'Open%' AND rseas <= 2024),0) palletsop FROM rlarp.osm_stack os LEFT OUTER JOIN rlarp.molds m ON m.stlc = substring(os.product ,1,8) WHERE ( ( sseas IN (2023,2024) AND version = 'Actual' AND ostatus = 'Shipped' ) OR (ostatus LIKE 'Open%') ) AND calc_status <> 'CANCELED' AND substring(os.glec,1,1) <= '2' AND os.fs_line = '41010' AND COALESCE(ship_dba,'') <> '' GROUP BY bill_dba ,ship_dba ,dsm --,os.glec --,m.biggroup --,os.priceg ) ,bgt AS ( SELECT bill_dba ,ship_dba ,dsm ,sum(sales_usd) sales_usd ,sum(pounds) pounds ,sum(qty) qty ,sum(pallets) pallets --string_agg(distinct mold,', ') FROM rlarp.osm_stack WHERE version = 'Budget' AND oseas = '2024' GROUP BY bill_dba ,ship_dba ,dsm ) ,agg AS ( SELECT COALESCE(act.bill_dba,TRIM(bgt.bill_dba)) bill_dba ,COALESCE(act.ship_dba,TRIM(bgt.ship_dba)) ship_dba ,COALESCE(act.dsm ,TRIM(bgt.dsm )) dsm ,COALESCE(SUM(bgt.sales_usd),0) salesbg ,COALESCE(SUM(act.sales23),0) sales23 ,COALESCE(SUM(act.sales24),0) sales24 ,COALESCE(SUM(act.salesop),0) salesop ,COALESCE(SUM(bgt.pounds),0) poundsbg ,COALESCE(SUM(act.pounds23),0) pounds23 ,COALESCE(SUM(act.pounds24),0) pounds24 ,COALESCE(SUM(act.poundsop),0) poundsop ,COALESCE(SUM(bgt.qty),0) qtybg ,COALESCE(SUM(act.qty23),0) qty23 ,COALESCE(SUM(act.qty24),0) qty24 ,COALESCE(SUM(act.qtyop),0) qtyop ,COALESCE(SUM(bgt.pallets),0) palletsbg ,COALESCE(SUM(act.pallets23),0) pallets23 ,COALESCE(SUM(act.pallets24),0) pallets24 ,COALESCE(SUM(act.palletsop),0) palletsop --,COALESCE(SUM(act."Quotes" ),0) "Quotes" FROM act FULL OUTER JOIN bgt ON TRIM(bgt.bill_dba) = act.bill_dba AND TRIM(bgt.ship_dba) = act.ship_dba AND TRIM(bgt.dsm ) = act.dsm GROUP BY COALESCE(act.bill_dba,TRIM(bgt.bill_dba)) ,COALESCE(act.ship_dba,TRIM(bgt.ship_dba)) ,COALESCE(act.dsm ,TRIM(bgt.dsm )) ) SELECT * FROM agg ); ------------------------------------------sales walk------------------------------------------------------- DROP VIEW rlarp.cust_infered_forecast; CREATE VIEW rlarp.cust_infered_forecast AS ( WITH ask AS ( SELECT agg.bill_dba ,agg.ship_dba ,agg.dsm ,agg.salesbg ,agg.sales23 ,agg.sales24 ,agg.salesop ,agg.poundsbg ,agg.pounds23 ,agg.pounds24 ,agg.poundsop ,agg.qtybg ,agg.qty23 ,agg.qty24 ,agg.qtyop ,agg.palletsbg ,agg.pallets23 ,agg.pallets24 ,agg.palletsop ,CASE WHEN COALESCE((agg.pounds24 + agg.poundsop),0) > 0 THEN round((agg.sales24 + agg.salesop)/(agg.pounds24 + agg.poundsop),3) ELSE null --ELSE CASE WHEN (agg.pounds23) > 0 THEN round((agg.sales23)/(agg.pounds23),3) --ELSE CASE WHEN (agg.poundsbg) > 0 THEN round((agg.salesbg)/(agg.poundsbg),3) ELSE null END END basis_ppp ,CASE WHEN COALESCE((agg.qty24 + agg.qtyop),0) > 0 THEN round((agg.sales24 + agg.salesop)/(agg.qty24 + agg.qtyop),3) ELSE null --ELSE CASE WHEN (agg.pounds23) > 0 THEN round((agg.sales23)/(agg.pounds23),3) --ELSE CASE WHEN (agg.poundsbg) > 0 THEN round((agg.salesbg)/(agg.poundsbg),3) ELSE null END END basis_ppu ,CASE WHEN COALESCE((agg.pallets24 + agg.palletsop),0) > 0 THEN round((agg.sales24 + agg.salesop)/(agg.pallets24 + agg.palletsop),3) ELSE null --ELSE CASE WHEN (agg.pounds23) > 0 THEN round((agg.sales23)/(agg.pounds23),3) --ELSE CASE WHEN (agg.poundsbg) > 0 THEN round((agg.salesbg)/(agg.poundsbg),3) ELSE null END END basis_ppl --,"Quotes" quotes ,COALESCE(a.newords,0) newords ,COALESCE(a.newuom,'Units') newuom --,COUNT(*) OVER (PARTITION BY bill_dba, ship_dba) rep_count --,sum(COALESCE((agg.pounds24 + agg.poundsop),0)) OVER (PARTITION BY bill_dba, ship_dba)/COALESCE((agg.pounds24 + agg.poundsop),0) rep_count ,ROUND(CASE WHEN sum(COALESCE((agg.pounds24 + agg.poundsop),0)) OVER (PARTITION BY bill_dba, ship_dba) = 0 THEN 1::numeric/COUNT(*) OVER (PARTITION BY bill_dba, ship_dba)::numeric ELSE COALESCE((agg.pounds24 + agg.poundsop),0) /sum(COALESCE((agg.pounds24 + agg.poundsop),0)) OVER (PARTITION BY bill_dba, ship_dba) END,5) rep_count FROM rlarp.cust_review_basis agg LEFT OUTER JOIN rlarp.customer_review a ON a.ship_cust = agg.ship_dba AND a.bill_cust = agg.bill_dba ) ,infer AS ( SELECT a.bill_dba ,a.ship_dba ,a.dsm ,a.salesbg ,a.sales23 ,a.sales24 ,a.salesop ,a.poundsbg ,a.pounds23 ,a.pounds24 ,a.poundsop ,a.qtybg ,a.qty23 ,a.qty24 ,a.qtyop ,a.palletsbg ,a.pallets23 ,a.pallets24 ,a.palletsop ,a.basis_ppp ,a.basis_ppu ,a.basis_ppl ,a.newords ,a.newuom ,a.rep_count ,round(CASE a.newuom WHEN 'Pallets' THEN a.newords * a.basis_ppl WHEN 'Units' THEN a.newords * a.basis_ppu WHEN 'Dollars' THEN a.newords END,2)*a.rep_count newdollars ,ROUND(CASE WHEN COALESCE(a.basis_ppp,0) = 0 THEN NULL ELSE CASE a.newuom WHEN 'Pallets' THEN (a.newords * a.basis_ppl) / a.basis_ppp WHEN 'Units' THEN (a.newords * a.basis_ppu) / a.basis_ppp WHEN 'Dollars' THEN a.newords / a.basis_ppp END END,2)*a.rep_count newpounds FROM ask a ) ,limited AS ( SELECT a.bill_dba ,a.ship_dba ,a.dsm ,a.salesbg ,a.sales23 ,a.sales24 ,a.salesop ,a.basis_ppp ,a.basis_ppu ,a.basis_ppl ,a.rep_count ,a.newords ,a.newuom ,a.newdollars ,a.newpounds FROM infer a ) ,alldata AS ( SELECT a.bill_dba ,a.ship_dba ,a.dsm ,a.salesbg ,a.sales23 ,a.sales24 ,a.salesop ,a.poundsbg ,a.pounds23 ,a.pounds24 ,a.poundsop ,a.qtybg ,a.qty23 ,a.qty24 ,a.qtyop ,a.palletsbg ,a.pallets23 ,a.pallets24 ,a.palletsop ,a.basis_ppp ,a.basis_ppu ,a.basis_ppl ,a.newords ,a.newuom ,a.newdollars ,a.newpounds ,a.newdollars + sales24 + salesop fcsales24 ,a.newpounds + pounds24 + poundsop fcpounds24 ,a.rep_count FROM infer a ) SELECT * FROM alldata )