Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Airplane ticket table layout

Posted on 2004-09-16
4
Medium Priority
?
970 Views
Last Modified: 2011-10-03
I have a table with the following layout:
SQL> desc fltdestinations;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 FLTDESTID                                 NOT NULL NUMBER(10)
 FLTID                                     NOT NULL NUMBER(10)
 STOP                                      NOT NULL NUMBER(10)
 AIRPORTCODE                               NOT NULL VARCHAR2(4)
 ARRIVALTIME                                        DATE
 DEPARTURETIME                                      DATE
 RON                                       NOT NULL VARCHAR2(1)
 NUMNIGHTS                                          NUMBER(2)

With records like:
FLTDESTID,FLTID,STOP,AIRPORTCODE,ARRIVALTIME,DEPARTURETIME,RON,NUMNIGHTS
101,1,1,Cheyenne,,9:00am,N,0
102,1,2,Casper,10:00am,11:00am,N,0
103,1,3,SF,3:00pm,8:00am,Y,1
104,1,4,Cheyenne,12:00 pm,,N,0

I would like to create a Crystal Reports report that will look like:
Flight: 1
From - To                 Departure Time   Arrival time   RON  NumNights
Cheyenne - Casper    9:00 am             10:00 am       N       0
Casper - SF               11:00 am            3:00 pm        Y       1
SF - Cheyenne           8:00 am             12:00 PM       N       0

The starting city should not have an arrival time and the last city cannot have a departure time.  I need to be able to remove and add cities at anytime in any location of the flight, stop is how the order of cities is decided.

How do I create the report?
0
Comment
Question by:wykzimme
[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
  • 2
4 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 920 total points
ID: 12078413
An interesting question.

>> I need to be able to remove and add cities at anytime in any location of the flight,
Why are you asking this in the Crystal Reports forum?  You problem is database related not reporting related.

What is your problem building the report?

SELECT D.fltid, D.stop, D.airportcode, D.departuretime, D.ron, D.numnights,
             A.airportcode, A.arrivaltime
FROM fltdestinations D LEFT OUTER JOIN fltdestinations A ON
D.fltid  = A.fltid AND D.stop + 1 = A.stop
GROUP BY D.fltid
ORDER BY D.stop

That should get you the records

In the report
Create a group - FLTID
Sort by - STOP

Add the fields to the detail section

mlmcc

0
 
LVL 28

Assisted Solution

by:bdreed35
bdreed35 earned 80 total points
ID: 12078614
Crystal Reports is read only.  You need some other interface to change the underlying data.
0
 

Author Comment

by:wykzimme
ID: 12128504
My solution was:

CREATE OR REPLACE VIEW FLIGHTRAC.REQUESTSLEGS
(REQID, DEPARTURECITY, DEPARTURESTATE, ARRIVALCITY, ARRIVALSTATE,
 DEPTIME, ARRIVALTIME, DEPTIMEZONE, PAXPU, PAXDO,
 ARTIMEZONE, RON, NUMNIGHTS)
AS
SELECT d.REQID,d.DESCITY as departurecity,d.STATE_ABBREV as departurestate,a.descity as arrivalcity,a.STATE_ABBREV as arrivalstate, d.desdeptime as deptime,a.desarvtime as arrivaltime,d.TIMEZONEID as deptimezone,d.NUMPUPASSENGERS as paxpu,a.NUMDOPASSENGERS as paxdo,a.TIMEZONEID as artimezone,a.ron,a.NUMNIGHTS
FROM reqdestinations D, reqdestinations A
where D.reqid  = A.reqid
AND D.desid + 1 = A.desid
ORDER BY d.DESID;
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 12129630
Glad I could help

mlmcc
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

618 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