Select minimum or first bus arrival time

[capronton]

I am trying to select the minimum or first arrival time of a bus. Is it possible to put the statement below in the
"Select Expert" or in a "Command View" object?

How can you select the minimum or first arrival time of a bus grouping by bus?

Select Minimum ({incident_log.incident_date_time}, {incident_log.vehicle_id})
From incident_log

 

[hilfy]

No, you can't do this in the Select Expert. However, you can handle this in a couple of ways...

1. Group by whatever level you're trying to get the first arrival time for (is it Bus? or Bus Stop?). Then sort by arrival time ascending. Put all of your data in the group header section and suppress the details and group footer. This will display the first arrival time.

2. Write a Command. A command is just a SQL Select statement. The logic in your command will look something like this:

Select <ALL of the fields you need for the whole report>
From <all of the tables you need to get data from, joined together>
Where table.ArrivalTime = (
Select min(ArrivalTime)
from table as t
where t.bus = table.bus)

DO NOT join the command to other tables in the report. Instead, write a single command that gets ALL of the data for the report.

DO NOT use the Select Expert to filter the data in the report. Instead, put all of the filter conditions into the Where clause of the command.

For more information about using commands, see this:

http://scn.sap.com/community/crysta...ices-when-using-commands-with-crystal-reports

-Dell

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

http://www.decisionfirst.com

 

[capronton]

Thanks Dell. I am working on the Command "VIEW' now.

 

[capronton]

Dell,

The "subquery" I added gives me the error below. Can you explain or fix the problem?
Thanks for any assistance.

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING
clause or a select list, and the column being aggregated is an outer reference
SQL State: 42000
Native Error:147 [Database Vendor Code:147]


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, trip.trip_id_external,
trip.trip_id,headway_log.scheduled_headway,headway_log.actual_headway,headway_log.actual_leader
FROM timepoint_name timepoint_name
INNER JOIN incident_log incident_log ON timepoint_name.tp_id=incident_log.tp_id
LEFT OUTER JOIN headway_log headway_log ON headway_log.headway_log_id=incident_log.incident_log_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_log.incident_date_time = (select min(incident_log.incident_date_time) from incident_log as t where t.vehicle_id = incident_log.vehicle_id)
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='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>={ts '2016-06-12 04:00:00'} AND incident_log.incident_date_time<{ts '2016-06-15 03:59:00'})
AND (incident_log.incident_log_id % 2) = 0

ORDER BY incident_log.transit_date_time, incident_log.current_route_id, direction_codes.direction_description,trip_timepoint.seq_num,incident_log.tp_id, incident_log.incident_date_time

 

[kray4660]

Your sub-query in where clause should be moved to a HAVING clause since it has a aggregate in it (min(incident_log.incident_date_time).

HAVING incident_log.incident_date_time =
(select min(incident_log.incident_date_time)
from incident_log as t
where t.vehicle_id = incident_log.vehicle_id)
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='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')

Also I am not sure if your database (MS-SQL, mySQL, Oracle, etc.) supports the 'IN' function. It could simplify your SQL statement.

i.e., incident_types.incident_name in ('BUNCH-E', 'BUNCH-L', 'BUNCH-N','EARLY', 'GAP-E', 'GAP-L','GAP-N', 'LAYOVER', 'LATE' , 'NORMAL', 'NORMAL-E','NORMAL-L', 'NORMAL-N')

 

[capronton]

Thanks kray4660. I changed the WHERE clause to the HAVING clause and got the error below. Can you explain?

Column 'incident_log.vehicle_id" is invalid in the HAVING clause because it is not contained in either an aggregate function or GROUP BY
clause.
SQL State 42000
Native Error:8121 [Database Vendor Code:8121]

Also Column 'incident_log.incident_date_time' and 'incident_log.incident_log_id' is invalid in
the HAVING clause.


Also this is MS-SQL database, however I took out the incident_types.incident_name filter since it is not that important to the "VIEW".
Please see Query Command syntax below. Thanks.

SELECT
day_type_vs.description,incident_log.sched_time, incident_log.incident_date_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, trip.trip_id_external,
trip.trip_id,headway_log.scheduled_headway,headway_log.actual_headway,headway_log.actual_leader
FROM timepoint_name timepoint_name
INNER JOIN incident_log incident_log ON timepoint_name.tp_id=incident_log.tp_id
LEFT OUTER JOIN headway_log headway_log ON headway_log.headway_log_id=incident_log.incident_log_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)
HAVING incident_log.incident_date_time =
(select min(incident_log.incident_date_time) as min_Arrival
from incident_log as t
where t.vehicle_id = incident_log.vehicle_id)

AND (incident_date_time>={ts '2016-02-21 04:00:00'} AND incident_log.incident_date_time<{ts '2016-09-01 03:59:00'})
AND (incident_log.incident_log_id % 2) = 0

ORDER BY incident_log.transit_date_time, incident_log.current_route_id, direction_codes.direction_description,trip_timepoint.seq_num,incident_log.tp_id, incident_log.incident_date_time

 

[capronton]

Also, I want to be able to select the minimum incident_date_time without having to group all the other fields. Is this possible.
This is why I wanted to use the sub-query approach to use an aggregate function in the sub-query without grouping the other fields.

 

[kray4660]

I would have kept the WHERE clause and put the other filters in there (AND (incident_date_time>={ts '2016-02-21 04:00:00'} AND incident_log.incident_date_time<{ts '2016-09-01 03:59:00'})
AND (incident_log.incident_log_id % 2) = 0). That might take care of the error message. As far as analyzing your query, not sure if I am the right person for that. You might want to submit your question to SQLServerCentral.com. It is a great forum for MS-SQL related questions.

 

[hilfy]

Here's how I would rewrite it to get what you're looking for:

With minDates as (
  Select t.vehicle_id, min(t.incident_date_time) as min_Arrival
  from incident_log as t
  where t.incident_date_time>={ts '2016-02-21 04:00:00'} AND t.incident_date_time<{ts '2016-09-01 03:59:00'}
  and t.incident_log_id % 2 = 0
  group by t.vehicle_id
)

SELECT
  day_type_vs.description, incident_log.sched_time, incident_log.incident_date_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, trip.trip_id_external,
  trip.trip_id, headway_log.scheduled_headway, headway_log.actual_headway, 
  headway_log.actual_leader
FROM minDates 
  INNER JOIN incident_log incident_log 
    ON minDates.vehicle_id = incident_log.vehicle_id
	  and minDates.min_Arrival = incident_log.incident_date_time
  INNER JOIN timepoint_name timepoint_name
    ON incident_log.tp_id=timepoint_name.tp_id
  LEFT OUTER JOIN headway_log headway_log 
    ON headway_log.headway_log_id=incident_log.incident_log_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
ORDER BY 
  incident_log.transit_date_time, incident_log.current_route_id, direction_codes.direction_description,
  trip_timepoint.seq_num, incident_log.tp_id, incident_log.incident_date_time

Assuming that you want to use parameters for the dates, you would create them in the Command editor following the instructions in the link in my first post above.

-Dell

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

http://www.decisionfirst.com

 

[capronton]

Thanks Dell for all your help with this.

The query you designed was created successfully, however after creating the date parameter in the Command editor I cannot get a "date-range".

How can I modify the query below to use the Select Expert to filter dates? My current report has several formulas that need to filter dates using
the Select Expert.

With minDates as (
Select t.vehicle_id, min(t.incident_date_time) as min_Arrival
from incident_log as t
where t.incident_date_time>={ts '2016-02-21 04:00:00'} AND t.incident_date_time<{ts '2016-09-01 03:59:00'}
and t.incident_log_id % 2 = 0
group by t.vehicle_id
)

SELECT
day_type_vs.description, incident_log.sched_time, incident_log.incident_date_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, trip.trip_id_external,
trip.trip_id, headway_log.scheduled_headway, headway_log.actual_headway,
headway_log.actual_leader
FROM minDates
INNER JOIN incident_log incident_log
ON minDates.vehicle_id = incident_log.vehicle_id
and minDates.min_Arrival = incident_log.incident_date_time
INNER JOIN timepoint_name timepoint_name
ON incident_log.tp_id=timepoint_name.tp_id
LEFT OUTER JOIN headway_log headway_log
ON headway_log.headway_log_id=incident_log.incident_log_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_log.incident_date_time >= {?minArrival}
and incident_log.incident_date_time <= {?minArrival}

ORDER BY
incident_log.transit_date_time, incident_log.current_route_id, direction_codes.direction_description,
trip_timepoint.seq_num, incident_log.tp_id, incident_log.incident_date_time

 

[hilfy]

Instead of using a date range, you'll have to create separate start date and end date parameters and use those.

-Dell

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

http://www.decisionfirst.com

 

[capronton]

Thanks Dell. I created separate start date and end date parameters in the Command Editor
as you can see from the query below. The Command is created successfully, but returns
zero records. Can you explain what might be going on?

With minDates as (
Select t.vehicle_id, min(t.incident_date_time) as min_Arrival
from incident_log as t
where t.incident_date_time>={ts '2016-02-21 04:00:00'} AND t.incident_date_time<{ts '2016-09-01 03:59:00'}
and t.incident_log_id % 2 = 0
group by t.vehicle_id
)

SELECT
day_type_vs.description, incident_log.sched_time, incident_log.incident_date_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, trip.trip_id_external,
trip.trip_id, headway_log.scheduled_headway, headway_log.actual_headway,
headway_log.actual_leader
FROM minDates
INNER JOIN incident_log incident_log
ON minDates.vehicle_id = incident_log.vehicle_id
and minDates.min_Arrival = incident_log.incident_date_time
INNER JOIN timepoint_name timepoint_name
ON incident_log.tp_id=timepoint_name.tp_id
LEFT OUTER JOIN headway_log headway_log
ON headway_log.headway_log_id=incident_log.incident_log_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_log.incident_date_time between {?StartDate}
and {?EndDate}

ORDER BY
incident_log.transit_date_time, incident_log.current_route_id, direction_codes.direction_description,
trip_timepoint.seq_num, incident_log.tp_id, incident_log.incident_date_time

 

[capronton]

Also, when I remove the date filter from the "CTE" as shown below I get
one record. Not sure what's going on, any ideas? Thanks.

With minDates as (
Select t.vehicle_id, min(t.incident_date_time) as min_Arrival
from incident_log as t
where t.incident_log_id % 2 = 0
group by t.vehicle_id
)

 

[hilfy]

Try running just the SQL from the CTE in something like SSMS or Toad to see how many records you get back.

How are you trying to use the date range? My guess is that you're trying to get all buses that started within the date range (hence, the search for minimum date) and their incident records that occur before the end date. Is that correct? If that's the case, I would add the date filter (using the parameters) back in to the CTE, take out the Where clause and change the join to incident_log to something like this:

INNER JOIN incident_log incident_log
ON minDates.vehicle_id = incident_log.vehicle_id
and minDates.min_Arrival <= incident_log.incident_date_time
and incident_log.incident_date_time <= {?EndDate}

-Dell



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

http://www.decisionfirst.com

 

[capronton]

Thanks Dell and others for all your help with this. The command below in the group selection editor allows me to select only minimum "incident_date_time".

{Command.incident_date_time} = minimum({Command.incident_date_time}, {Command.vehicle_id})

I still would like to be able to select minimum "incident_date_time" using a "CTE" or "subquery" but it appears the more questions I ask the more complex it gets.

Thanks again