Solved

analytics query - oracle sql

Posted on 2014-09-05
9
217 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
error doing substr 3 36
Changing the Schema for all Tables in a Tables - MS SQL Server 2008 R2. 3 32
error in oracle form 11 27
calculate days away 11 27
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

749 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