1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
| SELECT tc.card_number ,
tc.vehicle_id,
vehicles.vehicle_numb,
TO_CHAR(realtimes.datetime AT TIME ZONE 'GMT','HH24-MI-SS-MM-DD-YYYY'),
realtimes.session_number,
realtimes.sequence_number,
realtimes.event_type_id,
realtimes.sensor_idling_time_control,
realtimes.sensor_aux,
realtimes.sensor_brake,
realtimes.sensor_button,
realtimes.sensor_key,
realtimes.sensor_aux2,
realtimes.sensor_aux3,
realtimes.sensor_aux4,
realtimes.signal,
realtimes.latitude_wgs84,
realtimes.longitude_wgs84,
realtimes.heading,
realtimes.speed,
realtimes.distance,
realtimes.sensor_aux2_temp,
realtimes.sensor_aux3_temp,
realtimes.sensor_aux4_temp,
realtimes.aux2_temp,
realtimes.aux3_temp,
realtimes.aux4_temp,
mappoint_address.street_address,
mappoint_address.city,
mappoint_address.province,
mappoint_address.country,
configurations.aux_2_temp_act,
configurations.aux_3_temp_act,
configurations.aux_4_temp_act,
configurations.obd2_rpm,
configurations.obd2_temperature,
configurations.obd2_gas,
configurations.rs232_active,
realtimes.temp_obd2,
realtimes.gas_consumption,
realtimes.rpm,
realtimes.rs232_temperature1,
realtimes.rs232_temperature2,
realtimes.rs232_temperature3,
realtimes.rs232_temperature4,
session_driver.driver_name,
realtimes.accel_force
FROM tc50.tc
JOIN core.vehicles ON vehicles.vehicle_id = tc.vehicle_id
JOIN tc50.realtimes ON realtimes.vehicle_id = tc.vehicle_id
JOIN tc50.configurations ON vehicles.vehicle_id = configurations.vehicle_id
LEFT OUTER JOIN tc50.session_driver ON vehicles.vehicle_id = session_driver.vehicle_id AND realtimes.session_number = session_driver.session_id
LEFT OUTER JOIN address.mappoint_address
ON realtimes.address_id = mappoint_address.address_id
AND mappoint_address.is_correct = true
AND realtimes.datetime >= '2012-09-20 0:0:0'
AND realtimes.datetime <= '2012-09-22 0:0:0'
AND branch_id = 502
ORDER BY vehicles.vehicle_numb, realtimes.datetime, realtimes.sequence_number
limit 3; |
Partager