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
|
CREATE OR REPLACE VIEW V_FOR_8311_DEPART AS
SELECT DISTINCT Xx.annee,
SUM(Data1.Total),
SUM(Data1.Femmes),
Data2.Hommes + Data2.Femmes,
Data2.Femmes,
SUM(Data2.Total),
SUM(Data2.Femmes)
FROM (SELECT DISTINCT m.Mois,
m.Annee,
s.Structure,
f.Affichage Csp,
f.Ord
FROM For_All_Months m,
Structure s,
Csp c,
For_Display f
WHERE s.Structure NOT IN ('AMT')
AND c.Csp = f.Intitule
AND f.Document = 'FOR_8311_Depart'
AND f.Element = 'Tableau1') Xx,
(SELECT To_Char(a.Date_, 'MM') Mois,
To_Char(a.Date_, 'YYYY') Annee,
a.Structure,
a.Csp,
a.Hommes,
a.Femmes,
(a.Hommes + a.Femmes) Total
FROM Depart a) Data1,
(SELECT To_Char(b.Date_, 'MM') Mois,
To_Char(b.Date_, 'YYYY') Annee,
b.Structure,
b.Csp,
b.Hommes,
b.Femmes,
(b.Hommes + b.Femmes) Total
FROM Depart b) Data2
WHERE Xx.Mois = Data1.Mois(+)
AND Xx.Mois = Data2.Mois(+)
AND Xx.Annee - 1 = Data1.Annee(+)
AND Xx.Annee = Data2.Annee(+)
AND Xx.Structure = Data1.Structure(+)
AND Xx.Structure = Data2.Structure(+)
AND Xx.Csp = Data1.Csp(+)
AND Xx.Csp = Data2.Csp(+)
GROUP BY Xx.Annee, Data2.Hommes ,
Data2.Femmes |
Partager