Scripts to get the statistics based on order type

Apr 172011

Scripts to get the statistics based on order type:

– Number of orders taken in each order type per month

select b.name,b.description,to_char(ordered_date,'Mon') Month, count(0) NoOfOrders
from oe_order_headers_all a,
     oe_transaction_types_tl b
where 1=1
and a.order_type_id = b.transaction_type_id
and ordered_Date between '01-SEP-2009' and '01-OCT-2009'
and org_id = 1
group by b.name, b.description,to_char(ordered_date,'Mon')
order by to_char(ordered_date,'Mon')

–Number of lines shipped in each order type per month

select b.name,b.description,to_char(schedule_ship_date,'Mon') Month, count(0) NoOfOrders
from oe_order_lines_all a,
     oe_transaction_types_tl b,
     oe_order_headers_all c
where 1=1
and c.order_type_id = b.transaction_type_id
and a.header_id = c.header_id
and a.org_id = c.org_id
and schedule_ship_date between '01-SEP-2009' and sysdate
and a.org_id = 1
group by b.name, b.description,to_char(schedule_ship_date,'Mon')