maya.public

Parameters

Name	Type	Mode
in_date	date	IN
serial_number_c28	character varying	OUT
client	character varying	OUT
creationdate	timestamp with time zone	OUT

Definition

SELECT distinct ON (serialnumber) serialnumber,client,receive_dt::timestamptz FROM
(SELECT e.id,d.serialnumber,c.name client,e.receive_dt,e.gmt_time_c2,e.reason_c9,e.ignition_c11,lead(e.receive_dt,1) over w as next_cdate,lead(e.reason_c9,1) over w as next_reason
FROM staging.gnx_report_1104_1106 e JOIN device d ON d.serialnumber = e.serial_number_c28
JOIN technicalaccount t ON t.idtechnicalaccount = d.idtechnicalaccount JOIN client c ON c.idclient = t.idclient
WHERE  e.receive_dt between in_date::timestamptz and (in_date::timestamptz + '1 day'::interval)::timestamptz
 WINDOW w AS (partition by e.serial_number_c28 order by e.receive_dt) order by e.id,e.receive_dt) as q
WHERE (extract(epoch from (next_cdate - receive_dt )) / 60) > 150 and reason_c9 = 'PERIODIC' and next_reason = 'PERIODIC'
AND NOT EXISTS (SELECT 1 FROM staging.gnx_report_1104_1106 WHERE gmt_time_c2 BETWEEN (q.gmt_time_c2::timestamptz - '15 minutes'::interval)::timestamptz and q.gmt_time_c2::timestamptz AND serial_number_c28 = q.serialnumber
AND reason_c9 = 'NO_COVERAGE' and gmt_time_c2 between (in_date::timestamptz - '5 hours'::interval)::timestamptz and (in_date::timestamptz + '1 day'::interval)::timestamptz) and ignition_c11 = 'ON' ;

ot_ro_baddelay1(date)

Parameters

Definition