Would it be possible to Group By Route and Peak and the attached SQL Query?
When I “group by the Peak field” using Crystal Report Group Expert this forces a change in the sort and throws off the number of “Ontime, Late, Missing_Data”.
My thought was to add the “Group By Clause” within the SQL Command Query” rather than using Crystal Report Group Expert to prevent altering the sort.
I have attached the SQL Command Query, the BRT OTP report, and the BRT OTP by time of day. I am pretty sure the sorting difference is what is throwing the numbers off.
Any suggestions would be appreciated.
I have also pasted the sqlQuery_Command below. How can you Group By Route and Peak in the query below without changing the Order By clause?
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_log.direction_code_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, trip.trip_id_external,
trip.trip_id,
(incident_log.incident_log_id % 2) as "modulo",
dow = CASE day_type_vs.service_days
WHEN 64 THEN 'Sunday'
WHEN 1 THEN 'Saturday'
WHEN 62 THEN
CASE datepart(dw, incident_log.transit_date_time)
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
END
END,
peak = CASE WHEN (datepart(hh, trip.start_time) + datepart(n, trip.start_time)/60) >= 3.5 AND (datepart(hh, trip.start_time) < 6) THEN 'Early AM'
WHEN datepart(hh, trip.start_time) >= 6 AND datepart(hh, trip.start_time) < 9 THEN 'AM Peak'
WHEN datepart(hh, trip.start_time) >= 9 AND datepart(hh, trip.start_time) < 15 THEN 'Mid Day'
WHEN datepart(hh, trip.start_time) >= 15 AND datepart(hh, trip.start_time) < 18 THEN 'PM Peak'
WHEN datepart(hh, trip.start_time) >= 18 AND datepart(hh, trip.start_time) < 22 THEN 'Evening'
ELSE 'Late Night'
END
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.day_type_vs=day_type_vs.id)
WHERE (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='LAYOVER' 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>= {?StartDate} AND incident_log.incident_date_time<={?EndDate})
AND trip.trip_id_external IS NOT NULL
AND (incident_log.current_route_id = 801 OR incident_log.current_route_id = 803)
ORDER BY incident_log.transit_date_time, incident_log.current_route_id, direction_codes.direction_description,trip_timepoint.seq_num,incident_log.tp_id,
CASE WHEN (incident_log.incident_log_id % 2) = 0 THEN
trip.start_time ELSE incident_log.incident_date_time
END,
incident_log.incident_date_time;
When I “group by the Peak field” using Crystal Report Group Expert this forces a change in the sort and throws off the number of “Ontime, Late, Missing_Data”.
My thought was to add the “Group By Clause” within the SQL Command Query” rather than using Crystal Report Group Expert to prevent altering the sort.
I have attached the SQL Command Query, the BRT OTP report, and the BRT OTP by time of day. I am pretty sure the sorting difference is what is throwing the numbers off.
Any suggestions would be appreciated.
I have also pasted the sqlQuery_Command below. How can you Group By Route and Peak in the query below without changing the Order By clause?
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_log.direction_code_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, trip.trip_id_external,
trip.trip_id,
(incident_log.incident_log_id % 2) as "modulo",
dow = CASE day_type_vs.service_days
WHEN 64 THEN 'Sunday'
WHEN 1 THEN 'Saturday'
WHEN 62 THEN
CASE datepart(dw, incident_log.transit_date_time)
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
END
END,
peak = CASE WHEN (datepart(hh, trip.start_time) + datepart(n, trip.start_time)/60) >= 3.5 AND (datepart(hh, trip.start_time) < 6) THEN 'Early AM'
WHEN datepart(hh, trip.start_time) >= 6 AND datepart(hh, trip.start_time) < 9 THEN 'AM Peak'
WHEN datepart(hh, trip.start_time) >= 9 AND datepart(hh, trip.start_time) < 15 THEN 'Mid Day'
WHEN datepart(hh, trip.start_time) >= 15 AND datepart(hh, trip.start_time) < 18 THEN 'PM Peak'
WHEN datepart(hh, trip.start_time) >= 18 AND datepart(hh, trip.start_time) < 22 THEN 'Evening'
ELSE 'Late Night'
END
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.day_type_vs=day_type_vs.id)
WHERE (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='LAYOVER' 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>= {?StartDate} AND incident_log.incident_date_time<={?EndDate})
AND trip.trip_id_external IS NOT NULL
AND (incident_log.current_route_id = 801 OR incident_log.current_route_id = 803)
ORDER BY incident_log.transit_date_time, incident_log.current_route_id, direction_codes.direction_description,trip_timepoint.seq_num,incident_log.tp_id,
CASE WHEN (incident_log.incident_log_id % 2) = 0 THEN
trip.start_time ELSE incident_log.incident_date_time
END,
incident_log.incident_date_time;