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
| CREATE OR ALTER VIEW EFFECTIF_EVOLUTION(
NOM_UNITE,
DATE_BILAN,
CADRE,
MAITRISE,
EXECUTION,
TOTAL)
AS
WITH RECURSIVE PERIODE(DEBUTMOIS,P)
AS (SELECT MAX(DATE_MOIS),EXTRACT(YEAR FROM MAX(DATE_MOIS)) AS P from tb_effectif
union all
select DATEADD(-1 MONTH TO DEBUTMOIS),EXTRACT(YEAR FROM DATEADD(-1 MONTH TO DEBUTMOIS))
from PERIODE
where EXTRACT(YEAR FROM DATEADD(-1 MONTH TO DEBUTMOIS))>=2009
),
FINAN AS (SELECT MAX(P) AS PA FROM PERIODE
GROUP BY EXTRACT(YEAR FROM DEBUTMOIS)),
UAN AS (SELECT U.CODE_UNITE,U.NOM_UNITE,P.PA FROM TB_UNITE U FULL JOIN FINAN P ON 1=1),
EFFECTIF AS (
select CODE_UNITE,EXTRACT(YEAR FROM date_mois) as periodemensuelle,
/* cadre */
Sum(nbr_cadre_unite +nbr_cadre_centre+nbr_cadre_step +nbr_cadre_sr) as cadre,
/* maitrise */
Sum(nbr_maitrise_unite + nbr_maitrise_centre+nbr_maitrise_step + nbr_maitrise_sr) as maitrise,
/* execution */
Sum(nbr_execution_unite + nb_execution_centre+nbr_execution_step + nbr_execution_sr) as execution,
/* total */
Sum(nbr_cadre_unite +nbr_cadre_centre+nbr_cadre_step +nbr_cadre_sr) +
Sum(nbr_maitrise_unite + nbr_maitrise_centre+nbr_maitrise_step + nbr_maitrise_sr) +
Sum(nbr_execution_unite + nb_execution_centre+nbr_execution_step + nbr_execution_sr) as Total
from tb_effectif
GROUP BY CODE_UNITE, code_unite, EXTRACT(YEAR FROM date_mois)
)
SELECT U.NOM_UNITE, U.PA,
COALESCE(E.cadre,0) AS CADRE,
COALESCE(E.Maitrise,0) AS MAITRISE,
COALESCE(E.Execution,0) AS EXECUTION,
COALESCE(E.cadre,0) + COALESCE(E.Maitrise,0) + COALESCE(E.Execution,0) AS TOTAL
FROM UAN U LEFT JOIN EFFECTIF E ON U.code_unite = E.CODE_UNITE AND E.PERIODEmensuelle = U.PA
; |
Partager