Solved

Airplane ticket table layout

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Ignore parameter if no value entered 22 61
Crystal Reports - Trim Data 7 51
Crystal Reports last day of month 2 54
Date Formula: Last business day - 2 of prior month 12 54
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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 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