maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_ro_no_coverage(timestamp with time zone, timestamp with time zone)
Parameters
Name
Type
Mode
start_date
timestamp with time zone
IN
end_date
timestamp with time zone
IN
Definition
select e.* from event e JOIN (select * from (select iddevice,idevent,lead(idevent,1) over(partition by iddevice order by iddevice,idevent) next_event,reason, lead(reason,1) over(partition by iddevice order by iddevice,idevent) next_reason FROM (select e.idevent,e.iddevice,e.creationdate,e.senddate,e.longitude,e.latitude,er.description reason FROM event e JOIN device d ON d.iddevice = e.iddevice JOIN catalogs.event_reason er ON er.id = e.reason WHERE e.senddate BETWEEN $1 AND $2 AND er.description IN ('IN_COVERAGE','NO_COVERAGE') order by e.iddevice,e.idevent) as q) as q1 where next_event is not null and (reason = 'NO_COVERAGE' and next_reason = 'IN_COVERAGE')) as q2 ON (e.idevent between q2.idevent and q2.next_event and q2.iddevice = e.iddevice and e.senddate between $1 and $2) order by 2;