Solved

analytics query - oracle sql

Posted on 2014-09-05
9
206 Views
Last Modified: 2014-09-05
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
0
Comment
Question by:tonMachine100
  • 5
  • 3
9 Comments
 
LVL 24

Expert Comment

by:mankowitz
Comment Utility
Assuming that map_id is always ordinal, you want something like this:

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
0
 
LVL 24

Expert Comment

by:mankowitz
Comment Utility
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 167 total points
Comment Utility
try this...  

similar idea to above except I removed extraneous join to stops, removed illegal group by and changed group_concat which isn't an oracle function (or at least not one built in ) to the listagg function which comes with 11gR2 and above


  SELECT r.*,
         (SELECT LISTAGG(descrp, ',') WITHIN GROUP (ORDER BY step)
            FROM stops s
           WHERE s.route_id = r.route_id AND s.map_id between r.map_id1 and r.map_id2)
             outbound,
         (SELECT LISTAGG(descrp, ',') WITHIN GROUP (ORDER BY step)
            FROM stops s
           WHERE s.route_id = r.route_id AND s.map_id between r.map_id3 and map_id4)
             inbound
    FROM routes r
ORDER BY route_id, stud_id

I tested this using your sample data and it produced the expected results
0
 
LVL 24

Assisted Solution

by:mankowitz
mankowitz earned 333 total points
Comment Utility
D'oh,
beat me to it. I realized that it was oracle, not mysql.

SELECT r.route_id, 
       r.stud_id,
       r.stud_name,
       r.map_id1,
       r.map_id2,
       r.map_id3,
       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   

Open in new window

0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
that query still has an extraneous join  and illegal grouping - it doesn't execute
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 333 total points
Comment Utility
That's odd. It works for me. See http://sqlfiddle.com/#!4/12103f/12
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
 
LVL 24

Expert Comment

by:mankowitz
Comment Utility
yes, I posted it and then realized that I had copy/pasted the wrong version, so I updated it.
0
 

Author Closing Comment

by:tonMachine100
Comment Utility
this is spot on - thanks chaps
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now