The SQL statements are for those people using a system call Navis Express, pretty old system, and struggle to get the data for their port equipment movement data such as quay crane (QC), empty handler (EH), and rubber tyred gantry (RTG).
For EH, for example, you want to retrieve the movement from 01 Oct 2016 – 01 Nov 2016:
SELECT gkey,
eq_nbr, tserv_id, from_che_id, from_che_opr, to_che_id, to_che_opr, from_pos_id,
to_pos_id,carry_started, performed, work_queue, pow_id,
carry_che_id
From
SERVICE_EVENTS
where
tserv_id in (‘LOAD’,’DISCHARGE’,’YARD2TRUCK’,’TRUCK2YARD’,’YARD MOVE’,’YARD
SHIFT’,’RHCDCOPER’,’RHCDCTERM’,’RHCDCLINE’,’RHCTCOPER’,’RHCTCTERM’,’RHCTCLINE’,’RAIL
LOAD’,’RAILUNLOAD’)
and
(from_che_id like ‘EH%’ or to_che_id like ‘CFS%’ or from_che_id like ‘CFS%’ or to_che_id like ‘EH%’)
and
((performed BETWEEN TO_DATE (‘2016/10/01’, ‘yyyy/mm/dd’)AND TO_DATE
(‘2016/11/01’, ‘yyyy/mm/dd’))
or
(carry_started BETWEEN TO_DATE (‘2016/10/01’, ‘yyyy/mm/dd’) AND TO_DATE
(‘2016/11/01’, ‘yyyy/mm/dd’)))
For RTG, for example, you want to retrieve the movement from 01 Oct 2016 – 01 Nov 2016:
SELECT gkey, eq_nbr, tserv_id, from_che_id,
from_che_opr, to_che_id, to_che_opr, from_pos_id, to_pos_id,carry_started,
performed, work_queue, pow_id, carry_che_id
From SERVICE_EVENTS
where tserv_id in
(‘LOAD’,’DISCHARGE’,’YARD2TRUCK’,’TRUCK2YARD’,’YARD MOVE’,’YARD
SHIFT’,’RHCDCOPER’,’RHCDCTERM’,’RHCDCLINE’,’RHCTCOPER’,’RHCTCTERM’,’RHCTCLINE’,’RAIL
LOAD’,’RAILUNLOAD’)
and (from_che_id like ‘R%’ or to_che_id like ‘R%’)
and ((performed BETWEEN TO_DATE (‘2016/10/01’,
‘yyyy/mm/dd’)
AND TO_DATE (‘2016/11/01’, ‘yyyy/mm/dd’)) or
(carry_started BETWEEN TO_DATE (‘2016/10/01’, ‘yyyy/mm/dd’) AND TO_DATE
(‘2016/11/01’, ‘yyyy/mm/dd’)))
For QC, for example, you want to retrieve the movement from 01 Oct 2016 – 01 Nov 2016:
select se.gkey, se.eq_nbr, se.tserv_id,
se.pow_id, se.ship_id, se.voy_nbr,
substr(ss.name,1,4) as vessel_type,
se.from_loc_type, se.from_pos_id, se.to_loc_type,
se.to_pos_id,
se.fetch_spcl, se.put_spcl,
se.performed,
se.carry_started,
svi.ves_ref, svi.ATA, svi.ATD,
svi.READY_TO_WORK
from
service_events se,
ship_voyages sv,
ship_visits svi,
scheduled_services ss,
equipment_uses eu
where (se.ship_id = sv.ship_id and se.voy_nbr =
sv.nbr)
and (se.ship_id = svi.ship_id and se.voy_nbr =
svi.in_voy_nbr)
and sv.service_id = ss.id
and se.equse_gkey = eu.gkey
and se.tserv_id in
(‘LOAD’,’DISCHARGE’,’RHCDCOPER’,’RHCDCTERM’,’RHCDCLINE’,’RHCTCOPER’,’RHCTCTERM’,’RHCTCLINE’)
and svi.ves_ref <>
‘9999999’
and se.pow_id is not null
and se.performed BETWEEN TO_DATE (‘2016/10/01 07:00:00
AM ‘, ‘yyyy/mm/dd HH:MI:SS AM’) AND TO_DATE (‘2016/11/01 09:00:00 AM’,
‘yyyy/mm/dd HH:MI:SS AM’)
