Solved

analytics query - oracle sql

Posted on 2014-09-05
9
218 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 40305912
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
ID: 40305947
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 167 total points
ID: 40305995
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Assisted Solution

by:mankowitz
mankowitz earned 333 total points
ID: 40306002
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40306016
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
ID: 40306062
That's odd. It works for me. See http://sqlfiddle.com/#!4/12103f/12
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40306067
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
ID: 40306081
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
ID: 40306321
this is spot on - thanks chaps
0

Featured Post

Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

688 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