tonMachine100
asked on
analytics query - oracle sql
I’m writing a report around students taxi routes (taxis to and from school) and need some assistance.
Please see the attachment which some sample data.
fig 1. output from routes table
this lists the route details for each student. Each students route could be:
* an outbound trip only - a lift from home to school (2 Map ID numbers - MAP_ID1 and MAP_ID2)
* an outbound and inbound trip - a lift from home to school, and from school to home (4 map ID numbers - MAP_ID1, MAP_ID2, MAP_ID3, MAP_ID4)
* an inbound trip only - a lift from school to home (2 Map ID numbers - MAP_ID3 and MAP_ID4)
The MAP_ID* fields indicate the students route start and route end points.
fig 2. output from stops table
this lists all the stops within each route (and the route start and end points). Each route may have multiple stops, e.g.
* route_id 37682 - this route transports one student to and from school
* route_id 37685 - this route transports one student from home to school only
* route_id 37687 - this route transports multiple students from their homes to school, and from school back home
The stop_type column indicates if the stop is at an address (A) or school (B).
fig 3. the desired output
I need the report to list all the stops for each student (and the route start and end point) in columns Outbound and Inbound. The stops need to be concatenated into either column. For example:
* student 208865 (route 37687) - has 3 stops in Outbound -'Student Five (Home Address), Student Six (Home Address), Another School', and 3 stops in inbound - 'Another School, Student Six (Home Address), Student Five (Home Address)'
Any help is appreciated.
ee-output.xlsx
Please see the attachment which some sample data.
fig 1. output from routes table
this lists the route details for each student. Each students route could be:
* an outbound trip only - a lift from home to school (2 Map ID numbers - MAP_ID1 and MAP_ID2)
* an outbound and inbound trip - a lift from home to school, and from school to home (4 map ID numbers - MAP_ID1, MAP_ID2, MAP_ID3, MAP_ID4)
* an inbound trip only - a lift from school to home (2 Map ID numbers - MAP_ID3 and MAP_ID4)
The MAP_ID* fields indicate the students route start and route end points.
fig 2. output from stops table
this lists all the stops within each route (and the route start and end points). Each route may have multiple stops, e.g.
* route_id 37682 - this route transports one student to and from school
* route_id 37685 - this route transports one student from home to school only
* route_id 37687 - this route transports multiple students from their homes to school, and from school back home
The stop_type column indicates if the stop is at an address (A) or school (B).
fig 3. the desired output
I need the report to list all the stops for each student (and the route start and end point) in columns Outbound and Inbound. The stops need to be concatenated into either column. For example:
* student 208865 (route 37687) - has 3 stops in Outbound -'Student Five (Home Address), Student Six (Home Address), Another School', and 3 stops in inbound - 'Another School, Student Six (Home Address), Student Five (Home Address)'
Any help is appreciated.
ee-output.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
that query still has an extraneous join and illegal grouping - it doesn't execute
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you changed it
I was commenting on your original version which looked like this:
(tonMachine100 - don't use this, it's a copy of a previous incorrect query)
SELECT MAX(r.route_id),
MAX(r.stud_id),
MAX(r.stud_name),
MAX(r.map_id1),
MAX(r.map_id2),
MAX(r.map_id3),
MAX(r.map_id4),
(SELECT LISTAGG(descrp) WITHIN GROUP (ORDER BY map_id)
FROM stops
WHERE route_id = r.route_id AND map_id BETWEEN r.map_id1 AND r.map_id2)
AS outbound,
(SELECT LISTAGG(descrp) WITHIN GROUP (ORDER BY map_id)
FROM stops
WHERE route_id = r.route_id AND map_id BETWEEN r.map_id3 AND r.map_id4)
AS inbound
FROM routes r JOIN stops s ON (r.route_id = s.route_id)
GROUP BY r.stud_id
your current version is the same as mine now, except you changed the ordering column, but either step or map_id works for the given sample data
I was commenting on your original version which looked like this:
(tonMachine100 - don't use this, it's a copy of a previous incorrect query)
SELECT MAX(r.route_id),
MAX(r.stud_id),
MAX(r.stud_name),
MAX(r.map_id1),
MAX(r.map_id2),
MAX(r.map_id3),
MAX(r.map_id4),
(SELECT LISTAGG(descrp) WITHIN GROUP (ORDER BY map_id)
FROM stops
WHERE route_id = r.route_id AND map_id BETWEEN r.map_id1 AND r.map_id2)
AS outbound,
(SELECT LISTAGG(descrp) WITHIN GROUP (ORDER BY map_id)
FROM stops
WHERE route_id = r.route_id AND map_id BETWEEN r.map_id3 AND r.map_id4)
AS inbound
FROM routes r JOIN stops s ON (r.route_id = s.route_id)
GROUP BY r.stud_id
your current version is the same as mine now, except you changed the ordering column, but either step or map_id works for the given sample data
yes, I posted it and then realized that I had copy/pasted the wrong version, so I updated it.
ASKER
this is spot on - thanks chaps
select r.route_id, r.stud_id, r.stud_name,
r.map_id1, r.map_id2, r.map_id3, r.map_id4,
(select group_concat(descrp) from stops where route_id=r.route_id
and map_id between r.map_id1 and r.map_id2) as Outbound,
(select group_concat(descrp) from stops where route_id=r.route_id
and map_id between r.map_id3 and r.map_id4) as Inbound
from routes r join stops s on (r.route_id=s.route_id)
group by r.stud_id