Solved

Airplane ticket table layout

Posted on 2004-09-16
4
875 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 100

Accepted Solution

by:
mlmcc earned 230 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 20 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 100

Expert Comment

by:mlmcc
ID: 12129630
Glad I could help

mlmcc
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

947 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

23 Experts available now in Live!

Get 1:1 Help Now