CREATE INDEX CONCURRENTLY coitem_scheddate_index
  ON coitem
  USING btree
  (coitem_scheddate);
  
CREATE INDEX CONCURRENTLY wooper_wo_id_index
  ON wooper
  USING btree
  (wooper_wo_id);


Select cohead_number, coitem_linenumber, round(coitem_qtyord, 0) as coitem_qtyord, coitem_order_id, to_char(coitem_scheddate, 'Month Dy DD YYYY') as coitem_scheddate, cust_name, cust_number, 
item_number, cast(wo_number as text) ||'-' || cast(wo_subnumber as text) as wo_number, 
 case 
	when (select max(wooper_seqnumber) from wooper where wooper_rncomplete = true and wooper_wo_id = coitem_order_id) IS NULL 
		then 'No Operation Completed'
	when (select max(wooper_seqnumber) from wooper where wooper_rncomplete = true and wooper_wo_id = coitem_order_id) IS NOT NULL 
		then 
		(select wrkcnt_code || ' Operation Completed :' || cast(wooper_seqnumber as text) from wooper, wrkcnt 
		where wooper_seqnumber =(select max(wooper_seqnumber) from wooper where wooper_rncomplete = true and wooper_wo_id = coitem_order_id)
		and wooper_wo_id = coitem_order_id and wrkcnt_id = wooper_wrkcnt_id )
	end as LastFinshedOp
from coitem left join wo on wo_id = coitem.coitem_order_id, cohead, custinfo, itemsite, item 

where cohead_id = coitem_cohead_id and coitem_itemsite_id = itemsite_id and itemsite_item_id = item_id 
and cohead_cust_id = cust_id and coitem_status = 'O' and coitem_scheddate between 
'2008-01-01' and '2008-12-31'
order by coitem_scheddate, cohead_number, coitem_linenumber; 

drop index coitem_scheddate_index;
drop index wooper_wo_id_index;