maya.public

Parameters

Name	Type	Mode
in_date	date	IN
iddevice	integer	OUT
serialnumber	character varying	OUT
client	character varying	OUT
creationdate	timestamp with time zone	OUT

Definition

SELECT distinct ON (iddevice) iddevice,serialnumber,client,creationdate FROM 
(SELECT e.idevent,e.iddevice,d.serialnumber,c.name client,e.creationdate,e.senddate,e.reason,e.ignition_on,lead(e.creationdate,1) over w as next_cdate,lead(e.reason,1) over w as next_reason 
FROM event e JOIN device d ON d.iddevice = e.iddevice
JOIN technicalaccount t ON t.idtechnicalaccount = d.idtechnicalaccount JOIN client c ON c.idclient = t.idclient
WHERE  e.senddate between in_date::timestamptz and (in_date::timestamptz + '1 day'::interval)::timestamptz
 WINDOW w AS (partition by e.iddevice order by e.creationdate) order by e.idevent,e.creationdate) as q 
WHERE (extract(epoch from (next_cdate - creationdate )) / 60) > 150 and reason = 6 and next_reason = 6
AND NOT EXISTS (SELECT 1 FROM event WHERE senddate BETWEEN (q.senddate::timestamptz - '15 minutes'::interval)::timestamptz and q.senddate::timestamptz AND iddevice = q.iddevice 
AND reason = 42 and senddate between (in_date::timestamptz - '5 hours'::interval)::timestamptz and (in_date::timestamptz + '1 day'::interval)::timestamptz) and ignition_on = 'true' ;

ot_ro_baddelay(date)

Parameters

Definition