[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Airplane ticket table layout

Posted on 2004-09-16
4
Medium Priority
?
978 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
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month20 days, 13 hours left to enroll

865 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