Solved

Airplane ticket table layout

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

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…
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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.

747 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

12 Experts available now in Live!

Get 1:1 Help Now