CREATE OR REPLACE VIEW {schema}.vw_detections AS 
SELECT d.detection_key, d.datelastmodified, d.date, d.code_space, d.id AS transmitter_id, 
d.sensor_1, d.units_1, d.sensor_2, d.units_2, d.transmitter_name, d.transmitter_sn, d.receiver_name, 
d.receiver_sn AS rcv_serial_no, l.consecutive_deploy_no, l.otn_array::text || l.station_no::text AS station_name, 
l.dep_lat AS latitude, l.dep_long AS longitude, l.receiver_depth AS depth, 
st_makepoint(l.dep_long, l.dep_lat) AS the_geom
FROM {schema}.detections d LEFT JOIN
  ( SELECT vw_receivers.rcv_serial_no, vw_receivers.otn_array, vw_receivers.station_no, vw_receivers.consecutive_deploy_no,      
    vw_receivers.dep_lat, vw_receivers.dep_long, vw_receivers.deploy_date, vw_receivers.receiver_depth, vw_receivers.recover_date_time
    FROM {schema}.vw_receivers) l 
ON d.receiver_sn::integer = l.rcv_serial_no::integer AND 
(f_date_num(d.date) >= f_date_num(l.deploy_date) AND f_date_num(d.date) < f_date_num(l.recover_date_time) 
 OR f_date_num(d.date) >= f_date_num(l.deploy_date) AND l.recover_date_time IS NULL);
