Requête sur l'évolution de l'effectif

**Just-Soft** · 27/06/2017, 22h47

Bonjour !

Pour commencer voici une requête que j'ai réalisé sous forme de vue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE OR ALTER VIEW EVOLUTION_EFFECTIF_UNITE(
    NOM_UNITE,
    DATE_MOIS,
    CADRE,
    MAITRISE,
    EXECUTION,
    TOTAL_EFFECTIF_UNITE)
AS
with effectif as
(select distinct tb_unite.nom_unite, tb_effectif.date_mois,
 
        tb_effectif.nbr_cadre_unite + tb_effectif.nbr_cadre_centre
                    + tb_effectif.nbr_cadre_step + tb_effectif.nbr_cadre_sr
        cadre,
 
        tb_effectif.nbr_maitrise_unite + tb_effectif.nbr_maitrise_centre
                    + tb_effectif.nbr_maitrise_step + tb_effectif.nbr_maitrise_sr
        maitrise,
 
        tb_effectif.nbr_execution_unite + tb_effectif.nb_execution_centre
                    + tb_effectif.nbr_execution_step + tb_effectif.nbr_execution_sr
        execution
 
from tb_unite
inner join tb_effectif on (tb_effectif.code_unite = tb_unite.code_unite)
order by tb_effectif.date_mois)
 
select e.*, e.cadre+e.maitrise+e.execution Total_Effectif_Unite from effectif e
order by e.nom_unite, e.date_mois
;

Donc, elle me fournit un tableau remplie de nom de l'unité, date, cadre, maitrise, execution et total_effectif. Ce que j'arrive pas à faire, et j'espère le trouver chez vous, c'est extraire la donnée du dernier mois de l'année pour avoir l'évolution annuelle. Le problème est dans l'année 2017 puisqu'elle n'a pas expirée !

Si ma demande est assez claire alors merci de me guider ou de me donner une piste.

**SergioMaster** · 28/06/2017, 09h06

Bonjour,

J'ai tiqué sur le distinct et le order by de la CTE A.M.H.A. inutiles

pour faire des comparaisons annuelles il faudrait déjà être sûr que pour chaque unité on ait les renseignements par mois ce que le inner join de la CTE pourrait empêcher (avec les données fournies uniquement par le code présenté)
1 - rien ne nous dit qu'un élément de tb_unite a une correspondance dans tb_effectif (-> un left join avec coalesce serait peut être mieux)
2- rien ne nous dit qu'un élément de tb_unite n'a pas plus d'une correspondance dans tb_effectif , ce qui expliquerait le distinct ? (-> un group by à la place)
une explication sur la relation entre les deux tables serait bienvenue

ensuite la comparaison doit-elle se faire en fonction du dernier mois de 2017 ou par années pleines et même ainsi que faire en cas de tranche de 12 mois incomplète ?

sinon pour gérer "les tranches" c'est tout à fait ce qu'une requête récursive sait faire

**Just-Soft** · 09/07/2017, 17h15

Envoyé par SergioMaster

1 - rien ne nous dit qu'un élément de tb_unite a une correspondance dans tb_effectif (-> un left join avec coalesce serait peut être mieux)

inner join tb_effectif on (tb_effectif.code_unite = tb_unite.code_unite)

Envoyé par SergioMaster

2- rien ne nous dit qu'un élément de tb_unite n'a pas plus d'une correspondance dans tb_effectif , ce qui expliquerait le distinct ? (-> un group by à la place)
une explication sur la relation entre les deux tables serait bienvenue

en effet, pour chaque mois, on renseigne la table effectif, donc un code_unité, une date et des info sur le personnel.

Envoyé par SergioMaster

ensuite la comparaison doit-elle se faire en fonction du dernier mois de 2017 ou par années pleines et même ainsi que faire en cas de tranche de 12 mois incomplète ?

Pour l'année en cours on prend le mois en cours mais pour les autres années c'est le dernier mois de l'année.

Envoyé par SergioMaster

sinon pour gérer "les tranches" c'est tout à fait ce qu'une requête récursive sait faire

C'est ce que j'arrive pas à faire

**SergioMaster** · 10/07/2017, 10h12

Bonjour,

Envoyé par Just-Soft

inner join tb_effectif on (tb_effectif.code_unite = tb_unite.code_unite)

j'avais bien vu le inner join mais je reste persuadé qu'un Left Join serait mieux surtout en cas de "trous"

par contre, je n'avais pas bien étudié la vue qui, AMHA est très mal exprimée :
- à quoi sert la jonction puisque aucune donnée de tb_Unité n'est extraite
- pourquoi faire une CTE
voir plus bas comment j'aurais fait (A mon avis la CTE effectif correspond à la vue, la seul chose que je ne comprends pas est la date_mois)

C'est ce que j'arrive pas à faire

à partir du moment où l'on a une date de départ c'est simple et une date de fin

disons que, pour l'exemple je veuille tous les mois (périodes) d'aujourd'hui jusqu'à début 2015)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
WITH RECURSIVE PERIODE(DEBUTMOIS,P) AS (select CURRENT_DATE AS DEBUTMOIS,EXTRACT(YEAR FROM CURRENT_DATE)*100+EXTRACT(MONTH FROM CURRENT_DATE)  AS P from RDB$DATABASE
                                    union all 
                                    select DATEADD(-1 MONTH TO DEBUTMOIS),EXTRACT(YEAR FROM DATEADD(-1 MONTH TO DEBUTMOIS))*100+EXTRACT(MONTH FROM DATEADD(-1 MONTH TO DEBUTMOIS)) from PERIODE 
                                    where EXTRACT(YEAR FROM DATEADD(-1 MONTH TO DEBUTMOIS))>=2015 
                             )
SELECT * FROM PERIODE

reste à transformer le SELECT * from PERIODE en un LEFT JOIN avec les données EFFECTIFS.
Là je vais prendre comme hypothèse que Date_mois correspond à un entier sous la forme YYYYMM

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH RECURSIVE PERIODE(DEBUTMOIS,P) AS (select CURRENT_DATE AS DEBUTMOIS,EXTRACT(YEAR FROM CURRENT_DATE)*100+EXTRACT(MONTH FROM CURRENT_DATE)  AS P from RDB$DATABASE
                                    union all 
                                    select DATEADD(-1 MONTH TO DEBUTMOIS),EXTRACT(YEAR FROM DATEADD(-1 MONTH TO DEBUTMOIS))*100+EXTRACT(MONTH FROM DATEADD(-1 MONTH TO DEBUTMOIS)) from PERIODE 
                                    where EXTRACT(YEAR FROM DATEADD(-1 MONTH TO DEBUTMOIS))>=2015 
                             ),
 
effectif as
  select e.nom_unite, e.date_mois,
           SUM(e.nbr_cadre_unite + e.nbr_cadre_centre+ e.nbr_cadre_step + e.nbr_cadre_sr) as  cadre,
           SUM(e.nbr_maitrise_unite +e.nbr_maitrise_centre+ e.nbr_maitrise_step + e.nbr_maitrise_sr) as  maitrise,
           SUM(e.nbr_execution_unite + e.nb_execution_centre + e.nbr_execution_step + e.nbr_execution_sr) as execution
           SUM(e.nbr_cadre_unite + e.nbr_cadre_centre+ e.nbr_cadre_step + e.nbr_cadre_sr+e.nbr_maitrise_unite +e.nbr_maitrise_centre+ e.nbr_maitrise_step + e.nbr_maitrise_sr+e.nbr_execution_unite + e.nb_execution_centre + e.nbr_execution_step + e.nbr_execution_sr) as TotalEffectif
 from tb_effectif  e
 GROUP BY e.nom_unite,e.date_mois 
 
SELECT PERIODE.P,E.UNITE,COALESCE(E.CADRE,0) AS CADRE,COALESCE( E.MAITRISE,0) AS MAITRISE, COALESCE(E.EXECUTION,0) AS EXECUTION,
           COALESCE(E.TOTALEFFECTIF,0) AS TOTALEFFECTIF
FROM PERIODE LEFT JOIN EFFECTIF E ON PERIODE.P=E.DATE_MOIS
ORDER BY PERIODE.P

si date_mois est une date et non une période une manipulation de date comme dans la CTE récursive et le tour est joué

NOTES : On peut réduire effectif avec une clause WHERE (dans mon exemple avec WHERE DATE_MOIS>201500)
On peut aussi modifier la CTE récursive période de façon à obtenir les 12 mois de l'année

**Just-Soft** · 10/07/2017, 23h34

Merci pour le temps et l'intérêt que vous portez Serge ...
La table tb_unite sert à retourner le nom de l'unité donc elle est utile pour le cas étudié.
La CTE ? justement, je voulais démarrer avec ça puisque je dois avoir l'évolution de l'effectif au cours du temps ...
mais je crois que ça se complique alors voilà ce que je propose.

La requête suivante permet d'avoir l'évolution de l'effectif/mois et par unité :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select distinct tb_unite.nom_unite, tb_effectif.date_mois,
 
        tb_effectif.nbr_cadre_unite + tb_effectif.nbr_cadre_centre
                    + tb_effectif.nbr_cadre_step + tb_effectif.nbr_cadre_sr
 
        cadre,
 
 
        tb_effectif.nbr_maitrise_unite + tb_effectif.nbr_maitrise_centre
                    + tb_effectif.nbr_maitrise_step + tb_effectif.nbr_maitrise_sr
        maitrise,
 
        tb_effectif.nbr_execution_unite + tb_effectif.nb_execution_centre
                    + tb_effectif.nbr_execution_step + tb_effectif.nbr_execution_sr
        execution
 
from tb_unite
left join tb_effectif on (tb_effectif.code_unite = tb_unite.code_unite)
order by tb_effectif.date_mois

Je voudrais à partir des résultats de cette requête avoir l'évolution annuelle de l'effectif donc ne laisser que le dernier mois de chaque année.

Comment dois-je procéder ?

merci par avance.

NB : voici en pièce jointe le résultat de la requête.

**SergioMaster** · 11/07/2017, 09h40

Bonjour,

Franchement, j'eusse préféré description et jeu d'essai des deux tables impliquées qu'un tableau Excel, j'ai du moins compris que date_mois était vraiment une date et non une période.

NOTE : SANS JEU D'ESSAI JE N'AI PAS PU TESTER CERTAINES PARTIES

La table tb_unite sert à retourner le nom de l'unité donc elle est utile pour le cas étudié.

oui mais non dans votre premier code (celui de la vue) cette jointure aurait du être faite ligne 28

Je voudrais à partir des résultats de cette requête avoir l'évolution annuelle de l'effectif donc ne laisser que le dernier mois de chaque année.

annuel ou mensuel, ce n'est qu'une question de choix de période et donc de la partie récursive du code que j'ai proposé
Pour moi, un résultat annuel serait la somme des résultats mensuels d'une année et non que le dernier mois de chaque année mais

....

puisque date_mois est une date et non une période

Envoyé par mon post précédent

si date_mois est une date et non une période une manipulation de date comme dans la CTE récursive

La requête suivante pour obtenir l'évolution de l'effectif/mois et par unité serait pour moi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select u.nom_unite, EXTRACT(YEAR FROM e.date_mois)*100+EXTRACT(MONTH FROM e.date_mois) as periodemensuelle,
        Sum(e.nbr_cadre_unite +e.nbr_cadre_centre+e.nbr_cadre_step +e.nbr_cadre_sr) as cadre,
        Sum(e.nbr_maitrise_unite + e.nbr_maitrise_centre+e.nbr_maitrise_step + e.nbr_maitrise_sr) as  maitrise,
        Sum(e.nbr_execution_unite + e.nb_execution_centre+e.nbr_execution_step + e.nbr_execution_sr as execution    
from tb_effectif e join tb_unite u on e.code_unite = u.code_unite
group by u.nom_unite,  EXTRACT(YEAR FROM e.date_mois)*100+EXTRACT(MONTH FROM e.date_mois)
ORDER BY 1,2

note : j'ai un petit doute sur la nécessité/possibilité d'adjonction du Order By
faire une requête pour des résultats annuels est aussi simple

pour avoir les résultats au mois 12 de chaque année sauf année en cours il "suffit de modifier" la partie récursive ou plutôt de faire une deuxième requête (CTE) dessus

considérez ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
WITH RECURSIVE PERIODE(DEBUTMOIS,P) AS (select CURRENT_DATE AS DEBUTMOIS,EXTRACT(YEAR FROM CURRENT_DATE)*100+EXTRACT(MONTH FROM CURRENT_DATE)  AS P from RDB$DATABASE
                                    union all 
                                    select DATEADD(-1 MONTH TO DEBUTMOIS),EXTRACT(YEAR FROM DATEADD(-1 MONTH TO DEBUTMOIS))*100+EXTRACT(MONTH FROM DATEADD(-1 MONTH TO DEBUTMOIS)) from PERIODE 
                                    where EXTRACT(YEAR FROM  DATEADD(-1 MONTH TO DEBUTMOIS))>=2015 
                             )
SELECT MAX(P) FROM PERIODE 
GROUP BY EXTRACT(YEAR FROM DEBUTMOIS)

en voici le résultat :

Périodes
201512
201612
201707

en utilisant cette requête comme CTE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
WITH RECURSIVE PERIODE(DEBUTMOIS,P) AS (select CURRENT_DATE AS DEBUTMOIS,EXTRACT(YEAR FROM CURRENT_DATE)*100+EXTRACT(MONTH FROM CURRENT_DATE)  AS P from RDB$DATABASE
                                    union all 
                                    select DATEADD(-1 MONTH TO DEBUTMOIS),EXTRACT(YEAR FROM DATEADD(-1 MONTH TO DEBUTMOIS))*100+EXTRACT(MONTH FROM DATEADD(-1 MONTH TO DEBUTMOIS)) from PERIODE 
                                    where EXTRACT(YEAR FROM  DATEADD(-1 MONTH TO DEBUTMOIS))>=2015 
                             ),
FINAN AS (SELECT MAX(P) AS PA FROM PERIODE 
GROUP BY EXTRACT(YEAR FROM DEBUTMOIS)),

pour obtenir les périodes pour toutes les unités même si les unités n'étaient pas en fonction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UAN AS (SELECT U.CODE_UNITE,U.NOM_UNITE,P.PA FROM TB_UNITE U FULL JOIN PERIODE P ON 1=1),

et avoir les effectifs en fonction de périodes mensuelle (et non de date)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
EFFECTIF AS (select CODE_UNITE,EXTRACT(YEAR FROM date_mois)*100+EXTRACT(MONTH FROM date_mois) as periodemensuelle,
        Sum(nbr_cadre_unite +nbr_cadre_centre+nbr_cadre_step +nbr_cadre_sr) as cadre,
        Sum(nbr_maitrise_unite + nbr_maitrise_centre+nbr_maitrise_step + nbr_maitrise_sr) as  maitrise,
        Sum(nbr_execution_unite + nb_execution_centre+nbr_execution_step + nbr_execution_sr as execution    
from tb_effectif -- ajout de WHERE EXTRACT(YEAR FROM date_mois)>=2015 pour limiter le nombre 
GROUP BY EXTRACT(YEAR FROM date_mois)*100+EXTRACT(MONTH FROM date_mois))

on utilise ensuite les différentes CTE pour obtenir les stats voulues pour toutes les unités, c'est le COALESCE qui met à 0 si l'unité n'était pas en service surant la période

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SELECT U.NOM_UNITE,U.PA,COALESCE(E.cadre,0) AS CADRE,COALESCE(E.Maitrise,0) AS Maitrise,COALESCE(e.Execution,0) AS EXECUTION 
FROM  UAN U LEFT JOIN EFFECTIF E ON  U.CODE_UNITE=E.CODE_UNITE AND E.PERIODEmensuelle=U.PA
ORDER BY -- selon le besoin NOM_UNITE,PA ou PA,NOM_UNITE

NOTE : Il me semble qu'avec les fonctions de fenêtrage de FB3 ce serait plus simple mais :

je n'ai pas de FB3 sous la main
je ne maitrise pas cette partie

**Just-Soft** · 11/07/2017, 12h26

Re,

Merci encore une fois ...

Alors voici le jeu de données des deux tables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE TB_EFFECTIF (
    CODE_BILAN             VARCHAR(8),
    CODE_UNITE             VARCHAR(2) NOT NULL,
    DATE_MOIS              DATE NOT NULL,
    NBR_CADRE_UNITE        SMALLINT,
    NBR_MAITRISE_UNITE     SMALLINT,
    NBR_EXECUTION_UNITE    SMALLINT,
    NBR_CADRE_STEP         SMALLINT,
    NBR_MAITRISE_STEP      SMALLINT,
    NBR_EXECUTION_STEP     SMALLINT,
    NBR_CADRE_CENTRE       SMALLINT,
    NBR_MAITRISE_CENTRE    SMALLINT,
    NB_EXECUTION_CENTRE    SMALLINT,
    NBR_CADRE_SR           SMALLINT,
    NBR_MAITRISE_SR        SMALLINT,
    NBR_EXECUTION_SR       SMALLINT,
    TOTAL_EFFETCTIF_LIGNE  BIGINT,
    TOTAL_HORIZONTALE COMPUTED BY (nbr_cadre_unite+nbr_maitrise_unite+nbr_execution_unite+
nbr_cadre_step+nbr_maitrise_step+nbr_execution_step+
nbr_cadre_centre+nbr_maitrise_centre+nb_execution_centre+
nbr_cadre_sr+nbr_maitrise_sr+nbr_execution_sr)
);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE TABLE TB_UNITE (
    CODE_UNITE      VARCHAR(2) NOT NULL,
    NOM_UNITE       VARCHAR(15) NOT NULL,
    REF_DECISION_   VARCHAR(15),
    DATE_CREATION   DATE,
    NB_COM_TOTAL    SMALLINT,
    LIN_TOTAL_COM   INTEGER,
    ADRESSE_CLIENT  VARCHAR(200)
);

Pour le code que vous m'avez fourni, j'ai eu du mal à le comprendre (désolé) et en l'exécutant j'ai eu droit à quelques erreurs que je n'ai pu surmonter.

**Just-Soft** · 11/07/2017, 12h58

Envoyé par SergioMaster

annuel ou mensuel, ce n'est qu'une question de choix de période et donc de la partie récursive du code que j'ai proposé
Pour moi, un résultat annuel serait la somme des résultats mensuels d'une année et non que le dernier mois de chaque année mais

....

Votre raisonnement est juste mais pas pour l'effectif ...
soit p.e l'effectif d'une unité X au mois de janvier 2017 : 150
au mois de février il est de 147
si on fait la somme on aura 297 ! alors qu'en réalité on dit que l'effectif à ce jour (février si c'est le dernier mois) est de 147 personnes

dans une courbe on va avoir une régression de 3 personnes ...

j'espère avoir était clair.

**Just-Soft** · 11/07/2017, 16h00

En rassemblant les bouts de codes que vous m'avez fournis Mr Serge j'ai pû éliminer quelques petits problèmes mais un me résiste.

Voici le code rassemblé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH RECURSIVE PERIODE(DEBUTMOIS,P)
 
    AS (select CURRENT_DATE AS DEBUTMOIS,EXTRACT(YEAR FROM CURRENT_DATE)*100+EXTRACT(MONTH FROM CURRENT_DATE)  AS P from RDB$DATABASE
        union all
        select DATEADD(-1 MONTH TO DEBUTMOIS),EXTRACT(YEAR FROM DATEADD(-1 MONTH TO DEBUTMOIS))*100+EXTRACT(MONTH 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.NOM_UNITE, U.code_unite FROM TB_UNITE U FULL JOIN PERIODE P ON 1=1),
EFFECTIF AS (select CODE_UNITE,EXTRACT(YEAR FROM date_mois)*100+EXTRACT(MONTH FROM date_mois) as periodemensuelle,
        Sum(nbr_cadre_unite +nbr_cadre_centre+nbr_cadre_step +nbr_cadre_sr) as cadre,
        Sum(nbr_maitrise_unite + nbr_maitrise_centre+nbr_maitrise_step + nbr_maitrise_sr) as  maitrise,
        Sum(nbr_execution_unite + nb_execution_centre+nbr_execution_step + nbr_execution_sr) as execution
from tb_effectif -- ajout de WHERE EXTRACT(YEAR FROM date_mois)>=2015 pour limiter le nombre 
GROUP BY CODE_UNITE, code_unite, EXTRACT(YEAR FROM date_mois)*100+EXTRACT(MONTH 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 
FROM  UAN U LEFT JOIN EFFECTIF E ON U.code_unite=E.CODE_UNITE AND E.PERIODEmensuelle=U.PA
--ORDER BY -- selon le besoin NOM_UNITE,PA ou PA,NOM_UNITE

L'erreur est dans cette ligne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

FROM  UAN U LEFT JOIN EFFECTIF E ON U.code_unite=E.CODE_UNITE AND E.PERIODEmensuelle=U.PA

Il reconnait pas la colonne U.PA bien qu'elle a était déclaré à cet endroit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

FINAN AS (SELECT MAX(P) AS PA FROM PERIODE

J'ai essayé de corriger ce petit problème mais sans résultat.

J'ai une petite question concernant ce code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXTRACT(YEAR FROM date_mois)*100

pourquoi un multiplicateur ? à quoi sert le *100 ?

Merci encore

**SergioMaster** · 11/07/2017, 16h17

c'est
ligne 11 UAN AS (SELECT U.CODE_UNITE,U.NOM_UNITE,P.PA FROM TB_UNITE U FULL JOIN ~~PERIODE P~~ FINAN P ON 1=1),

pourquoi un multiplicateur ? à quoi sert le *100 ?

pour pouvoir ajouter le mois et faire un entier sous la forme YYYYMM

**Just-Soft** · 11/07/2017, 17h00

Envoyé par SergioMaster

c'est
ligne 11 UAN AS (SELECT U.CODE_UNITE,U.NOM_UNITE,P.PA FROM TB_UNITE U FULL JOIN PERIODE P ON 1=1),

même erreur

Column does not belong to referenced table.
Dynamic SQL Error.
SQL error code = -206.
Column unknown.
P.PA.
At line 11, column 43.

**SergioMaster** · 11/07/2017, 17h24

Oui effectivement, à force je ne suis plus !
il y a erreur car il ne faut plus utiliser la CTE PERIODE mais FINAN de la ligne 8 pour n'avoir que les Periodes Maximales par année

La CTE UAN prend donc la forme
ligne 11

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UAN AS (SELECT U.CODE_UNITE,U.NOM_UNITE,P.PA FROM TB_UNITE U FULL JOIN FINAN P ON 1=1),

d'ailleurs PA ne fait partie que de cet CTE

**Just-Soft** · 12/07/2017, 15h52

Envoyé par SergioMaster

Oui effectivement, à force je ne suis plus !

désolé de ne pas avoir pu détecter l'erreur tout seul et merci encore une fois

Tout semble correct, le résultat retourné fournie la nombre par unité et pas année (dernier mois de l'année), sauf pour l'année en cours le résultat est zéro !

**SergioMaster** · 12/07/2017, 16h26

Envoyé par Just-Soft

Tout semble correct, le résultat retourné fournie la nombre par unité et pas année (dernier mois de l'année), sauf pour l'année en cours le résultat est zéro !

Peut être parce que la période (date_mois) n'est pas encore saisie ?
dans mes proposition pour la CTE récursive j'utilise CURRENT_DATE, il faudrait certainement remplacer la partie

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select CURRENT_DATE AS DEBUTMOIS,EXTRACT(YEAR FROM CURRENT_DATE)*100+EXTRACT(MONTH FROM CURRENT_DATE)  AS P from RDB$DATABASE

par un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT MAX(DATE_MOIS),EXTRACT(YEAR FROM MAX(DATE_MOIS))*100+EXTRACT(MONTH FROM MAX(DATE_MOIS))  AS P from tb_effectif

ou revoir la partie récursive de façon à intégrer l'id unité

**Just-Soft** · 12/07/2017, 17h17

Envoyé par SergioMaster

Peut être parce que la période (date_mois) n'est pas encore saisie ?
dans mes proposition pour la CTE récursive j'utilise CURRENT_DATE, il faudrait certainement remplacer la partie

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select CURRENT_DATE AS DEBUTMOIS,EXTRACT(YEAR FROM CURRENT_DATE)*100+EXTRACT(MONTH FROM CURRENT_DATE)  AS P from RDB$DATABASE

par un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT MAX(DATE_MOIS),EXTRACT(YEAR FROM MAX(DATE_MOIS))*100+EXTRACT(MONTH FROM MAX(DATE_MOIS))  AS P from tb_effectif

Très bien vue Maitre

Un grand merci et chapeau
il faut que je m'y mette maintenant ...

**Just-Soft** · 12/07/2017, 23h34

Toute ma gratitude à Serge qui sans lui ce code n'aura jamais vu le jour

Alors, pour toute fin utile, voici le code sous forme d'une vue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
;

**SergioMaster** · 13/07/2017, 07h59

Bonjour,

je ne suis pas sûr que ton code final soit totalement optimum et celui voulu car tu avais écrit que tu voulais l'effectif en fin d'année alors que là c'est l'effectif annuel
(somme des effectifs des mois)
Or tu as écrit :

Votre raisonnement est juste mais pas pour l'effectif ...
soit p.e l'effectif d'une unité X au mois de janvier 2017 : 150
au mois de février il est de 147
si on fait la somme on aura 297 ! alors qu'en réalité on dit que l'effectif à ce jour (février si c'est le dernier mois) est de 147 personnes

pour obtenir ceci c'est la période mensuelle qu'il faut avoir sous la forme YYYYMM soit an*100+mois

De même pour des questions d'optimisations (quand il y aura un grand nombre d'années d'historique) une limitation dans la CTE effectif devrait être envisagée

**Just-Soft** · 13/07/2017, 09h57

Bonjour à vous

Envoyé par SergioMaster

Bonjour,
je ne suis pas sûr que ton code final soit totalement optimum et celui voulu car tu avais écrit que tu voulais l'effectif en fin d'année alors que là c'est l'effectif annuel
(somme des effectifs des mois)

J'ai vérifié les résultats annuels et le compte est bon et je crois que ceci est dû à la ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT MAX(DATE_MOIS),EXTRACT(YEAR FROM MAX(DATE_MOIS)) AS P from tb_effectif

qui va chercher la dernière date l'année

Envoyé par SergioMaster

De même pour des questions d'optimisations (quand il y aura un grand nombre d'années d'historique) une limitation dans la CTE effectif devrait être envisagée

Pour l'instant disons que je suis satisfait car sous réseau intranet j'ai la réponse en moins d'une seconde pour 9 ans d'exploitation.

**SergioMaster** · 13/07/2017, 10h43

Re, tu ne m'as pas compris !

Si tu voulais le cumul annuel par unité OK , mais s'il s'agit de l'effectif en fin d'exercice (c.a.d dernier mois de l'année) soit décembre pour les années terminées et max(date_mois) pour l'année en cours il faut passer par une période de style AAAAMM et non AAAA

OR
ta CTE EFFECTIF fait une somme annuelle des effectifs (d'ailleurs, je remarque que le group by utilise deux fois la même colonne code_unite !)

s'il s'agit de la somme annuelle des effectifs alors ton post ne veux plus rien dire, la CTE RECURSIVE peut être simplifiée (pas la peine d'incrémenter par mois) puisqu'il suffit de récupérer les date_mois maximale et minimale , quelque chose comme ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
WITH RECURSIVE PERIODE(AN)
    AS (SELECT EXTRACT(YEAR FROM MIN(DATE_MOIS)) AS AN from tb_effectif
        union all
        select AN+1from PERIODE
        where AN+1<=EXTRACT(YEAR FROM CURRENT_DATE)
        )

**Just-Soft** · 13/07/2017, 13h06

Envoyé par SergioMaster

Re, tu ne m'as pas compris !

Si tu voulais le cumul annuel par unité OK , mais s'il s'agit de l'effectif en fin d'exercice (c.a.d dernier mois de l'année) soit décembre pour les années terminées et max(date_mois) pour l'année en cours il faut passer par une période de style AAAAMM et non AAAA

Effectivement, tu as raison sur toute la ligne.
Ce que je veux c'est en fin d'exercice.

Requête sur l'évolution de l'effectif

SQL Firebird

Discussions similaires

Partager

Partager