Request 4 - CANADIAN STATE OF DESIGN FLEET STATISTICS

See PBI-153094 CAWIS Report - CANADIAN FLEET NUMBERS AND FLIGHT HOURS

See also RDIMS #15130178

Task: Refresh Canadian Manufacturer Fleet numbers and Total Fleet hours (requested by Chief AARDG)
This report would likely be required, yearly

Open

SECTION 1 - ORIGINAL REPORT

For the following Manufacturers :

Bombardier and Former Bombardier

Total 4948 A/C among which 600 are operated in Canada.  (12%)

Viking

Total 1850 A/C among which 668 are operated in Canada.  (36%)

Bell

Total 9400 A/C among which 811 are operated in Canada.  (8.6%)

Pratt & Whitney Canada (as of April 4/20 Reliability Analysis & Census Report)

Total 56,857 engines (flying population, out of 74,269 engines installed) among which 3237 are operated in Canada. (5.7%)

Open

SECTION 2 - DATA NOTES

Pls note:

1. We have no way of determining “Total Global Fleet”  numbers from the data in CAWIS.  Many newly built aircraft are sold and exported without ever being placed on the Canadian register, so CAWIS has no record of these instances.  Also many historical elements from before 2005 were not retained during the CAWIS data conversion.

2. BOMBARDIER/CANADAIR/VIKING/MHI/AIRBUS CANADA  “marketing designations” do not exist anywhere on the CAWIS tables (CAWIS DESIGNATIONS used for querying are IN RED)

3. I cannot determine from the original report, if the figures were derived from what is currently on the Canadian register -or- from ALL aircraft of a given model found in CAWIS (y04 table)  – thus a breakdown & total of all figures for REGISTERED/DEREGISTERED has been provided for each  (I have no recollection of generating the original report) 

4. Most of these figures do not reconcile with those in the original report (ie: they should be close, but slightly larger)  Would be fine with sharing a screen with any interested parties to show how they were derived.

SECTION 3 - EXTRACTION CODE

Generic code for performing this task - the user needs to swap in each model, for each row

3A - BOMBARDIER

/* BOMBARDIER / DEHAVILLAND / VIKING / MHI / AIRBUS CANADA / VIKING

NOTE : Manufacturer ids are not specified in the Bombardier queries because the models are known to be unique to those listed above

*/

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') DT,

(SELECT COUNT(*) FROM Y04_AIRCRAFT_CONFIGURATION Y04, Y93_CCARCS_PREVIOUS_DAY Y93
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MODEL_ID LIKE 'CL215%'
AND Y93.AIRCRAFT_ID = Y04.AIRCRAFT_ID ) REGD_AC ,

/* CURRENTLY REGISTERED AIRCRAFT FOR THIS MODEL TYPE */

(SELECT COUNT(*) FROM Y04_AIRCRAFT_CONFIGURATION Y04
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MODEL_ID LIKE 'CL215%'
AND Y04.AIRCRAFT_ID NOT IN (SELECT AIRCRAFT_ID FROM Y93_CCARCS_PREVIOUS_DAY) )
DEREGD_AC ,

/* DE-REGISTERED AIRCRAFT FOR THIS MODEL TYPE */

(SELECT COUNT(*) FROM Y04_AIRCRAFT_CONFIGURATION Y04
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MODEL_ID LIKE 'CL215%' ) TOTAL_AC ,

/* ALL FOR THIS MODEL TYPE */

--- ##############

(SELECT ROUND(SUM(Q02.HOURS_FLOWN_NBR))
FROM Y04_AIRCRAFT_CONFIGURATION Y04, Y93_CCARCS_PREVIOUS_DAY Y93,
Q02_AIRCRFT_LCNS_TYP_HR Q02
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MODEL_ID LIKE 'CL215%'
AND Y93.AIRCRAFT_ID = Y04.AIRCRAFT_ID
AND Q02.FLYING_HOURS_TYPE_CD = 1
AND Q02.YEAR_CALENDAR_NBR = (SELECT MAX(X.YEAR_CALENDAR_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Q02.AIRCRAFT_ID)
AND Q02.AIRCRAFT_ID = Y04.AIRCRAFT_ID )
TSN_REGD,

/* TOTAL TIME SINCE NEW FOR REGISTERED AIRCRAFT FOR THIS MODEL TYPE */

(SELECT ROUND(SUM(Q02.HOURS_FLOWN_NBR))
FROM Y04_AIRCRAFT_CONFIGURATION Y04,
Q02_AIRCRFT_LCNS_TYP_HR Q02
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MODEL_ID LIKE 'CL215%'
AND Y04.AIRCRAFT_ID NOT IN (SELECT AIRCRAFT_ID FROM Y93_CCARCS_PREVIOUS_DAY)
AND Q02.FLYING_HOURS_TYPE_CD = 1
AND Q02.YEAR_CALENDAR_NBR = (SELECT MAX(X.YEAR_CALENDAR_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Q02.AIRCRAFT_ID)
AND Q02.AIRCRAFT_ID = Y04.AIRCRAFT_ID )
TSN_DEREGD,

/* TOTAL TIME SINCE NEW FOR DE-REGISTERED AIRCRAFT FOR THIS MODEL TYPE */

(SELECT ROUND(SUM(Q02.HOURS_FLOWN_NBR))
FROM Y04_AIRCRAFT_CONFIGURATION Y04 ,
Q02_AIRCRFT_LCNS_TYP_HR Q02
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MODEL_ID LIKE 'CL215%'
AND Q02.FLYING_HOURS_TYPE_CD = 1
AND Q02.YEAR_CALENDAR_NBR = (SELECT MAX(X.YEAR_CALENDAR_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Q02.AIRCRAFT_ID)
AND Q02.AIRCRAFT_ID = Y04.AIRCRAFT_ID )
TSN_TOTAL,

/* TOTAL TIME SINCE NEW ALL AIRCRAFT FOR THIS MODEL TYPE */

ROUND ((SELECT SUM(Q02.HOURS_FLOWN_NBR)
FROM Y04_AIRCRAFT_CONFIGURATION Y04, Y93_CCARCS_PREVIOUS_DAY Y93,
Q02_AIRCRFT_LCNS_TYP_HR Q02
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MODEL_ID LIKE 'CL215%'
AND Y93.AIRCRAFT_ID = Y04.AIRCRAFT_ID
AND Q02.FLYING_HOURS_TYPE_CD = 1
AND Q02.YEAR_CALENDAR_NBR = (SELECT MAX(X.YEAR_CALENDAR_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Q02.AIRCRAFT_ID)
AND Q02.AIRCRAFT_ID = Y04.AIRCRAFT_ID ) /
(SELECT COUNT(Y04.AIRCRAFT_ID) FROM Y04_AIRCRAFT_CONFIGURATION Y04, Y93_CCARCS_PREVIOUS_DAY Y93
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MODEL_ID LIKE 'CL215%'
AND Y93.AIRCRAFT_ID = Y04.AIRCRAFT_ID ))
AVGHRS_REGD,

/* AVERAGE TOTAL TIME SINCE NEW FOR REGISTERED AIRCRAFT FOR THIS MODEL TYPE */

ROUND ((SELECT SUM(Q02.HOURS_FLOWN_NBR)
FROM Y04_AIRCRAFT_CONFIGURATION Y04 ,
Q02_AIRCRFT_LCNS_TYP_HR Q02
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MODEL_ID LIKE 'CL215%'
AND Q02.FLYING_HOURS_TYPE_CD = 1
AND Q02.YEAR_CALENDAR_NBR = (SELECT MAX(X.YEAR_CALENDAR_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Q02.AIRCRAFT_ID)
AND Q02.AIRCRAFT_ID = Y04.AIRCRAFT_ID ) /
(SELECT COUNT(Y04.AIRCRAFT_ID) FROM Y04_AIRCRAFT_CONFIGURATION Y04
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MODEL_ID LIKE 'CL215%' ) )
AVGHRS_ALL

/* AVERAGE TOTAL TIME SINCE NEW FOR ALL AIRCRAFT FOR THIS MODEL TYPE */

FROM DUAL ;

3B - BELL HELICOPTER

/* BELL HELICOPTER - MANUFACTURER_ID = 266 */

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') DT,

(SELECT COUNT(*) FROM Y04_AIRCRAFT_CONFIGURATION Y04, Y93_CCARCS_PREVIOUS_DAY Y93
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MANUFACTURER_ID = 266 AND (Y04.MODEL_ID LIKE '%505%' )
AND Y93.AIRCRAFT_ID = Y04.AIRCRAFT_ID ) REGD_AC ,

(SELECT COUNT(*) FROM Y04_AIRCRAFT_CONFIGURATION Y04
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MANUFACTURER_ID = 266 AND (Y04.MODEL_ID LIKE '%505%' )
AND Y04.AIRCRAFT_ID NOT IN (SELECT AIRCRAFT_ID FROM Y93_CCARCS_PREVIOUS_DAY) )
DEREGD_AC ,

(SELECT COUNT(*) FROM Y04_AIRCRAFT_CONFIGURATION Y04
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MANUFACTURER_ID = 266 AND (Y04.MODEL_ID LIKE '%505%' ) ) TOTAL_AC ,

(SELECT ROUND(SUM(Q02.HOURS_FLOWN_NBR))
FROM Y04_AIRCRAFT_CONFIGURATION Y04, Y93_CCARCS_PREVIOUS_DAY Y93,
Q02_AIRCRFT_LCNS_TYP_HR Q02
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MANUFACTURER_ID = 266 AND (Y04.MODEL_ID LIKE '%505%' )
AND Y93.AIRCRAFT_ID = Y04.AIRCRAFT_ID
AND Q02.FLYING_HOURS_TYPE_CD = 1
AND Q02.YEAR_CALENDAR_NBR = (SELECT MAX(X.YEAR_CALENDAR_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Q02.AIRCRAFT_ID)
AND Q02.AIRCRAFT_ID = Y04.AIRCRAFT_ID )
TSN_REGD,

(SELECT ROUND(SUM(Q02.HOURS_FLOWN_NBR))
FROM Y04_AIRCRAFT_CONFIGURATION Y04,
Q02_AIRCRFT_LCNS_TYP_HR Q02
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MANUFACTURER_ID = 266 AND (Y04.MODEL_ID LIKE '%505%' )
AND Y04.AIRCRAFT_ID NOT IN (SELECT AIRCRAFT_ID FROM Y93_CCARCS_PREVIOUS_DAY)
AND Q02.FLYING_HOURS_TYPE_CD = 1
AND Q02.YEAR_CALENDAR_NBR = (SELECT MAX(X.YEAR_CALENDAR_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Q02.AIRCRAFT_ID)
AND Q02.AIRCRAFT_ID = Y04.AIRCRAFT_ID )
TSN_DEREGD,

(SELECT ROUND(SUM(Q02.HOURS_FLOWN_NBR))
FROM Y04_AIRCRAFT_CONFIGURATION Y04 ,
Q02_AIRCRFT_LCNS_TYP_HR Q02
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MANUFACTURER_ID = 266 AND (Y04.MODEL_ID LIKE '%505%' )
AND Q02.FLYING_HOURS_TYPE_CD = 1
AND Q02.YEAR_CALENDAR_NBR = (SELECT MAX(X.YEAR_CALENDAR_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Q02.AIRCRAFT_ID)
AND Q02.AIRCRAFT_ID = Y04.AIRCRAFT_ID )
TSN_TOTAL,

ROUND ((SELECT SUM(Q02.HOURS_FLOWN_NBR)
FROM Y04_AIRCRAFT_CONFIGURATION Y04, Y93_CCARCS_PREVIOUS_DAY Y93,
Q02_AIRCRFT_LCNS_TYP_HR Q02
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MANUFACTURER_ID = 266 AND (Y04.MODEL_ID LIKE '%505%' )
AND Y93.AIRCRAFT_ID = Y04.AIRCRAFT_ID
AND Q02.FLYING_HOURS_TYPE_CD = 1
AND Q02.YEAR_CALENDAR_NBR = (SELECT MAX(X.YEAR_CALENDAR_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Q02.AIRCRAFT_ID)
AND Q02.AIRCRAFT_ID = Y04.AIRCRAFT_ID ) /
(SELECT COUNT(Y04.AIRCRAFT_ID) FROM Y04_AIRCRAFT_CONFIGURATION Y04, Y93_CCARCS_PREVIOUS_DAY Y93
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MANUFACTURER_ID = 266 AND (Y04.MODEL_ID LIKE '%505%' )
AND Y93.AIRCRAFT_ID = Y04.AIRCRAFT_ID ))
AVGHRS_REGD,

ROUND ((SELECT SUM(Q02.HOURS_FLOWN_NBR)
FROM Y04_AIRCRAFT_CONFIGURATION Y04 ,
Q02_AIRCRFT_LCNS_TYP_HR Q02
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MANUFACTURER_ID = 266 AND (Y04.MODEL_ID LIKE '%505%' )
AND Q02.FLYING_HOURS_TYPE_CD = 1
AND Q02.YEAR_CALENDAR_NBR = (SELECT MAX(X.YEAR_CALENDAR_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Q02.AIRCRAFT_ID)
AND Q02.AIRCRAFT_ID = Y04.AIRCRAFT_ID ) /
(SELECT COUNT(Y04.AIRCRAFT_ID) FROM Y04_AIRCRAFT_CONFIGURATION Y04
WHERE Y04.PRODUCT_TYPE_CD = 'A'
AND Y04.MANUFACTURER_ID = 266 AND (Y04.MODEL_ID LIKE '%505%' )) )
AVGHRS_ALL

FROM DUAL ;

3C - PRATT AND WHITNEY - CANADA ENGINES

/* NOTE : THIS REPORT COUNTS INDIVIDUAL PWC ENGINES  - ONE AIRCRAFT MAY HAVE SEVERAL , Y04 Ordinal Position number would need to be added to each query to count aircraft, instead of engines */

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') DT,

(SELECT COUNT(*) FROM Y04_AIRCRAFT_CONFIGURATION Y04, Y93_CCARCS_PREVIOUS_DAY Y93
WHERE Y04.PRODUCT_TYPE_CD = 'E'
AND Y04.MANUFACTURER_ID = 1469
AND (DATA_COMPRESSION(Y04.MODEL_ID) LIKE '%PW8%' )
AND Y93.AIRCRAFT_ID = Y04.AIRCRAFT_ID ) REGD_AC ,

(SELECT COUNT(*) FROM Y04_AIRCRAFT_CONFIGURATION Y04
WHERE Y04.PRODUCT_TYPE_CD = 'E'
AND Y04.MANUFACTURER_ID = 1469
AND (DATA_COMPRESSION(Y04.MODEL_ID) LIKE '%PW8%' )
AND Y04.AIRCRAFT_ID NOT IN (SELECT AIRCRAFT_ID FROM Y93_CCARCS_PREVIOUS_DAY) )
DEREGD_AC ,

(SELECT COUNT(*) FROM Y04_AIRCRAFT_CONFIGURATION Y04
WHERE Y04.PRODUCT_TYPE_CD = 'E'
AND Y04.MANUFACTURER_ID = 1469
AND (DATA_COMPRESSION(Y04.MODEL_ID) LIKE '%PW8%' ) ) TOTAL_AC ,

(SELECT ROUND(SUM(Q02.HOURS_FLOWN_NBR))
FROM Y04_AIRCRAFT_CONFIGURATION Y04, Y93_CCARCS_PREVIOUS_DAY Y93,
Q02_AIRCRFT_LCNS_TYP_HR Q02
WHERE Y04.PRODUCT_TYPE_CD = 'E'
AND Y04.MANUFACTURER_ID = 1469
AND (DATA_COMPRESSION(Y04.MODEL_ID) LIKE '%PW8%' )
AND Y93.AIRCRAFT_ID = Y04.AIRCRAFT_ID
AND Q02.FLYING_HOURS_TYPE_CD = 1
AND Q02.YEAR_CALENDAR_NBR = (SELECT MAX(X.YEAR_CALENDAR_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Q02.AIRCRAFT_ID)
AND Q02.AIRCRAFT_ID = Y04.AIRCRAFT_ID )
TSN_REGD,

(SELECT ROUND(SUM(Q02.HOURS_FLOWN_NBR))
FROM Y04_AIRCRAFT_CONFIGURATION Y04,
Q02_AIRCRFT_LCNS_TYP_HR Q02
WHERE Y04.PRODUCT_TYPE_CD = 'E'
AND Y04.MANUFACTURER_ID = 1469
AND (DATA_COMPRESSION(Y04.MODEL_ID) LIKE '%PW8%' )
AND Y04.AIRCRAFT_ID NOT IN (SELECT AIRCRAFT_ID FROM Y93_CCARCS_PREVIOUS_DAY)
AND Q02.FLYING_HOURS_TYPE_CD = 1
AND Q02.YEAR_CALENDAR_NBR = (SELECT MAX(X.YEAR_CALENDAR_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Q02.AIRCRAFT_ID)
AND Q02.AIRCRAFT_ID = Y04.AIRCRAFT_ID )
TSN_DEREGD,

(SELECT ROUND(SUM(Q02.HOURS_FLOWN_NBR))
FROM Y04_AIRCRAFT_CONFIGURATION Y04 ,
Q02_AIRCRFT_LCNS_TYP_HR Q02
WHERE Y04.PRODUCT_TYPE_CD = 'E'
AND Y04.MANUFACTURER_ID = 1469
AND (DATA_COMPRESSION(Y04.MODEL_ID) LIKE '%PW8%' )
AND Q02.FLYING_HOURS_TYPE_CD = 1
AND Q02.YEAR_CALENDAR_NBR = (SELECT MAX(X.YEAR_CALENDAR_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Q02.AIRCRAFT_ID)
AND Q02.AIRCRAFT_ID = Y04.AIRCRAFT_ID )
TSN_TOTAL

FROM DUAL ;

SECTION 4 - REPORT OUTPUT

NOTE: COLUMN B - TOTAL FLEET is from the original report - CAWIS cannot re-produce these figures

Bombardier and Former Bombardier

Viking

Bell Helicopter

Pratt & Whitney Canada Engines