maya.public

Parameters

Name	Type	Mode
startdate	timestamp with time zone	IN
enddate	timestamp with time zone	IN
serialnumber	character varying	OUT
d	date	OUT
j1939_hiresodometer	double precision	OUT
j1939_odometer	double precision	OUT
j1708_odometer	double precision	OUT
gps_odometer	double precision	OUT
j1939_totalfuel	double precision	OUT
j1708_totalfuel	double precision	OUT
j1939_totalenginehours	double precision	OUT
j1708_totalenginehours	double precision	OUT
j1939_totalidlehours	double precision	OUT
j1708_totalidlehours	double precision	OUT
gps_movingtime	double precision	OUT
gps_idlingtime	double precision	OUT
gps_stoppedtime	double precision	OUT

Definition

WITH q AS (SELECT * FROM vw_dw_event where senddate between $1 and $2), q1 as (SELECT *,(lead(senddate,1) over (partition by serialnumber order by senddate) - senddate)::interval as event_duration FROM q)
 select serialnumber,cast((senddate at time zone 'America/Mexico_city') as date) d,
 max(j1939_hiresodometer) - min(nullif(j1939_hiresodometer,0)) as j1939_hiresodometer, 
 max(j1939_odometer) - min(nullif(j1939_odometer,0)) as j1939_odometer,
 max(j1708_odometer) - min(nullif(j1708_odometer,0)) as j1708_odometer,
 max(odometer) - min(nullif(odometer,0)) as gps_odometer,
 max(j1939_totalfuel) - min(nullif(j1939_totalfuel,0)) as j1939_totalfuel,
 max(j1708_totalfuel) - min(nullif(j1708_totalfuel,0)) as j1708_totalfuel,
 max(j1939_totalenginehours) - min(nullif(j1939_totalenginehours,0)) as j1939_totalenginehours,
 max(j1708_totalenginehours) - min(nullif(j1708_totalenginehours,0)) as j1708_totalenginehours,
 max(j1939_totalidlehours) - min(nullif(j1939_totalidlehours,0)) as j1939_totalidlehours,
 max(j1708_totalidlehours) - min(nullif(j1708_totalidlehours,0)) as j1708_totalidlehours,
 (sum(case when vms = 1 then extract(epoch from event_duration) end))/3600.0 gps_movingtime,
 (sum(case when vms = 2 then extract(epoch from event_duration) end))/3600.0 gps_idlingtime,
 (sum(case when vms = 3 then extract(epoch from event_duration) end))/3600.0 gps_stoppedtime
 FROM q1 GROUP BY 1,2;

ot_ro_dw_daily(timestamp with time zone, timestamp with time zone)

Parameters

Definition