SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Multiple Count aggregates from different sources grouped by Year

RoxyrollersAug 7 2012 — edited Aug 8 2012

I have a total of 4 tables and they deal with Frequent Flyers. My goal is to find the Number of Frequent Flyers in a given period of time dating back to 1990 till date.

I need to create a Report where I am listing the Number of Frequent Flyers based on when they enrolled, took a survey and took their flights. The test data is only a small sample of data. Also, some Frequent Flyers may have Enrolled and taken a Survey but never flown like Freq Flyer 1004 and some customers might have enrolled but never taken the survey like Freq Flyer No 1010. The report needs to be by Year (1990 - Sysdate).

Year       Enrolled       Took Survey       Flights Flown
=========================================================
1990             99                99                  99
1991             99                99                  99 
1992             99                99                  99
...
2010             99                99                  99
2011             99                99                  99
2012             99                99                  99

Scripts for Tables and Inserts:

Table #1: Frequent Flyer Master table

  CREATE TABLE FREQ_FLYER
   (	"FREQ_FLYER_NO" VARCHAR2(4 BYTE), 
	"FREQ_FLYER_LAST_NAME" VARCHAR2(30 BYTE), 
	"FREQ_FLYER_FIRST_NAME" VARCHAR2(30 BYTE)
   );
REM INSERTING into FREQ_FLYER
SET DEFINE OFF;
Insert into FREQ_FLYER (FREQ_FLYER_NO,FREQ_FLYER_LAST_NAME,FREQ_FLYER_FIRST_NAME) values ('1001','Taylor','David');
Insert into FREQ_FLYER (FREQ_FLYER_NO,FREQ_FLYER_LAST_NAME,FREQ_FLYER_FIRST_NAME) values ('1002','Green','Robert');
Insert into FREQ_FLYER (FREQ_FLYER_NO,FREQ_FLYER_LAST_NAME,FREQ_FLYER_FIRST_NAME) values ('1003','McWilliams','Cynthia');
Insert into FREQ_FLYER (FREQ_FLYER_NO,FREQ_FLYER_LAST_NAME,FREQ_FLYER_FIRST_NAME) values ('1004','Poppins','Mary');
Insert into FREQ_FLYER (FREQ_FLYER_NO,FREQ_FLYER_LAST_NAME,FREQ_FLYER_FIRST_NAME) values ('1005','Longnose','Pinochio');
Insert into FREQ_FLYER (FREQ_FLYER_NO,FREQ_FLYER_LAST_NAME,FREQ_FLYER_FIRST_NAME) values ('1006','Brown','Edward');
Insert into FREQ_FLYER (FREQ_FLYER_NO,FREQ_FLYER_LAST_NAME,FREQ_FLYER_FIRST_NAME) values ('1007','Menace','Dennis');
Insert into FREQ_FLYER (FREQ_FLYER_NO,FREQ_FLYER_LAST_NAME,FREQ_FLYER_FIRST_NAME) values ('1008','Black','Monica');
Insert into FREQ_FLYER (FREQ_FLYER_NO,FREQ_FLYER_LAST_NAME,FREQ_FLYER_FIRST_NAME) values ('1009','Dombrowski','Tom');
Insert into FREQ_FLYER (FREQ_FLYER_NO,FREQ_FLYER_LAST_NAME,FREQ_FLYER_FIRST_NAME) values ('1010','Brown','Craig');
Insert into FREQ_FLYER (FREQ_FLYER_NO,FREQ_FLYER_LAST_NAME,FREQ_FLYER_FIRST_NAME) values ('1011','Hernandez','Elsa');
Insert into FREQ_FLYER (FREQ_FLYER_NO,FREQ_FLYER_LAST_NAME,FREQ_FLYER_FIRST_NAME) values ('1012','Williams','Brian');
--------------------------------------------------------
--  Constraints for Table FREQ_FLYER
--------------------------------------------------------

  ALTER TABLE FREQ_FLYER MODIFY ("FREQ_FLYER_NO" NOT NULL ENABLE);

Table# 2: Enrollment

  CREATE TABLE FREQ_FLYER_ENROLLMENT
   (	"FREQ_FLYER_NO" VARCHAR2(4 BYTE), 
	"START_DATE" DATE, 
	"END_DATE" DATE
   );
REM INSERTING into FREQ_FLYER_ENROLLMENT
SET DEFINE OFF;
Insert into FREQ_FLYER_ENROLLMENT (FREQ_FLYER_NO,START_DATE,END_DATE) values ('1001',to_date('12-JAN-90','DD-MON-RR'),null);
Insert into FREQ_FLYER_ENROLLMENT (FREQ_FLYER_NO,START_DATE,END_DATE) values ('1002',to_date('17-JAN-90','DD-MON-RR'),null);
Insert into FREQ_FLYER_ENROLLMENT (FREQ_FLYER_NO,START_DATE,END_DATE) values ('1003',to_date('31-DEC-95','DD-MON-RR'),null);
Insert into FREQ_FLYER_ENROLLMENT (FREQ_FLYER_NO,START_DATE,END_DATE) values ('1004',to_date('30-DEC-95','DD-MON-RR'),null);
Insert into FREQ_FLYER_ENROLLMENT (FREQ_FLYER_NO,START_DATE,END_DATE) values ('1005',to_date('01-JAN-00','DD-MON-RR'),null);
Insert into FREQ_FLYER_ENROLLMENT (FREQ_FLYER_NO,START_DATE,END_DATE) values ('1006',to_date('02-JAN-00','DD-MON-RR'),null);
Insert into FREQ_FLYER_ENROLLMENT (FREQ_FLYER_NO,START_DATE,END_DATE) values ('1007',to_date('31-JAN-00','DD-MON-RR'),null);
Insert into FREQ_FLYER_ENROLLMENT (FREQ_FLYER_NO,START_DATE,END_DATE) values ('1008',to_date('30-OCT-11','DD-MON-RR'),null);
Insert into FREQ_FLYER_ENROLLMENT (FREQ_FLYER_NO,START_DATE,END_DATE) values ('1009',to_date('01-MAR-12','DD-MON-RR'),null);
Insert into FREQ_FLYER_ENROLLMENT (FREQ_FLYER_NO,START_DATE,END_DATE) values ('1010',to_date('31-MAR-12','DD-MON-RR'),null);

--------------------------------------------------------
--  Constraints for Table FREQ_FLYER_ENROLLMENT
--------------------------------------------------------
  ALTER TABLE FREQ_FLYER_ENROLLMENT MODIFY ("FREQ_FLYER_NO" NOT NULL ENABLE);
  ALTER TABLE FREQ_FLYER_ENROLLMENT MODIFY ("START_DATE" NOT NULL ENABLE);

Table #3: Survey

  CREATE TABLE FREQ_FLYER_SURVEY
   (	"FREQ_FLYER_NO" VARCHAR2(4 BYTE), 
	"SURVEY_DATE" DATE
   );

SET DEFINE OFF;
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1001',to_date('01-JAN-91','DD-MON-RR'));
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1002',to_date('01-FEB-91','DD-MON-RR'));
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1003',to_date('01-JAN-96','DD-MON-RR'));
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1003',to_date('01-JAN-97','DD-MON-RR'));
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1003',to_date('01-JAN-98','DD-MON-RR'));
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1004',to_date('31-DEC-95','DD-MON-RR'));
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1004',to_date('12-FEB-11','DD-MON-RR'));
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1005',to_date('12-MAR-11','DD-MON-RR'));
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1005',to_date('12-APR-12','DD-MON-RR'));
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1006',to_date('10-APR-10','DD-MON-RR'));
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1007',to_date('10-APR-10','DD-MON-RR'));
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1008',to_date('31-JUL-12','DD-MON-RR'));
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1009',to_date('31-JUL-12','DD-MON-RR'));
Insert into FREQ_FLYER_SURVEY (FREQ_FLYER_NO,SURVEY_DATE) values ('1009',to_date('02-AUG-12','DD-MON-RR'));
--------------------------------------------------------
--  Constraints for Table FREQ_FLYER_SURVEY
--------------------------------------------------------

  ALTER TABLE FREQ_FLYER_SURVEY MODIFY ("FREQ_FLYER_NO" NOT NULL ENABLE);

Table# 4: Flights

  CREATE TABLE FREQ_FLYER_FLIGHTS
   (	"FREQ_FLYER_NO" VARCHAR2(4 BYTE), 
	"FLT_NO" VARCHAR2(4 BYTE), 
	"FLT_DATE" DATE
   );
REM INSERTING into FREQ_FLYER_FLIGHTS
SET DEFINE OFF;
Insert into FREQ_FLYER_FLIGHTS (FREQ_FLYER_NO,FLT_NO,FLT_DATE) values ('1001','1234',to_date('01-JAN-00','DD-MON-RR'));
Insert into FREQ_FLYER_FLIGHTS (FREQ_FLYER_NO,FLT_NO,FLT_DATE) values ('1001','1234',to_date('01-JAN-12','DD-MON-RR'));
Insert into FREQ_FLYER_FLIGHTS (FREQ_FLYER_NO,FLT_NO,FLT_DATE) values ('1001','2345',to_date('01-AUG-12','DD-MON-RR'));
Insert into FREQ_FLYER_FLIGHTS (FREQ_FLYER_NO,FLT_NO,FLT_DATE) values ('1002','1234',to_date('01-FEB-95','DD-MON-RR'));
Insert into FREQ_FLYER_FLIGHTS (FREQ_FLYER_NO,FLT_NO,FLT_DATE) values ('1002','2345',to_date('01-AUG-12','DD-MON-RR'));
Insert into FREQ_FLYER_FLIGHTS (FREQ_FLYER_NO,FLT_NO,FLT_DATE) values ('1002','2346',to_date('04-AUG-12','DD-MON-RR'));
Insert into FREQ_FLYER_FLIGHTS (FREQ_FLYER_NO,FLT_NO,FLT_DATE) values ('1003','1234',to_date('01-DEC-00','DD-MON-RR'));
Insert into FREQ_FLYER_FLIGHTS (FREQ_FLYER_NO,FLT_NO,FLT_DATE) values ('1003','2346',to_date('01-JAN-01','DD-MON-RR'));
Insert into FREQ_FLYER_FLIGHTS (FREQ_FLYER_NO,FLT_NO,FLT_DATE) values ('1010','1234',to_date('01-JUL-12','DD-MON-RR'));
--------------------------------------------------------
--  Constraints for Table FREQ_FLYER_FLIGHTS
--------------------------------------------------------

  ALTER TABLE FREQ_FLYER_FLIGHTS MODIFY ("FREQ_FLYER_NO" NOT NULL ENABLE);
  ALTER TABLE FREQ_FLYER_FLIGHTS MODIFY ("FLT_NO" NOT NULL ENABLE);

Your help or suggestions are welcome.

Edited by: Roxyrollers on Aug 7, 2012 5:39 PM

Edited by: Roxyrollers on Aug 8, 2012 8:35 AM

This post has been answered by Frank Kulash on Aug 8 2012

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 5 2012

Added on Aug 7 2012

8 comments

312 views