Advanced Report Expressions (Pickup/Drop off SQL Times)

• Departure to Patient Pickup

select data(id, 'request time', '#waypointDepart', 'r') 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

• Arrive at Requestor

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

• Departing from Pickup

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

• Arrive at receiver

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

• Departing from receiver

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

• Final Arrival

select data(id, 'request time', '#waypointArrive', 'hsd') from row order by data(id, 'waypoint index') desc

If you do not what the time zone (CDT) replace 'hsd' instead of 'r'

For ‘hsd’ if you ONLY want to see the time (instead of date and time) you ‘hs’

• Arrive at Second Waypoint

select strftime('%d/%m/%Y %H:%M',data(id, 'request time', '#waypointArrive'), 'localtime') from row where cast(data(id, 'waypoint index') as int) = 1

• Depart from Second Waypoint

select strftime('%m/%d/%Y %H:%M',data(id, 'request time', '#waypointDepart'), 'localtime') from row where cast(data(id, 'waypoint index') as int) = 1

• Depart from First Waypoint

select strftime('%d/%m/%Y %H:%M',data(id, 'request time', '#waypointDepart'),'localtime') from row where cast(data(id, 'waypoint index') as int) = 0

• Crew Types/Names

--All in one

ifnull((select group_concat(name, ' / ') from (select distinct(data(id, 'request crew name') as 'name' from row where data(id, 'request crew type') in ('Nurses SCT', 'HROB', 'NNP', 'NEO')))), '')

--Separate

CASE WHEN IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'Nurses SCT'), '') != '' THEN
IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'Nurses SCT'), '') ||
CASE WHEN IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'Paramedic SCT'), '') != '' OR IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'HROB'), '') != '' OR IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'NNP'), '') != '' OR IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'NEO'), '') != '' THEN ' / ' ELSE '' END
ELSE '' END ||

CASE WHEN IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'Paramedic SCT'), '') != '' THEN
IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'Paramedic SCT'), '') ||
CASE WHEN IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'HROB'), '') != '' OR IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'NNP'), '') != '' OR IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'NEO'), '') != '' THEN ' / ' ELSE '' END
ELSE '' END ||

CASE WHEN IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'HROB'), '') != '' THEN
IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'HROB'), '') ||
CASE WHEN IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'NNP'), '') != '' OR IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'NEO'), '') != '' THEN ' / ' ELSE '' END
ELSE '' END ||

CASE WHEN IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'NNP'), '') != '' THEN
IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'NNP'), '') ||
CASE WHEN IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'NEO'), '') != '' THEN ' / ' ELSE '' END
ELSE '' END ||

CASE WHEN IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'NEO'), '') != '' THEN
IFNULL((SELECT group_concat(distinct data(id, 'request crew name')) FROM ROW WHERE data(id, 'request crew type') = 'NEO'), '') ELSE '' END

Related Articles:

Advance Report Expressions (Row Grouper Examples)

Advanced Report Expressions (Other Examples)

Advanced Report Expressions (Examples for Columns)