sr.customer_site_name, sr.customer_reference, sr.imdl_container , sr.shipment_id , sr.shipper_city , sr.shipper_state , sr.consignee_site_state , sr.consignee_site_city , sl.destramp_arrive_date , sr.delv_live_start , sr.delv_sched_to , sr.delv_spot_unload , sr.delv_spot_empty_ready , sr.equipment_terminated , datediff(sr.pick_spot_shipper_notified, sr.pick_spot_load) , , sr.dest_dray_site_name
from cs.shipment_reporting sr inner join cs.shipment_legs sl
where sr.imdl_container = 'xxx'
and sr.create_date < date_add(now(), interval - 30 day)
So what needs to be added to the where clause is sl.shipment_legs = but i cannot figure out the code to go through field shipment_legs and if total is 3 return the 2nd. It can have more than three. Stupid i can't but can anyone advise?