Most Excellent Queries

Reporting

Query/Export Dispatch Reports Examples with DIP_TICKETHISTORY - Start Here!

GPS

GPTSTATUS

select * from gptstatus
where truck_no='74' order by seq_no desc
rows 1 to 100

GPS with description:

select gps.truck_no , status_datetime,
case status_col
when 0 then 'Unknown'
when 1 then 'Beg Load'
when 2 then 'End Load'
when 3 then 'To Job'
when 4 then 'On Job'
when 5 then 'Beg Pour'
when 6 then 'End Pour'
when 7 then 'Returning'
when 8 then 'At Plant'
when 9 then 'In Serv'
when 10 then 'Out of Serv'
when 11 then 'Trk Clock In'
when 12 then 'Trk Clock Out'
when 13 then 'Travel'
when 14 then 'Pre-Ticket'
when 15 then 'Drv Clock In'
when 16 then 'Drv Clock Out'
end,
case status_reason
when 0 then 'GPS Data'
when 1 then 'Manual Status'
when 2 then ' Auto Status'
when 3 then 'On-job based on stop time'
when 4 then 'On-job based on button'
when 5 then 'Loaded based on batch results received'
when 6 then 'Pre-ticketed load released'
when 7 then 'Geofence entered/exited'
else '???' end Reason
,gps.*
from gptstatus gps 
where gps.status_datetime between :"Begin" and :"End"
-- and gps.truck_no = '105' 
and status_col ^= 13

GPS with Ticket Info

select gps.truck_no gpstrk,pth.truck_no ptk_trk, status_datetime,
case status_col
when 0 then 'Unknown'
when 1 then 'Beg Load'
when 2 then 'End Load'
when 3 then 'To Job'
when 4 then 'On Job'
when 5 then 'Beg Pour'
when 6 then 'End Pour'
when 7 then 'Returning'
when 8 then 'At Plant'
when 9 then 'In Serv'
when 10 then 'Out of Serv'
when 11 then 'Trk Clock In'
when 12 then 'Trk Clock Out'
when 13 then 'Travel'
when 14 then 'Pre-Ticket'
when 15 then 'Drv Clock In'
when 16 then 'Drv Clock Out'
end,
case status_reason
when 0 then 'GPS Data'
when 1 then 'Manual Status'
when 2 then ' Auto Status'
when 3 then 'On-job based on stop time'
when 4 then 'On-job based on button'
when 5 then 'Loaded based on batch results received'
when 6 then 'Pre-ticketed load released'
when 7 then 'Geofence entered/exited'
else '???' end Reason,pth.cust_no,pth.ticket_no
,gps.*
from gptstatus gps 
left outer join artptkhd pth on pth.order_session_no=gps.order_session_no and pth.order_trans_no=gps.order_trans_no and gps.load_no=pth.order_load_no
where gps.status_datetime between :"Begin" and :"End"
-- and gps.truck_no = '105' 
and status_col ^= 13

STATUS_COL values:

1 Begin Load
2 End Load
3 Leave Plant
4 Arrive Job
5 Begin Pour
6 End Pour
7 Leave Job
8 Arrive Plant
9 In Service
10 Out of Service
11 Clock In
12 Clock Out
13 GPS Data
14 Pre-Ticket

STATUS_REASON values:

0 GPS Data
1 Manual Status
2 Auto Status
3 On-job based on stop time
4 On-job based on button
5 Loaded based on batch results received
6 Pre-ticketed load released
7 Geofence entered/exited