maya.public

Parameters

Name	Type	Mode
_initdate	timestamp with time zone	IN
_enddate	timestamp with time zone	IN
date_key	integer	OUT
serialnumber	character varying	OUT
idunit	integer	OUT
idclient	integer	OUT
iddriver	integer	OUT
gps_distance	double precision	OUT
gps_maxspeed	double precision	OUT
ems_distance	double precision	OUT
ems_consumedfuel	double precision	OUT
ems_idlehours	double precision	OUT
ems_enginehours	double precision	OUT
ems_movinghours	double precision	OUT
total_harshbreaks	integer	OUT
ems_speedrpmmatrix	character varying	OUT
gps_moving_time	interval	OUT
gps_idling_time	interval	OUT
gps_stop_time	interval	OUT
gps_idling_novms	interval	OUT

Definition

WITH q AS
( SELECT e.*,
CASE WHEN e.ems_speedrpmmatrix = m.ems_speedrpmmatrix AND e.serialnumber = m.serialnumber THEN null ELSE e.ems_speedrpmmatrix END ems_speedrpmmatrix_unique,
(lead(e.senddate,1) over (PARTITION BY e.serialnumber ORDER BY e.senddate) - e.senddate) as duration 
FROM public.vw_dw_event_ecm e LEFT JOIN (SELECT DISTINCT on (serialnumber) serialnumber,ems_speedrpmmatrix FROM vw_dw_event_ecm e WHERE e.senddate between (_initdate - '1 day'::interval) AND _initdate ORDER BY serialnumber,senddate desc) as m ON m.serialnumber = e.serialnumber  WHERE e.senddate between _initdate AND _enddate),
q1 AS
(SELECT
(extract(year from (senddate at time zone 'America/Mexico_city')::date)*10000 +
extract(month from (senddate at time zone 'America/Mexico_city')::date)*100 + extract(day from (senddate at time zone 'America/Mexico_city')::date))::integer as date_key,
serialnumber,
idunit,
idclient,
iddriver,
max(odometer) - min(odometer) gps_distance,
(max(case when speed > 200 then null else speed end ))::float gps_maxspeed,
((max(ems_odometer) - min(ems_odometer))::float) ems_distance,
(max(ems_totalfuel) - min(ems_totalfuel))::float ems_consumedfuel,
(max(ems_totalidlehours) - min(ems_totalidlehours))::float ems_idlehours,
(max(ems_totalenginehours) - min(ems_totalenginehours))::float ems_enginehours,
coalesce((max(ems_totalenginehours) - min(ems_totalenginehours))::float - (max(ems_totalidlehours) - min(ems_totalidlehours))::float,(max(ems_totalenginehours) - min(ems_totalenginehours))::float - extract(epoch from sum(case idling when true then duration else null end))/(60*60.0)) ems_movinghours,
(count(harsh_break))::int total_harshbreaks,
(ot_ro_aggaddarray(distinct ot_ro_arraydiv(ems_speedrpmmatrix_unique,3600.0)))::varchar ems_speedrpmmatrix, /* comes in seconds,transforming to hours */
sum(case vms when 1 then duration else null end) gps_moving_time,
sum(case vms when 2 then duration else null end) gps_idling_time,
sum(case vms when 3 then duration else null end) gps_stop_time,
sum(case idling when true then duration else null end) gps_idling_novms
FROM q WHERE
/* filter out records that don't report EMS data */
(ems_odometer > 0 OR ems_totalfuel >0 OR ems_totalenginehours > 0 OR ems_totalidlehours >0 OR ems_speedrpmmatrix is not null)
GROUP BY (senddate at time zone 'America/Mexico_city')::date,serialnumber,idunit,idclient,iddriver)
SELECT date_key,q1.serialnumber,idunit,idclient,iddriver,gps_distance,gps_maxspeed,ems_distance,ems_consumedfuel,ems_idlehours,coalesce(ems_enginehours,extract(epoch from gps_moving_time)/3600.0 + extract(epoch from gps_idling_time)/3600.0) ems_enginehours,coalesce(ems_movinghours,extract(epoch from gps_moving_time)/3600.0) ems_movinghours,total_harshbreaks,q1.ems_speedrpmmatrix::real[]::varchar,gps_moving_time,gps_idling_time,gps_stop_time,gps_idling_novms
FROM q1;

dw_ems_ecm(timestamp with time zone, timestamp with time zone)

Parameters

Definition