Advanced Report Expressions (Examples for Columns)

This is for adding in columns to the Advanced Report (think of adding in columns in Excel)

• Date of Service

Date:

strftime('%m/%d/%Y',data('request time', '#dateOfService'),'localtime')

Time:

strftime('%H:%M',data('request time', '#dateOfService'),'localtime')

Date & Time:

strftime('%H:%M %m/%d/%Y',data('request time', '#dateOfService'),'localtime')

Date & Time (f you want time zone):
data('request time', '#created', 'hdr')

Time Stamp Names for First Added, Last Added or All Concatenated 

First Added

select data(id, 'request time', 'Test Time Stamp') from row
order by data(id, 'request time', 'Test Time Stamp') asc

Last Added

select data(id, 'request time', 'Test Time Stamp') from row
order by data(id, 'request time', 'Test Time Stamp') desc

concatenated

select group_concat(data(id, 'request time', 'Test Time Stamp'), ', ') from row

• Return Day of the Week

data(id,'request time', '#dateOfService','w')

• Patient Name

data('transfer item field', 'lastName') || ', ' ||data('transfer item field', 'firstName') || ', ' ||data('transfer item field', 'middleName')

Below are Advanced Report Files that can be imported and used/modified

• All reasons for close on one tier

data(id, 'request reason for close level 1')

|| case when data(id, 'request reason for close level 2') != '' then ' / ' || data(id, 'request reason for close level 2') else '' end

|| case when data(id, 'request reason for close level 3') != '' then ' / ' || data(id, 'request reason for close level 3') else '' end

|| case when data(id, 'request reason for close level 4') != '' then ' / ' || data(id, 'request reason for close level 4') else '' end

|| case when data(id, 'request reason for close level 5') != '' then ' / ' || data(id, 'request reason for close level 5') else '' end

• Time Difference Examples:

round(duration_minutes((data('request time', 'Time of Initial Call')),(data('request time', '#launchPage'))))

 round(duration_minutes(data('patient time', 'ESTIMATED ARRIVAL'),

(select data(id, 'request time', '#waypointArrive') from row where data(id, 'waypoint id') = data(id,

'patient leg pick up waypoint id'))))

Ticket created to first departure

round(duration_minutes(data('request time', '#ticketCreated'),

(select data(id, 'request time', '#waypointDepart') from row

where cast(data(id, 'waypoint index') as int) =

(select cast(data(id, 'waypoint index') as int) from row where data(id, 'waypoint id') =

data(id, 'patient leg pick up waypoint id'))-1)))

Departure from pickup to drop off a receiver

round(duration_minutes((select(select data(id, 'request time', '#waypointDepart') from row where data(id, 'waypoint id') =
data(id, 'patient leg pick up waypoint id'))),
(select(select data(id, 'request time', '#waypointArrive') from row where
data(id, 'waypoint id') = data(id, 'patient leg drop off waypoint id')))))

First Departure to Arrive at Requestor

round(duration_minutes((select data(id, 'request time', '#waypointDepart') from row
where cast(data(id, 'waypoint index') as int) =
(select cast(data(id, 'waypoint index') as int) from row where data(id, 'waypoint id') =
data(id, 'patient leg pick up waypoint id'))-1),
(select (select data(id, 'request time', '#waypointArrive') from row where data(id, 'waypoint id') =
data(id, 'patient leg pick up waypoint id')))))

Arrive at Requestor to Depart from Requestor

round(duration_minutes((select data(id, 'request time', '#waypointArrive') from row where data(id, 'waypoint id') =
data(id, 'patient leg pick up waypoint id')),
(select data(id, 'request time', '#waypointDepart') from row where data(id, 'waypoint id') =
data(id, 'patient leg pick up waypoint id'))))

Arrive at Requestor to Depart Requestor

round(duration_minutes(
(select data(id, 'request time', '#waypointArrive', '') from row where data(id, 'waypoint id') =
data(id, 'patient leg drop off waypoint id')),
(select data(id, 'request time', '#waypointDepart', '') from row where data(id, 'waypoint id') =
data(id, 'patient leg drop off waypoint id'))))

Created to Arrive at Receiver

round(duration_minutes(data('request time', '#ticketCreated'),

(select data(id, 'request time', '#waypointArrive') from row where data(id, 'waypoint id') =
data(id, 'patient leg drop off waypoint id'))))

Created to Arrive at Requestor

round(duration_minutes(data('request time', '#created'),
(select (select data(id, 'request time', '#waypointArrive') from row where data(id, 'waypoint id') =
data(id, 'patient leg pick up waypoint id')))))

• Final Waypoint Name 

Select data(id, 'waypoint name') from row where cast (data (id, 'waypoint index') as int) =
(SELECT max (data (id, 'waypoint index')) from row)

• First Departure Waypoint Name

Select data(id, 'waypoint name') from row where cast(data(id, 'waypoint index') as int) = 0

• First Leg from Base to Patient Pickup to the nearest 10th

select round(cast(data(id, 'waypoint incoming mileage') as float)*10)/10 from row where data(id, 'waypoint id') = data(id, 'patient leg pick up waypoint id')

• Custom Waypoint save as names

select data(id, 'waypoint field', 'save_as_name') from row where data(id, 'waypoint index') = '0'

-the 0 will need to be updated based on the waypoint you want , 0 is first departure, 1 is first arrival and so on….

Related Articles:

Advanced Report Expressions (Pickup/Drop off SQL Times)

Advance Report Expressions (Row Grouper Examples)

Advanced Report Expressions (Other Examples)