This content has been marked as final. Show 3 replies
To summarize by product category, then by state_desc :
To summarize by state_desc, just remove "product category," from the query above.
WITH p AS ( Select distinct partner, region, od.ORDER_AFFILIATE_NAME, oh.external_order_number, ain.get_g1(queue_type) offer, Case When disp.category = 100 Then'ORDER' When disp.category = 200 Then'ACCESS' When disp.category = 300 Then'DSL' When disp.category = 600 Then'IPTV' When disp.category = 700 Then'INTERNET' When disp.category = 800 Then'VOIP' End as product_category, TO_CHAR(oh.created_ts, 'yyyy-mm-dd hh24:mi:ss') CREATE_DATE, Case when ip.offer_type = 'IPDSLAM' or instr(ip.display_name, '- D')> 0 THEN 'IP-DSL' ELSE 'FTTN' end as product_type, Case When disp.state = 4 or disp.state = 9 or disp.state= 21 or disp.state= 22 Then 'Canceled' When disp.state = 17 Then 'Canceled NRFC' When disp.state = 7 Then 'Incomplete' When disp.state = 3 or disp.state = 8 Then 'Complete' When disp.state = 6 or disp.state = 10 or disp.state = 13 or disp.state =14 Then 'Pending - Other' When disp.state = 19 or disp.state = 20 Then 'Submitted' when disp.state = 21 or disp.state = 22 then 'Cancelled- CSUS' when disp.state =23 or disp.state = 24 then 'Order Confirmation' when disp.state =1 or disp.state = 2 or disp.state = 15 then 'Received' Else 'Other' End as STATE_desc from AIN.impl_oh_order_header oh, ain.impl_order_data od, AIN.sncr_order_curr_disp disp , AIN.impl_package ip where oh.created_ts between to_date('2013-02-04', 'yyyy-mm-dd') and to_date('2013-02-11', 'yyyy-mm-dd') and oh.order_type = 'ORDER' and disp.category in (200, 300, 600, 700, 800) and disp.state !='5' and uscs= 0 and disp.transaction_id = oh.transaction_id and od. transaction_id = oh.transaction_id and ip.transaction_id = oh.transaction_id and ip.package_type = 'PACKAGE' and ip.parent_package_id is null and ip.product_type = 'ORDER' ) SELECT product_category, STATE_desc, COUNT(*) FROM p GROUP BY product_category, STATE_desc
My fault just found out I need all the columns as part of the group by , including : Case when ip.offer_type = 'IPDSLAM' or instr(ip.display_name, '- D')> 0
end as product_type,
Thank you for all of your help it has been perfect, can you help me with the above too please????