Solved

analytics query - oracle sql

Posted on 2014-09-05
9
210 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
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 73

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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

920 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

17 Experts available now in Live!

Get 1:1 Help Now