MDS-DW-AviationProject/kpi_calculations.sql at main · AimbotParce/MDS-DW-AviationProject · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
a)Give me FH and TO per aircraft (also per model) per day (also per month and per year).


Select Aircraft,
        DateLevel,
        sum(flightDuration) AS FH
        sum(case when flightDuration > 0 then 1 else 0 end ) AS TO
From Flight
join FlightDim
Group by Aircraft,
        DateLevel


b)
Aircraft Days Out of Service (ADOS)
Aircraft Days Out of Service Scheduled (ADOSS)
Aircraft Days Out of Service Unscheduled (ADOSU)

select Aircraft,
        startMaintenance(Month) as DateLevel
        sum(duration) as ADOS
        sum(case when SCHEDULED then duration else 0 end) as ADOSS
        sum(case when UNSCHEDULED then duration else 0 end) as ADOSU
From maintemanceFact
group by aircraft,
        DateLevel


Delay Rate (DYR)
CNR
TDR
ADD

select AIRCRAFT,
        startFlight(Month) as DateLevel,
        count(case when delay > 15 and < 6*60 then 1 else 0 end)/
            count(case when flightDuration > 0 then 1 else 0 end )*100 as DYR

        count(case when flightDuration = 0 then 1 else 0 end)/
            count(case when flightDuration > 0 then 1 else 0 end )*100 as CNR

        count(case when flightDuration > 0 and delay = 0 then 1 else 0 end)/
            count(case when flightDuration > 0 then 1 else 0 end )*100 as TDR

        sum(case when delay > 15 and < 6*60 then duration else 0 end)/
            count(case when delay > 15 and < 6*60 then 1 else 0 end )*100 as ADD

FROM From Flight
join FlightDim
group by AIRCRAFT,
        DateLevel


C)Give me the RRh, RRc, PRRh, PRRc, MRRh and MRRc per aircraft (also per model and manufacturer) per month (also per year).


Select
        Aircraft,
        startFlight(Month) as DateLevel,
        sum(entryLogbookCount)/sum(durationFlight) as RRH,
        sum(entryLogbookCount)/(case when flightDuration > 0 then 1 else 0 end) as RRc

        sum(case when ReporteurType = Pilot then entryLogbookCount end)/sum(durationFlight) as PRRH,
        sum(case when ReporteurType = Pilot then entryLogbookCount end)/(case when flightDuration > 0
                then 1 else 0 end) as PRRc


        sum(case when ReporteurType = Maintenance then entryLogbookCount end)/sum(durationFlight) as MRRH,
        sum(case when ReporteurType = Maintenance then entryLogbookCount end)/(case when flightDuration > 0
                then 1 else 0 end) as MRRc

from FactFlight a
inner join FlightDim b
    on a.FlightID = B.FlightID
Inner join FactLogBook c
    on a.FlightID = c.ReportID
Inner join DimReporteurID D
    on c.ReporteurID = d.reporteurID
Group by Aircraft,
            DateLevel


D)
Select  ReporteurAirportID,
        Aircraft,
        startFlight(Month) as DateLevel,


        sum(case when ReporteurType = Maintenance then entryLogbookCount end)/sum(durationFlight) as MRRH,
        sum(case when ReporteurType = Maintenance then entryLogbookCount end)/(case when flightDuration > 0
                then 1 else 0 end) as MRRc

from FactFlight a
inner join FlightDim b
    on a.FlightID = B.FlightID
Inner join FactLogBook c
    on a.FlightID = c.ReportID
Inner join DimReporteurID D
    on c.ReporteurID = d.reporteurID
Group by ReporteurAirportID,
            Aircraft,
            DateLevel