1. #1
    Membre émérite
    Homme Profil pro
    Directeur technique
    Inscrit en
    mai 2008
    Messages
    2 266
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : mai 2008
    Messages : 2 266
    Points : 2 275
    Points
    2 275

    Par défaut Requête sur l'évolution de l'effectif

    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.
    Bon courage ou Bonne Chance (selon le contexte)
    Mon blog sur WordPress

  2. #2
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    8 205
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 8 205
    Points : 18 790
    Points
    18 790
    Billets dans le blog
    4

    Par défaut

    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
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein
    J'entends et j'oublie. Je vois et je me souviens. Je fais et je comprends . Confucius
    Si votre seul outil est un marteau, vous aurez tendance a ne voir que des clous

  3. #3
    Membre émérite
    Homme Profil pro
    Directeur technique
    Inscrit en
    mai 2008
    Messages
    2 266
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : mai 2008
    Messages : 2 266
    Points : 2 275
    Points
    2 275

    Par défaut

    Citation Envoyé par SergioMaster Voir le message
    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)


    Citation Envoyé par SergioMaster Voir le message
    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.

    Citation Envoyé par SergioMaster Voir le message
    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.

    Citation Envoyé par SergioMaster Voir le message
    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
    Bon courage ou Bonne Chance (selon le contexte)
    Mon blog sur WordPress

  4. #4
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    8 205
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 8 205
    Points : 18 790
    Points
    18 790
    Billets dans le blog
    4

    Par défaut

    Bonjour,
    Citation Envoyé par Just-Soft Voir le message
    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
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein
    J'entends et j'oublie. Je vois et je me souviens. Je fais et je comprends . Confucius
    Si votre seul outil est un marteau, vous aurez tendance a ne voir que des clous

  5. #5
    Membre émérite
    Homme Profil pro
    Directeur technique
    Inscrit en
    mai 2008
    Messages
    2 266
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : mai 2008
    Messages : 2 266
    Points : 2 275
    Points
    2 275

    Par défaut

    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.
    Fichiers attachés Fichiers attachés
    Bon courage ou Bonne Chance (selon le contexte)
    Mon blog sur WordPress

  6. #6
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    8 205
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 8 205
    Points : 18 790
    Points
    18 790
    Billets dans le blog
    4

    Par défaut

    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
    Citation 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 :
    1. je n'ai pas de FB3 sous la main
    2. je ne maitrise pas cette partie
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein
    J'entends et j'oublie. Je vois et je me souviens. Je fais et je comprends . Confucius
    Si votre seul outil est un marteau, vous aurez tendance a ne voir que des clous

  7. #7
    Membre émérite
    Homme Profil pro
    Directeur technique
    Inscrit en
    mai 2008
    Messages
    2 266
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : mai 2008
    Messages : 2 266
    Points : 2 275
    Points
    2 275

    Par défaut

    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.
    Bon courage ou Bonne Chance (selon le contexte)
    Mon blog sur WordPress

  8. #8
    Membre émérite
    Homme Profil pro
    Directeur technique
    Inscrit en
    mai 2008
    Messages
    2 266
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : mai 2008
    Messages : 2 266
    Points : 2 275
    Points
    2 275

    Par défaut

    Citation Envoyé par SergioMaster Voir le message
    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.
    Bon courage ou Bonne Chance (selon le contexte)
    Mon blog sur WordPress

  9. #9
    Membre émérite
    Homme Profil pro
    Directeur technique
    Inscrit en
    mai 2008
    Messages
    2 266
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : mai 2008
    Messages : 2 266
    Points : 2 275
    Points
    2 275

    Par défaut

    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
    Bon courage ou Bonne Chance (selon le contexte)
    Mon blog sur WordPress

  10. #10
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    8 205
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 8 205
    Points : 18 790
    Points
    18 790
    Billets dans le blog
    4

    Par défaut

    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
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein
    J'entends et j'oublie. Je vois et je me souviens. Je fais et je comprends . Confucius
    Si votre seul outil est un marteau, vous aurez tendance a ne voir que des clous

  11. #11
    Membre émérite
    Homme Profil pro
    Directeur technique
    Inscrit en
    mai 2008
    Messages
    2 266
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : mai 2008
    Messages : 2 266
    Points : 2 275
    Points
    2 275

    Par défaut

    Citation Envoyé par SergioMaster Voir le message
    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.
    Bon courage ou Bonne Chance (selon le contexte)
    Mon blog sur WordPress

  12. #12
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    8 205
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 8 205
    Points : 18 790
    Points
    18 790
    Billets dans le blog
    4

    Par défaut

    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
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein
    J'entends et j'oublie. Je vois et je me souviens. Je fais et je comprends . Confucius
    Si votre seul outil est un marteau, vous aurez tendance a ne voir que des clous

  13. #13
    Membre émérite
    Homme Profil pro
    Directeur technique
    Inscrit en
    mai 2008
    Messages
    2 266
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : mai 2008
    Messages : 2 266
    Points : 2 275
    Points
    2 275

    Par défaut

    Citation Envoyé par SergioMaster Voir le message
    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 !
    Bon courage ou Bonne Chance (selon le contexte)
    Mon blog sur WordPress

  14. #14
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    8 205
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 8 205
    Points : 18 790
    Points
    18 790
    Billets dans le blog
    4

    Par défaut

    Citation Envoyé par Just-Soft Voir le message
    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é
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein
    J'entends et j'oublie. Je vois et je me souviens. Je fais et je comprends . Confucius
    Si votre seul outil est un marteau, vous aurez tendance a ne voir que des clous

  15. #15
    Membre émérite
    Homme Profil pro
    Directeur technique
    Inscrit en
    mai 2008
    Messages
    2 266
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : mai 2008
    Messages : 2 266
    Points : 2 275
    Points
    2 275

    Par défaut

    Citation Envoyé par SergioMaster Voir le message
    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 ...
    Bon courage ou Bonne Chance (selon le contexte)
    Mon blog sur WordPress

  16. #16
    Membre émérite
    Homme Profil pro
    Directeur technique
    Inscrit en
    mai 2008
    Messages
    2 266
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : mai 2008
    Messages : 2 266
    Points : 2 275
    Points
    2 275

    Par défaut

    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
    ;
    Bon courage ou Bonne Chance (selon le contexte)
    Mon blog sur WordPress

  17. #17
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    8 205
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 8 205
    Points : 18 790
    Points
    18 790
    Billets dans le blog
    4

    Par défaut

    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
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein
    J'entends et j'oublie. Je vois et je me souviens. Je fais et je comprends . Confucius
    Si votre seul outil est un marteau, vous aurez tendance a ne voir que des clous

  18. #18
    Membre émérite
    Homme Profil pro
    Directeur technique
    Inscrit en
    mai 2008
    Messages
    2 266
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : mai 2008
    Messages : 2 266
    Points : 2 275
    Points
    2 275

    Par défaut

    Bonjour à vous
    Citation Envoyé par SergioMaster Voir le message
    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

    Citation Envoyé par SergioMaster Voir le message
    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.
    Bon courage ou Bonne Chance (selon le contexte)
    Mon blog sur WordPress

  19. #19
    Rédacteur/Modérateur

    Avatar de SergioMaster
    Homme Profil pro
    Développeur informatique
    Inscrit en
    janvier 2007
    Messages
    8 205
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Loire Atlantique (Pays de la Loire)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Industrie

    Informations forums :
    Inscription : janvier 2007
    Messages : 8 205
    Points : 18 790
    Points
    18 790
    Billets dans le blog
    4

    Par défaut

    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)
            )
    La seule chose absolue dans un monde comme le nôtre, c'est l'humour. » Albert Einstein
    J'entends et j'oublie. Je vois et je me souviens. Je fais et je comprends . Confucius
    Si votre seul outil est un marteau, vous aurez tendance a ne voir que des clous

  20. #20
    Membre émérite
    Homme Profil pro
    Directeur technique
    Inscrit en
    mai 2008
    Messages
    2 266
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Service public

    Informations forums :
    Inscription : mai 2008
    Messages : 2 266
    Points : 2 275
    Points
    2 275

    Par défaut

    Citation Envoyé par SergioMaster Voir le message
    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.
    Bon courage ou Bonne Chance (selon le contexte)
    Mon blog sur WordPress

+ Répondre à la discussion
Cette discussion est résolue.
Page 1 sur 2 12 DernièreDernière

Discussions similaires

  1. Réponses: 1
    Dernier message: 16/03/2016, 21h16
  2. Requête sur un MemData
    Par claude dans le forum Bases de données
    Réponses: 5
    Dernier message: 23/12/2004, 10h11
  3. Requête sur date
    Par guenfood dans le forum Access
    Réponses: 11
    Dernier message: 08/12/2004, 16h11
  4. Requête sur un serveur lié
    Par Guizz dans le forum MS SQL-Server
    Réponses: 2
    Dernier message: 06/08/2003, 11h35
  5. requête sur l'année d'une date
    Par jo77 dans le forum Langage SQL
    Réponses: 4
    Dernier message: 30/07/2003, 09h28

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo