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
|
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as mni
SQL>
SQL> With Data As (
2 Select '01A' As Numero, 'Frank' As Nom, 'Hilton' As Hotel, Null As Pourcentage_1, Null As Nombredenuit, 50 As Pourcentage_2 from dual union all
3 Select '01A' As Numero, 'Frank' As Nom, 'F1' As Hotel, 100 As Pourcentage_1, Null As Nombredenuit, 50 As Pourcentage_2 from dual union all
4 Select '02B' As Numero, 'Antoine' As Nom, 'Ozone' As Hotel, Null As Pourcentage_1, 2 As Nombredenuit, Null As Pourcentage_2 from dual union all
5 Select '02B' As Numero, 'Antoine' As Nom, 'Etap' As Hotel, 100 As Pourcentage_1, 5 As Nombredenuit, 100 As Pourcentage_2 from dual union all
6 Select '02B' As Numero, 'Antoine' As Nom, 'Ozone' As Hotel, Null As Pourcentage_1, 2 As Nombredenuit, Null As Pourcentage_2 from dual union all
7 Select '02B' As Numero, 'Antoine' As Nom, 'Ozone' As Hotel, 100 As Pourcentage_1, Null As Nombredenuit, 100 As Pourcentage_2 from dual union all
8 Select '02B' As Numero, 'Antoine' As Nom, 'Etap' As Hotel, Null As Pourcentage_1, 0 As Nombredenuit, Null As Pourcentage_2 from dual union all
9 Select '03C' As Numero, 'Elodie' As Nom, 'Mariott' As Hotel, 50 As Pourcentage_1, Null As Nombredenuit, Null As Pourcentage_2 from dual union all
10 Select '03C' As Numero, 'Elodie' As Nom, 'IbisEco' As Hotel, 50 As Pourcentage_1, Null As Nombredenuit, 100 As Pourcentage_2 from dual union all
11 Select '04D' As Numero, 'Jeremy' As Nom, 'Ibis' As Hotel, 100 As Pourcentage_1, Null As Nombredenuit, 100 As Pourcentage_2 from dual union all
12 Select '04D' As Numero, 'Jeremy' As Nom, 'B2B' As Hotel, 20 As Pourcentage_1, Null As Nombredenuit, 20 As Pourcentage_2 from dual
13 ),
14 Regle2 as (
15 Select numero, Nom, Hotel, Pourcentage_1, Nombredenuit, Pourcentage_2,
16 Sum(Nombredenuit) Over (Partition by numero, hotel) TotNbrNuit
17 From Data
18 ),
19 Regles14 As (
20 Select numero, Nom, Hotel, Pourcentage_1, Nombredenuit, Pourcentage_2,
21 Case
22 When Pourcentage_1 > Pourcentage_2 Then 1
23 When Pourcentage_1 = Pourcentage_2 And Max(TotNbrNuit) Over (Partition by numero) = TotNbrNuit Then 2
24 When Pourcentage_1 = Pourcentage_2 And TotNbrNuit Is Null And Max(Pourcentage_1) Over (Partition by numero) = Pourcentage_1 Then 4
25 When Pourcentage_1 < Pourcentage_2 And Max(Pourcentage_2) Over (Partition by numero) = Pourcentage_2 Then 3
26 End r
27 From Regle2
28 )
29 Select *
30 From Regles14
31 Where r In (1,2,3,4)
32 /
NUMERO NOM HOTEL POURCENTAGE_1 NOMBREDENUIT POURCENTAGE_2 R
------ ------- ------- ------------- ------------ ------------- ----------
01A Frank F1 100 50 1
02B Antoine Etap 100 5 100 2
03C Elodie IbisEco 50 100 3
04D Jeremy Ibis 100 100 4
SQL> |
Partager