Create SubQuery with Union Query

[capronton]

How can I incorporate the SubQuery listed below into the Union Query listed below.
I am trying to pull in the minimum incident_date_time.

SubQuery ---(SELECT MIN(incident_log.incident_date_time) FROM incident_log)

Union Query
SELECT
day_type_vs.description, incident_log.sched_time, incident_log.transit_date_time, incident_log.block_id, incident_log.current_route_id,
incident_types.incident_name, incident_types.incident_desc, incident_log.route_id, incident_log.vehicle_id, incident_log.driver_id, incident_log.deviation,
direction_codes.direction_description, timepoint_name.tp_lname, incident_log.incident_log_id, timepoints.tp_loc_x, timepoints.tp_loc_y,
timepoints.tp_radius, day_type_vs.description, trip_timepoint.seq_num, garage.garage_description, incident_log.tp_id, trip.end_tpid, trip_timepoint.tp_id,
trip.start_time, trip.end_time
FROM timepoint_name timepoint_name
INNER JOIN incident_log incident_log ON timepoint_name.tp_id=incident_log.tp_id
INNER JOIN incident_types incident_types ON incident_log.incident_type=incident_types.incident_type
INNER JOIN direction_codes direction_codes ON incident_log.direction_code_id=direction_codes.direction_code_id
INNER JOIN timepoints timepoints ON incident_log.tp_id=timepoints.tp_id
INNER JOIN vehicle_schedule vehicle_schedule
ON incident_log.sched_version=vehicle_schedule.sched_version


INNER JOIN trip_timepoint trip_timepoint ON ((incident_log.trip_id=trip_timepoint.trip_id) AND (vehicle_schedule.sched_version=trip_timepoint.sched_version)) AND (incident_log.tp_id=trip_timepoint.tp_id)
INNER JOIN garage garage ON incident_log.garage_id=garage.garage_id
INNER JOIN trip trip ON ((vehicle_schedule.sched_version=trip.sched_version) AND (incident_log.trip_id=trip.trip_id)) AND (incident_log.current_route_id=trip.route_id)
INNER JOIN day_type_vs day_type_vs ON (vehicle_schedule.description=day_type_vs.description) AND (vehicle_schedule.day_type_vs=day_type_vs.id)
WHERE day_type_vs.description='WKDAY'
AND (incident_types.incident_name='BUNCH-E' OR incident_types.incident_name='BUNCH-L' OR incident_types.incident_name='BUNCH-N' OR incident_types.incident_name='EARLY' OR incident_types.incident_name='GAP-E' OR incident_types.incident_name='GAP-L' OR incident_types.incident_name='GAP-N' OR incident_types.incident_name='LATE' OR incident_types.incident_name='NORMAL' OR incident_types.incident_name='NORMAL-E' OR incident_types.incident_name='NORMAL-L' OR incident_types.incident_name='NORMAL-N')
AND (incident_date_time>={ts '2016-02-21 04:00:00'} AND incident_log.incident_date_time<{ts '2016-04-20 03:59:00'})
AND trip_timepoint.tp_id<>trip.end_tpid
union
SELECT
day_type_vs.description,incident_log.incident_date_time, incident_log.sched_time, incident_log.transit_date_time, incident_log.block_id, incident_log.current_route_id,
incident_types.incident_name, incident_types.incident_desc, incident_log.route_id, incident_log.vehicle_id, incident_log.driver_id, incident_log.deviation,
direction_codes.direction_description, timepoint_name.tp_lname, incident_log.incident_log_id, timepoints.tp_loc_x, timepoints.tp_loc_y,
timepoints.tp_radius, day_type_vs.description, trip_timepoint.seq_num, garage.garage_description, incident_log.tp_id, trip.end_tpid, trip_timepoint.tp_id,
trip.start_time, trip.end_time
FROM timepoint_name timepoint_name
INNER JOIN incident_log incident_log ON timepoint_name.tp_id=incident_log.tp_id
INNER JOIN incident_types incident_types ON incident_log.incident_type=incident_types.incident_type
INNER JOIN direction_codes direction_codes ON incident_log.direction_code_id=direction_codes.direction_code_id
INNER JOIN timepoints timepoints ON incident_log.tp_id=timepoints.tp_id

INNER JOIN vehicle_schedule vehicle_schedule
ON incident_log.sched_version=vehicle_schedule.sched_version


INNER JOIN trip_timepoint trip_timepoint ON ((incident_log.trip_id=trip_timepoint.trip_id) AND (vehicle_schedule.sched_version=trip_timepoint.sched_version)) AND (incident_log.tp_id=trip_timepoint.tp_id)
INNER JOIN garage garage ON incident_log.garage_id=garage.garage_id
INNER JOIN trip trip ON ((vehicle_schedule.sched_version=trip.sched_version) AND (incident_log.trip_id=trip.trip_id)) AND (incident_log.current_route_id=trip.route_id)
INNER JOIN day_type_vs day_type_vs ON (vehicle_schedule.description=day_type_vs.description) AND (vehicle_schedule.day_type_vs=day_type_vs.id)
WHERE day_type_vs.description='SATURDAY'
AND (incident_types.incident_name='BUNCH-E' OR incident_types.incident_name='BUNCH-L' OR incident_types.incident_name='BUNCH-N' OR incident_types.incident_name='EARLY' OR incident_types.incident_name='GAP-E' OR incident_types.incident_name='GAP-L' OR incident_types.incident_name='GAP-N' OR incident_types.incident_name='LATE' OR incident_types.incident_name='NORMAL' OR incident_types.incident_name='NORMAL-E' OR incident_types.incident_name='NORMAL-L' OR incident_types.incident_name='NORMAL-N')
AND (incident_log.incident_date_time>={ts '2016-02-21 04:00:00'} AND incident_log.incident_date_time<{ts '2016-04-20 03:59:00'})
AND trip_timepoint.tp_id<>trip.end_tpid

 

[capronton]

I think I can write this query without the "Union", however I get the error
"Incorrect syntax near the Keyword SELECT". Can someone let me know what is wrong with my
SubQuery below? Thanks in advance for any assistance.

SELECT
day_type_vs.description, incident_log.incident_date_time, incident_log.sched_time, incident_log.transit_date_time, incident_log.block_id, incident_log.current_route_id,
incident_types.incident_name, incident_types.incident_desc, incident_log.route_id, incident_log.vehicle_id, incident_log.driver_id, incident_log.deviation,
direction_codes.direction_description, timepoint_name.tp_lname, incident_log.incident_log_id, timepoints.tp_loc_x, timepoints.tp_loc_y,
timepoints.tp_radius, day_type_vs.description, trip_timepoint.seq_num, garage.garage_description, incident_log.tp_id, trip.end_tpid, trip_timepoint.tp_id,
trip.start_time, trip.end_time,
SELECT MIN(incident_log.incident_date_time) FROM Incident_log) as minIncTime
FROM timepoint_name timepoint_name
INNER JOIN incident_log incident_log ON timepoint_name.tp_id=incident_log.tp_id
INNER JOIN incident_types incident_types ON incident_log.incident_type=incident_types.incident_type
INNER JOIN direction_codes direction_codes ON incident_log.direction_code_id=direction_codes.direction_code_id
INNER JOIN timepoints timepoints ON incident_log.tp_id=timepoints.tp_id
INNER JOIN vehicle_schedule vehicle_schedule
ON incident_log.sched_version=vehicle_schedule.sched_version


INNER JOIN trip_timepoint trip_timepoint ON ((incident_log.trip_id=trip_timepoint.trip_id) AND (vehicle_schedule.sched_version=trip_timepoint.sched_version)) AND (incident_log.tp_id=trip_timepoint.tp_id)
INNER JOIN garage garage ON incident_log.garage_id=garage.garage_id
INNER JOIN trip trip ON ((vehicle_schedule.sched_version=trip.sched_version) AND (incident_log.trip_id=trip.trip_id)) AND (incident_log.current_route_id=trip.route_id)
INNER JOIN day_type_vs day_type_vs ON (vehicle_schedule.description=day_type_vs.description) AND (vehicle_schedule.day_type_vs=day_type_vs.id)
WHERE (incident_date_time>={ts '2016-02-21 04:00:00'} AND incident_log.incident_date_time<{ts '2016-04-20 03:59:00'})

ORDER BY incident_log.transit_date_time, incident_log.current_route_id, incident_log.vehicle_id, incident_log.incident_date_time

 

[CrystalizeCanada]

In a UNION join the number of columns must match.
The example below will cause an error because I'm trying to UNION 4 fields onto 2:

SELECT fielda, fieldb, fieldc, fieldd
FROM TableB
UNION
SELECT this, that
FROM TableA

Fill in the gaps so that the number of columns match:

SELECT fielda, fieldb, fieldc, fieldd
FROM TableB
UNION
SELECT NULL, NULL, this, that
FROM TableA

In your case assuming your original UNION query works you can add the new query to the bottom but you have filling out the missing columns - you can add a NULL for every missing column.


UNION
SELECT NULL, NULL, NULL, etc, MIN(incident_log.incident_date_time)
FROM incident_log

Assuming you don't have duplicates in the sets of data you may find using UNION ALL instead of UNION is faster.


Gordon BOCP
Crystalize

 

[capronton]

I got the query below to work, but I'm only getting first incident_date_time. Can someone explain why?

SELECT
day_type_vs.description, incident_log.incident_date_time, incident_log.sched_time, incident_log.transit_date_time, incident_log.block_id, incident_log.current_route_id,
incident_types.incident_name, incident_types.incident_desc, incident_log.route_id, incident_log.vehicle_id, incident_log.driver_id, incident_log.deviation,
direction_codes.direction_description, timepoint_name.tp_lname, incident_log.incident_log_id, timepoints.tp_loc_x, timepoints.tp_loc_y,
timepoints.tp_radius, day_type_vs.description, trip_timepoint.seq_num, garage.garage_description, incident_log.tp_id, trip.end_tpid, trip_timepoint.tp_id,
trip.start_time, trip.end_time,
(SELECT MIN(incident_log.incident_date_time)
FROM timepoint_name timepoint_name
INNER JOIN incident_log incident_log ON timepoint_name.tp_id=incident_log.tp_id
INNER JOIN incident_types incident_types ON incident_log.incident_type=incident_types.incident_type
INNER JOIN direction_codes direction_codes ON incident_log.direction_code_id=direction_codes.direction_code_id
INNER JOIN timepoints timepoints ON incident_log.tp_id=timepoints.tp_id
INNER JOIN vehicle_schedule vehicle_schedule
ON incident_log.sched_version=vehicle_schedule.sched_version
INNER JOIN trip_timepoint trip_timepoint ON ((incident_log.trip_id=trip_timepoint.trip_id) AND (vehicle_schedule.sched_version=trip_timepoint.sched_version)) AND (incident_log.tp_id=trip_timepoint.tp_id)
INNER JOIN garage garage ON incident_log.garage_id=garage.garage_id
INNER JOIN trip trip ON ((vehicle_schedule.sched_version=trip.sched_version) AND (incident_log.trip_id=trip.trip_id)) AND (incident_log.current_route_id=trip.route_id)
INNER JOIN day_type_vs day_type_vs ON (vehicle_schedule.description=day_type_vs.description) AND (vehicle_schedule.day_type_vs=day_type_vs.id)
WHERE (incident_date_time>={ts '2016-02-21 04:00:00'} AND incident_log.incident_date_time<{ts '2016-04-20 03:59:00'})) as minIncTime

FROM timepoint_name timepoint_name
INNER JOIN incident_log incident_log ON timepoint_name.tp_id=incident_log.tp_id
INNER JOIN incident_types incident_types ON incident_log.incident_type=incident_types.incident_type
INNER JOIN direction_codes direction_codes ON incident_log.direction_code_id=direction_codes.direction_code_id
INNER JOIN timepoints timepoints ON incident_log.tp_id=timepoints.tp_id
INNER JOIN vehicle_schedule vehicle_schedule
ON incident_log.sched_version=vehicle_schedule.sched_version
INNER JOIN trip_timepoint trip_timepoint ON ((incident_log.trip_id=trip_timepoint.trip_id) AND (vehicle_schedule.sched_version=trip_timepoint.sched_version)) AND (incident_log.tp_id=trip_timepoint.tp_id)
INNER JOIN garage garage ON incident_log.garage_id=garage.garage_id
INNER JOIN trip trip ON ((vehicle_schedule.sched_version=trip.sched_version) AND (incident_log.trip_id=trip.trip_id)) AND (incident_log.current_route_id=trip.route_id)
INNER JOIN day_type_vs day_type_vs ON (vehicle_schedule.description=day_type_vs.description) AND (vehicle_schedule.day_type_vs=day_type_vs.id)
WHERE (incident_date_time>={ts '2016-02-21 04:00:00'} AND incident_log.incident_date_time<{ts '2016-04-20 03:59:00'})

ORDER BY incident_log.transit_date_time, incident_log.current_route_id, incident_log.vehicle_id, incident_log.incident_date_time

Data retreived by above query

incident_log_id tDate incDate minIncTime sched_time bus
466058417 2/22/16 11:44:23 PM 2/21/2016 4:00:29 AM 11:14 PM 2004
466058993 2/22/16 11:45:34 PM 2/21/2016 4:00:29 AM 11:45 PM 2004
466059783 2/22/16 11:47:26 PM 2/21/2016 4:00:29 AM 11:45 PM 2004
465200677 2/22/16 6:54:44 AM 2/21/2016 4:00:29 AM 6:57 AM 2005
465210061 2/22/16 7:03:56 AM 2/21/2016 4:00:29 AM 7:07 AM 2005
465855385 2/22/16 6:29:26 PM 2/21/2016 4:00:29 AM 6:26 PM 2005
465863011 2/22/16 6:37:40 PM 2/21/2016 4:00:29 AM 6:26 PM 2005
465863739 2/22/16 6:38:24 PM 2/21/2016 4:00:29 AM 6:36 PM 2005
465871645 2/22/16 6:47:19 PM 2/21/2016 4:00:29 AM 6:36 PM 2005
465871649 2/22/16 6:47:20 PM 2/21/2016 4:00:29 AM 6:36 PM 2005
465895613 2/22/16 7:15:01 PM 2/21/2016 4:00:29 AM 6:36 PM 2005
465137039 2/22/16 5:45:46 AM 2/21/2016 4:00:29 AM 5:41 AM 2006
465137523 2/22/16 5:46:22 AM 2/21/2016 4:00:29 AM 5:42 AM 2006
465938835 2/22/16 8:11:26 PM 2/21/2016 4:00:29 AM 8:04 PM 2006
465943545 2/22/16 8:17:38 PM 2/21/2016 4:00:29 AM 8:04 PM 2006
465944183 2/22/16 8:18:30 PM 2/21/2016 4:00:29 AM 8:14 PM 2006
465946901 2/22/16 8:22:20 PM 2/21/2016 4:00:29 AM 8:14 PM 2006
465121751 2/22/16 5:27:20 AM 2/21/2016 4:00:29 AM 5:42 AM 2007
465135425 2/22/16 5:43:40 AM 2/21/2016 4:00:29 AM 6:05 AM 2007
466009697 2/22/16 10:06:12 PM 2/21/2016 4:00:29 AM 10:06 PM 2007
466015541 2/22/16 10:17:14 PM 2/21/2016 4:00:29 AM 10:06 PM 2007
466016027 2/22/16 10:18:10 PM 2/21/2016 4:00:29 AM 10:19 PM 2007
466016593 2/22/16 10:19:13 PM 2/21/2016 4:00:29 AM 10:19 PM 2007
465137417 2/22/16 5:46:12 AM 2/21/2016 4:00:29 AM 5:49 AM 2008
465145887 2/22/16 5:57:06 AM 2/21/2016 4:00:29 AM 6:03 AM 2008
466005773 2/22/16 9:58:44 PM 2/21/2016 4:00:29 AM 9:58 PM 2008
466017595 2/22/16 10:20:57 PM 2/21/2016 4:00:29 AM 9:58 PM 2008
466017899 2/22/16 10:21:30 PM 2/21/2016 4:00:29 AM 10:18 PM 2008
466028013 2/22/16 10:40:32 PM 2/21/2016 4:00:29 AM 10:18 PM 2008
466028029 2/22/16 10:40:32 PM 2/21/2016 4:00:29 AM 10:18 PM 2008
466037555 2/22/16 11:01:07 PM 2/21/2016 4:00:29 AM 10:18 PM 2008
465131929 2/22/16 5:39:10 AM 2/21/2016 4:00:29 AM 5:50 AM 2009
465150319 2/22/16 6:02:30 AM 2/21/2016 4:00:29 AM 6:15 AM 2009
466050003 2/22/16 11:26:24 PM 2/21/2016 4:00:29 AM 11:26 PM 2009
466058963 2/22/16 11:45:29 PM 2/21/2016 4:00:29 AM 11:26 PM 2009
466059219 2/22/16 11:46:08 PM 2/21/2016 4:00:29 AM 11:45 PM 2009
466059605 2/22/16 11:47:04 PM 2/21/2016 4:00:29 AM 11:45 PM 2009
465190119 2/22/16 6:43:48 AM 2/21/2016 4:00:29 AM 6:43 AM 2011
465206661 2/22/16 7:00:26 AM 2/21/2016 4:00:29 AM 7:06 AM 2011
465248633 2/22/16 7:45:34 AM 2/21/2016 4:00:29 AM 7:35 AM 2011
465260903 2/22/16 7:59:12 AM 2/21/2016 4:00:29 AM 7:55 AM 2011
465930719 2/22/16 8:00:28 PM 2/21/2016 4:00:29 AM 7:57 PM 2011
465940563 2/22/16 8:13:34 PM 2/21/2016 4:00:29 AM 7:57 PM 2011
465941321 2/22/16 8:14:42 PM 2/21/2016 4:00:29 AM 8:17 PM 2011
465942623 2/22/16 8:16:22 PM 2/21/2016 4:00:29 AM 8:17 PM 2011
465148051 2/22/16 5:59:46 AM 2/21/2016 4:00:29 AM 6:04 AM 2012
465155213 2/22/16 6:07:58 AM 2/21/2016 4:00:29 AM 6:14 AM 2012
466040297 2/22/16 11:06:36 PM 2/21/2016 4:00:29 AM 11:06 PM 2012

 

[kray4660]

You are only showing partial query results (i.e., not all the fields that are in the query are being presented in the results). Also with no source data to look at, I am not sure what you are expecting.

 

[capronton]

Source Data is below. Please look at row 8, 9, 10. You will notice that these rows have the same bus number (5001) and the same schTime (6:36)
I am trying to either trying to filter out the additional rows by pulling the minimum incTime (6:15:18).

My main goal is to be able to subtract the incTimes ( 6:52:19 - 6:15:18), however with the duplicate records between I have not been able to get this done.
This occurs throughout the source data. If you have duplicate or several records with the same bus and schTime, is there way of selecting the first or minimum incTime
and subtracting the first incTime from the next incTime once the bus and schTime changes? I cannot use the previous or next functions because of the duplicate records
in between.

tDate incTime schTime startTrip endTrip bus
4/5/16 5:00:17 4:58 4:58 AM 6:22 AM 5013
4/5/16 5:14:18 5:13 5:13 AM 6:37 AM 5015
4/5/16 5:29:18 5:28 5:28 AM 6:52 AM 5020
4/5/16 5:44:18 5:43 5:43 AM 7:07 AM 5009
4/5/16 6:01:18 5:58 5:58 AM 7:22 AM 5017
4/5/16 6:14:18 6:13 6:13 AM 7:37 AM 5011
4/5/16 6:22:19 6:21 6:21 AM 7:59 AM 5022
4/5/16 6:15:18 6:36 6:36 AM 8:14 AM 5001
4/5/16 6:16:18 6:36 6:36 AM 8:14 AM 5001
4/5/16 6:34:19 6:36 6:36 AM 8:14 AM 5001
4/5/16 6:52:19 6:51 6:51 AM 8:29 AM 5003
4/5/16 7:04:19 7:03 7:03 AM 8:41 AM 5019
4/5/16 7:06:19 7:03 7:03 AM 8:41 AM 5019
4/5/16 7:10:19 7:03 7:03 AM 8:41 AM 5019
4/5/16 7:12:19 7:03 7:03 AM 8:41 AM 5019
4/5/16 7:17:19 7:15 7:15 AM 8:53 AM 5014
4/5/16 7:18:19 7:15 7:15 AM 8:53 AM 5014
4/5/16 7:19:19 7:15 7:15 AM 8:53 AM 5014
4/5/16 7:28:19 7:27 7:27 AM 9:05 AM 5002
4/5/16 7:33:19 7:27 7:27 AM 9:05 AM 5002
4/5/16 7:42:20 7:27 7:27 AM 9:05 AM 5002
4/5/16 7:43:20 7:27 7:27 AM 9:05 AM 5002
4/5/16 7:40:20 7:39 7:39 AM 9:17 AM 5005
4/5/16 7:53:20 7:51 7:51 AM 9:29 AM 5021
4/5/16 8:04:20 8:03 8:03 AM 9:41 AM 5018
4/5/16 8:16:20 8:15 8:15 AM 9:53 AM 5010
4/5/16 8:35:20 8:32 8:32 AM 10:02 AM 5013
4/5/16 8:49:20 8:45 8:45 AM 10:15 AM 5015
4/5/16 8:50:20 8:45 8:45 AM 10:15 AM 5015
4/5/16 8:59:21 8:58 8:58 AM 10:30 AM 5020
4/5/16 9:01:21 8:58 8:58 AM 10:30 AM 5020
4/5/16 8:47:20 9:11 9:11 AM 10:43 AM 5009
4/5/16 8:48:20 9:11 9:11 AM 10:43 AM 5009
4/5/16 8:49:20 9:11 9:11 AM 10:43 AM 5009
4/5/16 8:50:20 9:11 9:11 AM 10:43 AM 5009
4/5/16 9:15:21 9:11 9:11 AM 10:43 AM 5009

 

[kray4660]

looks like you need to group by bus (and of course all the other fields not part of aggregate). Keep your one query that gets the min time create another sub-query to get the msx time and subtract the two. Of course you could (possibly) do this in crystal reports with grouping by bus, order by the inctime. That way the min inctime is in the group header (use a global or shared variable to save this value in a formula). Then in the group footer (which will have the max inctime), subtract the the two times

 

[capronton]

I'm sorry, let me try to be more clear. I do not need to subtract minimum incTime from maximum incTime.

incTime represents bus arrival time and the source data above shows that the bus has 2 or more arrival times.
I need to subtract the first or minimum arrival time from next bus first or minimum arrival time

Looking at the source data above, I would need to subtract 7:04:19 (first arrival time for bus 5019) from
7:17:19 (first arrival time for bus 5014)

Is there a crystal report formula that can do this? Is there any way I can do this?

Thanks.

 

[capronton]

No ideas on this? Is there a solution to the above question?

Thanks.

 

[hilfy]

What type of database are you working with? If I were writing this, I would make a few tweaks to the query. I have learned that frequents, queries are much more efficient if you move the filters from the "Where" clause into the joins. Including your minimum incident time, here's what that query would look like:
[pre]
SELECT
day_type_vs.description,
incident_log.sched_time,
incident_log.transit_date_time,
incident_log.block_id,
incident_log.current_route_id,
incident_types.incident_name,
incident_types.incident_desc,
incident_log.route_id,
incident_log.vehicle_id,
incident_log.driver_id,
incident_log.deviation,
direction_codes.direction_description,
timepoint_name.tp_lname,
incident_log.incident_log_id,
timepoints.tp_loc_x,
timepoints.tp_loc_y,
timepoints.tp_radius,
day_type_vs.description,
trip_timepoint.seq_num,
garage.garage_description,
incident_log.tp_id,
trip.end_tpid,
trip_timepoint.tp_id,
trip.start_time,
trip.end_time ,
min(incident_log.incident_date_time) incTime
FROM timepoint_name timepoint_name
INNER JOIN incident_log incident_log
ON timepoint_name.tp_id=incident_log.tp_id
AND incident_log.incident_date_time between {ts '2016-02-21 04:00:00'} and {ts '2016-04-20-03:59:00'}
INNER JOIN incident_types incident_types
ON incident_log.incident_type=incident_types.incident_type
AND incident_types.incident_name in ('BUNCH-E','BUNCH-L','BUNCH-N', 'EARLY','GAP-E','GAP-L', 'GAP-N', 'LATE', 'NORMAL', 'NORMAL-E', 'NORMAL-L', 'NORMAL-N')
INNER JOIN direction_codes direction_codes
ON incident_log.direction_code_id=direction_codes.direction_code_id
INNER JOIN timepoints timepoints
ON incident_log.tp_id=timepoints.tp_id
INNER JOIN vehicle_schedule vehicle_schedule
ON incident_log.sched_version=vehicle_schedule.sched_version
INNER JOIN trip_timepoint trip_timepoint
ON incident_log.trip_id=trip_timepoint.trip_id
AND vehicle_schedule.sched_version=trip_timepoint.sched_version
AND incident_log.tp_id=trip_timepoint.tp_id
AND trip_timepoint.tp_id<>trip.end_tpid
INNER JOIN garage garage
ON incident_log.garage_id=garage.garage_id
INNER JOIN trip trip
ON vehicle_schedule.sched_version=trip.sched_version
AND incident_log.trip_id=trip.trip_id
AND incident_log.current_route_id=trip.route_id
INNER JOIN day_type_vs day_type_vs
ON vehicle_schedule.description=day_type_vs.description
AND vehicle_schedule.day_type_vs=day_type_vs.id
GROUP BY
day_type_vs.description,
incident_log.sched_time,
incident_log.transit_date_time,
incident_log.block_id,
incident_log.current_route_id,
incident_types.incident_name,
incident_types.incident_desc,
incident_log.route_id,
incident_log.vehicle_id,
incident_log.driver_id,
incident_log.deviation,
direction_codes.direction_description,
timepoint_name.tp_lname,
incident_log.incident_log_id,
timepoints.tp_loc_x,
timepoints.tp_loc_y,
timepoints.tp_radius,
day_type_vs.description,
trip_timepoint.seq_num,
garage.garage_description,
incident_log.tp_id,
trip.end_tpid,
trip_timepoint.tp_id,
trip.start_time,
trip.end_time [/pre]

From here, the question is do you want the subtraction to show for the next bus minus this bus or for this bus minus the previous bus? In Crystal, you would group by incTime and then use one of these calculations:

Next bus - This bus:
if not OnLastRecord then
Next({command.incTime}) - {command.incTime}

This bus - Previous bus:
if not OnFirstRecord then
{command.incTime} - Previous({command.incTime})

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com