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.*,(lead(e.senddate,1) over (PARTITION BY e.serialnumber ORDER BY e.senddate) - e.senddate) as duration 
FROM public.vw_dw_event_ecm e WHERE e.senddate between _initdate AND _enddate)
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,
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,
(max(ems_totalenginehours) - min(ems_totalenginehours))::float - (max(ems_totalidlehours) - min(ems_totalidlehours))::float ems_movinghours,
(count(harsh_break))::int total_harshbreaks,
(ot_ro_aggaddarray(distinct ot_ro_arraydiv(ems_speedrpmmatrix,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;

dw_ems_event_ecm(timestamp with time zone, timestamp with time zone)

Parameters

Definition