spool fix4.log

Prompt Fixing Missing records in COSTS vis-a-vis SALES

INSERT into COSTS 
SELECT b.prod_id, b.time_id, b.promo_id, b.channel_id,
round( (sum (b.amount_sold)) / (sum (b.quantity_sold)),2) * .4, 
round( (sum (b.amount_sold)) / (sum (b.quantity_sold)),2) 
FROM 
(SELECT a.prod_id, a.time_id, a.promo_id, a.channel_id, 
a.quantity_sold, a.amount_sold FROM SALES a
WHERE (a.prod_id, a.time_id, a.promo_id, a.channel_id) NOT IN 
(SELECT distinct c.prod_id, c.time_id, c.promo_id, c.channel_id
FROM COSTS c) ) b
GROUP BY 
b.prod_id, b.time_id, b.promo_id, b.channel_id;

commit;
exit




