IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes MySQL Discussion :

Somme sur hiérarchies "imbriquées"


Sujet :

Requêtes MySQL

  1. #21
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 965
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 965
    Points : 30 777
    Points
    30 777
    Billets dans le blog
    16
    Par défaut
    3e partie

    Dans un 3e temps, pour chaque personne, on cumule ses dépenses par type de dépense.

    Exemple : Luc Poilau a effectué les dépenses suivantes :

    Carburant : 99,00 euros
    Déplacements : 274,00 euros
    TV : 60,00 euros
    Autres : 40,00 euros
    Mobile : 0,00 euro

    Mais Les dépenses du type de dépense Déplacements sont à prendre en compte pour le type auquel il se rattache, à savoir Courses. En conséquence, l’inventaire des dépenses de Luc est à compléter :

    Courses : 274,00 euros

    Par ailleurs les dépenses du type de dépense Mobile sont à cumuler avec celles des types qui lui sont rattachés, en l’occurrence TV et Autres. Ainsi, les dépenses de Luc pour le type de dépense Mobile sont les suivantes :

    60,00 + 40,00 + 0,00 = 100,00 euros.

    Luc a aussi dépensé 200,00 euros en Cinéma, type de dépense rattaché au type Multimédia, pour lequel les dépenses de Luc s’élèvent à 4,33 euros. Le type de dépense Mobile est rattaché lui aussi au type de dépense Multimédia, Luc a donc dépensé :

    4,33 + 200,00 + 100,00 = 304,33 euro pour ce type de dépense.

    Luc a aussi dépensé 47,00 euros en électronique, type de dépense auquel est rattaché le type de dépense Multimédia, Luc a donc dépensé :

    47,00 + 304,33 = 351,33 euros pour le type de dépense Electronique.

    Enfin, Luc a dépensé 300 euros en bagatelle, type de dépense rattaché au type de dépense Loisirs, auquel est aussi rattaché le type de dépense Electronique.

    En tout pour le type de dépense Loisirs, Luc a dépensé :

    351,33 + 300,00 = 651,33 euros.


    Pour récapituler les dépenses de Luc :

    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
     
    ExpTypeId     ExpTypeNom    Montant
    ---------     ----------    -------
            7     CARBURANT       99,00
           12     Courses        274,00
            9     Déplacements   274,00
            8     NETTOYAGE      274,00
           11     Loisirs        651,33
           30     Bagatelle      300,00
           10     Electro        351,33
            2     Multimédia     304,33
           20     Cinéma         200,00
            3     MOBILE         100,00
            4     TV              60,00
            5     AUTRES          40,00

    On procède de la même façon pour chaque type de dépense. Quand on a effectué les calculs pour chaque personne Il est alors facile d’obtenir le cumul par type de dépense par département.

    Procédure utilisée : YmoxRecursionExpenseTypeAscendante.

    Pour créer et tester la procédure :

    Code SQL : 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
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    224
    225
    226
    227
    228
    229
    230
    231
    232
    233
    234
    235
    236
    237
    238
    239
    240
    241
    242
    243
    244
    245
    246
    247
    248
    249
    250
    251
    252
    253
    254
    255
    256
    257
    258
    259
    260
    261
    262
    263
    264
    265
    266
    267
    268
    269
    270
    271
    272
    273
    274
    275
    276
    277
    278
    279
    280
    281
    282
    283
    284
    285
    286
    287
    288
    289
    290
    291
    292
    293
    294
    295
    296
    297
    298
    299
    300
    301
    302
    303
    304
    305
    306
    307
    308
    309
    310
    311
    312
    313
    314
    315
    316
    317
    318
    319
    320
    321
    322
    323
    324
    325
    326
    327
    328
    329
    330
    331
    332
    333
    334
    335
    336
    337
    338
    339
    340
    341
    342
    343
    344
    345
    346
    347
    348
    349
    350
    351
    352
    353
    354
    355
    356
    357
    358
    359
    360
    361
    362
    363
    364
    365
    366
    367
    368
    369
    370
    371
    -- ----------------------------------------------------------------------------------------------
    --
    -- Procédure YmoxRecursionExpenseTypeAscendante
    --
    -- En entrée :   
    --                    PERSON : table des personnes
    --                    DEP_DESC : hiérarchie limitée au département à traiter
    --                    EXP_DEC : niveau hiérarchique des types de dépense
    --                    EXPENSE_TYPE_HIERARCHIE : hiérarchie des types de dépense
    --                    EXPENSE : table des dépenses des personnes
    --    
    -- en sortie :
    --                    PILE_DEPT_TYPE_EXP : Dépenses d'un département, par sous-départements et par personne
    --    
    -- table de travail :
    --                    PILE_W 
    --                    MA_TRACE_EXP_ASC (pour déboguer au besoin)
    --                    MA_TRACE_DESC (pour déboguer YmoxRecursionNiveauHierarchieDepartements)
    --                    MA_TRACE_EXP_DESC (pour déboguer YmoxRecursionNiveauHierarchieTypesdeDepenses)
    --                     
    -- ----------------------------------------------------------------------------------------------
    USE Ymox ;
     
    DROP TABLE IF EXISTS PILE_W ;
    DROP TABLE IF EXISTS PILE_DEPT_TYPE_EXP ;
    DROP TABLE IF EXISTS MA_TRACE_EXP_ASC ;
    DROP TABLE IF EXISTS MA_TRACE_DESC ;
    DROP TABLE IF EXISTS DEP_DESC ;
     
    DROP TABLE IF EXISTS MA_TRACE_EXP_DESC ;
    DROP TABLE IF EXISTS EXP_DEC ;
     
    COMMIT ;
     
    -- ----------------------------------------------------------------
    -- Au cas où l'on aurait à tracer ce qui se passe....
    -- ----------------------------------------------------------------
    CREATE TABLE MA_TRACE_EXP_DESC
    (
        Trace       VARCHAR(256)     NOT NULL
    ) ;
     
    -- ------------------------------------------------------------------------------------------
    -- Hiérarchie des types de dépense descendant d'un type de dépense considéré comme racine
    -- ------------------------------------------------------------------------------------------
     
    CREATE TABLE  EXP_DEC
    (
        ExpTypeRacine       VARCHAR(8)            NOT NULL,
        ExpTypeParent       VARCHAR(8)            NOT NULL,
        ExpTypeId           VARCHAR(8)            NOT NULL,
        ExpTypeNiveau       INT                   NOT NULL
      , PRIMARY KEY (ExpTypeId)  	
    ) ;
     
    -- ----------------------------------------------------------------
    -- Au cas où l'on aurait à tracer ce qui se passe....
    -- ----------------------------------------------------------------
    CREATE TABLE MA_TRACE_DESC
    (
        Trace       VARCHAR(256)     NOT NULL
    ) ;
     
    -- --------------------------------------------------------------------------------
    -- Hiérarchie des départements descendants d'un département considéré comme racine
    -- --------------------------------------------------------------------------------
     
    CREATE TABLE  DEP_DESC
    (
        DeptRacine       VARCHAR(8)            NOT NULL,
        DeptParent       VARCHAR(8)            NOT NULL,
        DeptId           VARCHAR(8)            NOT NULL,
        DeptNiveau       INT                   NOT NULL
      , PRIMARY KEY (DeptId)
    ) ;
     
    CREATE TABLE PILE_W
    (
         PsnId             INT            NOT NULL
       , DeptParent        VARCHAR(8)     NOT NULL
       , DeptId            VARCHAR(8)     NOT NULL
       , ExpTypeParent     INT            NOT NULL
       , ExpTypeId         INT            NOT NULL
       , Montant           DECIMAL(10,2)  NOT NULL
       , Niveau            INT            NOT NULL
      , PRIMARY  KEY (PsnId, ExpTypeId)
    ) ;
     
    CREATE TABLE PILE_DEPT_TYPE_EXP
    (
         PsnId             INT            NOT NULL
       , DeptParent        VARCHAR(8)     NOT NULL
       , DeptId            VARCHAR(8)     NOT NULL
       , ExpTypeParent     INT            NOT NULL
       , ExpTypeId         INT            NOT NULL
       , Montant           DECIMAL(10,2)  NOT NULL
       , Niveau            INT            NOT NULL
     , PRIMARY  KEY (PsnId, ExpTypeId, Niveau)
    ) ;
     
    -- ----------------------------------------------------------------
    -- Au cas où l'on aurait à tracer ce qui se passe....
    -- ----------------------------------------------------------------
    CREATE TABLE MA_TRACE_EXP_ASC
    (
        Trace       VARCHAR(256)     NOT NULL
    ) ;
     
    COMMIT ;
    -- ----------------------------------------------------------------------------------------------------------------------------
     
    DROP PROCEDURE IF EXISTS YmoxRecursionExpenseTypeAscendante ;
     
    DELIMITER GO
     
    -- -----------------------------------------------------------------------------------------------
    CREATE PROCEDURE YmoxRecursionExpenseTypeAscendante
    (
       IN DepartementRacine VARCHAR(8), 
       INOUT Statut VARCHAR(128)
    )
     
    BEGIN
     
    DECLARE theNiveau INT DEFAULT 0;
    DECLARE Kount INT ;
    DECLARE StatutOK VARCHAR(20) DEFAULT 'OK' ;
     
    INSERT INTO MA_TRACE_EXP_ASC VALUES ('========================================================================================') ;
    INSERT INTO MA_TRACE_EXP_ASC VALUES (CONCAT('DepartementRacine = ', DepartementRacine)) ;
     
    SET theNiveau = (SELECT DISTINCT COALESCE (MAX(Niveau) + 1, 1) FROM PILE_DEPT_TYPE_EXP) ;
     
    INSERT INTO MA_TRACE_EXP_ASC VALUES (CONCAT('theNiveau = ', theNiveau)) ;
     
    IF theNiveau = 1 THEN
     
        SET Statut = StatutOK ; 
     
        INSERT INTO PILE_W
            SELECT x.PsnId, z.DeptParent, y.DeptId, t.ExpTypeParent, x.ExpTypeId, SUM(Amount), theNiveau
            FROM   EXPENSE AS x JOIN PERSON AS y ON x.PsnId = y.PsnId
                                JOIN  DEP_DESC AS z ON y.DeptId = z.DeptId
                                JOIN  EXPENSE_TYPE_HIERARCHIE AS t ON  x.ExpTypeId = t.ExpTypeId
            GROUP BY  x.PsnId, x.ExpTypeId ;
     
        INSERT INTO  PILE_DEPT_TYPE_EXP
            SELECT *
            FROM PILE_W ;
     
            -- C'est parti pour la récursion !
            CALL YmoxRecursionExpenseTypeAscendante(DepartementRacine, Statut) ;
     
            INSERT INTO MA_TRACE_EXP_ASC VALUES ('Retour de YmoxRecursionExpenseTypeAscendante') ;
     
    -- -----------------------------------------
     
        ELSE
     
            IF (SELECT COUNT(*) FROM PILE_W) > 0 THEN
     
            --  On vire de la table de travail les lignes ayant été transférées dans la table finale  
                DELETE FROM PILE_W
                       WHERE ExpTypeId IN (SELECT ExpTypeId FROM PILE_DEPT_TYPE_EXP) ;
     
            --  On récupère les lignes candidates au niveau + 1, en sommant les dépenses d'une personne par type de dépense
                INSERT INTO  PILE_W
                    SELECT  DISTINCT x.PsnId, x.DeptParent, x.DeptId, x.ExpTypeParent, x.ExpTypeId, x.Montant, x.Niveau
                    FROM
                      (SELECT  Psnid, DeptParent, DeptId
                             , (SELECT y.ExpTypeParent FROM  EXPENSE_TYPE_HIERARCHIE AS y WHERE x.ExpTypeParent = y.ExpTypeId) AS ExpTypeParent
                             , ExpTypeParent AS ExpTypeId
                             , SUM(Montant) AS Montant
                             , Niveau + 1 AS Niveau
                       FROM    PILE_DEPT_TYPE_EXP AS x
                       WHERE   Niveau = theNiveau - 1 
                       AND    x.ExpTypeParent > 0  -- Si ExpTypeParent = 0 alors on a atteint le niveau racine
                       GROUP BY Psnid, x.ExpTypeParent
                      ) AS x ;
     
            --  Et on empile dans la table finale
                INSERT INTO  PILE_DEPT_TYPE_EXP
                    SELECT *
                    FROM PILE_W AS x
                    WHERE Niveau = theNiveau  ;
     
            --  Récursion, rebelote!  
                CALL YmoxRecursionExpenseTypeAscendante(DepartementRacine, Statut) ;
     
           END IF ;
     
           INSERT INTO MA_TRACE_EXP_ASC VALUES ('Terminé avec YmoxRecursionExpenseTypeAscendante') ;
     
        END IF ;
    END  GO
     
    DELIMITER ;
     
    -- **********************************************************************************************
    -- Dépenses d'un département, par sous-départements et par personne.
    -- On fait chauffer les routines récursives...
    --  **********************************************************************************************
     
    SET @@GLOBAL.max_sp_recursion_depth = 15;
    SET @@session.max_sp_recursion_depth = 15; 
     
    -- **********************************************************************************************
    -- 1)
    -- On fait chauffer la routine récursive YmoxRecursionNiveauHierarchieDepartements
    -- pour restreindre la table DEPARTMENT_HIERARCHIE à un département et ses descendants
    -- **********************************************************************************************
    SET @Departement := 'D2' ;  --  Départment racine
    --   SET @Departement := 'D2211D' ;  
    --    SET  @Departement := 'D0' ; 
     
    CALL YmoxRecursionNiveauHierarchieDepartements(@Departement, @Statut);
     
    -- Au résultat
     
    SELECT 'Phase 1 - Statut : ', @Statut ;
     
    SELECT *, '' AS '<= DEP_DESC' FROM DEP_DESC ORDER BY DeptNiveau, DeptId ;
     
    -- **********************************************************************************************
    -- 2)
    -- On fait chauffer la routine récursive YmoxRecursionNiveauHierarchieTypesdeDepenses
    -- pour obtenir la descendance restreinte à un niveau hiérarchique
    -- (pour l'ensemble des types de dépense :  @TypeDepense := 0)
    -- **********************************************************************************************
    -- SET @TypeDepense := 12 ;  --  Type de dépense racine
    -- SET @TypeDepense := 11 ;  
    SET @TypeDepense := 0 ;   -- Pour avoir la hiérarchie au niveau entreprise
     -- SET  @TypeDepense := 9999 ; 
     
    CALL YmoxRecursionNiveauHierarchieTypesdeDepenses(@TypeDepense, @Statut);
     
    -- Au résultat
     
    SELECT 'YmoxRecursionNiveauHierarchieTypesdeDepenses, Statut : ', @Statut AS Statut ;
     
    SELECT *, '' AS 'EXP_DEC' FROM EXP_DEC ORDER BY ExpTypeNiveau, ExpTypeId ;
     
     
    -- **********************************************************************************************
    -- 3)
    -- On fait chauffer la routine récursive YmoxRecursionExpenseTypeAscendante
    -- pour obtenir les dépenses par départent, sous-département, personne, type de dépense
    -- **********************************************************************************************
     
     CALL YmoxRecursionExpenseTypeAscendante(@Departement, @Statut);
     
    -- Voyons voir le résultat...
     
    SELECT 'Phase 2 - Statut : ', @Statut ;
     
    DROP TABLE IF EXISTS CUMUL_ET_DETAILS ;
    DROP TABLE IF EXISTS CUMUL_PAR_PERSONNE ;
     
    CREATE TABLE CUMUL_ET_DETAILS
    (
        CumulId           INT            NOT NULL   AUTO_INCREMENT 
      , DeptId            VARCHAR(8)     NOT NULL          
      , PsnId             INT            NOT NULL
      , ExpTypeId         INT            NOT NULL
      , Montant           DECIMAL(10,2)  NOT NULL
      , TypeMontant       VARCHAR(8)     NOT NULL
     , PRIMARY KEY (CumulId) 
    ) ;
     
    CREATE TABLE CUMUL_PAR_PERSONNE 
    (
        CumulId           INT            NOT NULL   AUTO_INCREMENT 
      , DeptId            VARCHAR(8)     NOT NULL
      , DeptNiveau        INT            NOT NULL
      , DeptNom           VARCHAR(64)    NOT NULL
      , PsnId             INT            NOT NULL
      , PsnPrenom         VARCHAR(64)    NOT NULL
      , PsnNom            VARCHAR(64)    NOT NULL
      , Echelon           VARCHAR(64)    NOT NULL
      , ExpTypeId         INT            NOT NULL  
      , ExpNiveau         INT            NOT NULL
      , ExpTypeNom        VARCHAR(64)    NOT NULL
      , ExpTypedescr      VARCHAR(64)    NOT NULL
      , Montant           DECIMAL(10,2)  NOT NULL
      , TypeMontant       VARCHAR(8)     NOT NULL
     , PRIMARY KEY (CumulId) 
    ) ;
     
    DROP TABLE IF EXISTS CUMUL_PERSONNES_CONFONDUES ;
     
    CREATE TABLE CUMUL_PERSONNES_CONFONDUES
    (
        CumulId           INT            NOT NULL   AUTO_INCREMENT
      , DeptId            VARCHAR(8)     NOT NULL
      , DeptParent        VARCHAR(8)     NOT NULL
      , DeptNiveau        INT            NOT NULL
      , DeptNom           VARCHAR(64)    NOT NULL
      , PsnId             INT            NOT NULL
      , PsnPrenom         VARCHAR(64)    NOT NULL
      , PsnNom            VARCHAR(64)    NOT NULL
      , Echelon           VARCHAR(64)    NOT NULL
      , ExpTypeId         INT            NOT NULL  
      , ExpNiveau         INT            NOT NULL
      , ExpTypeNom        VARCHAR(64)    NOT NULL 
      , ExpTypedescr      VARCHAR(64)    NOT NULL
      , Montant           DECIMAL(10,2)  NOT NULL
     , PRIMARY KEY (CumulId) 
    ) ;
     
    ALTER TABLE CUMUL_PERSONNES_CONFONDUES AUTO_INCREMENT = 1000000 ; -- Pour éviter les collisions 
     
    INSERT INTO CUMUL_ET_DETAILS (DeptId, PsnId, ExpTypeId, Montant, TypeMontant)
    SELECT DeptId, PsnId, ExpTypeId, Montant, ''
    FROM   PILE_DEPT_TYPE_EXP
    UNION
    SELECT DeptId, PsnId, ExpTypeId, SUM(Montant), 'Cumul'
    FROM   PILE_DEPT_TYPE_EXP
    GROUP BY DeptId, PsnId, ExpTypeId ;
     
    SELECT DeptId, PsnId, ExpTypeId, Montant, TypeMontant, '' AS '<= CUMUL_ET_DETAILS' FROM CUMUL_ET_DETAILS
    ORDER BY DeptId, PsnId, ExpTypeId, TypeMontant DESC ;
     
    INSERT INTO CUMUL_PAR_PERSONNE (DeptId, DeptNiveau, DeptNom, PsnId, PsnPrenom, PsnNom, Echelon, ExpTypeId, ExpNiveau, ExpTypeNom, ExpTypedescr, Montant, TypeMontant)
    SELECT  x.DeptId, v.DeptNiveau, DeptNom
          , x.PsnId, PsnPrenom, PsnNom, (CASE WHEN u.DeptId IS NULL  THEN '' ELSE CONCAT('Chef (', DeptNom, ')') END) AS Chef
          , x.ExpTypeId, w.ExpTypeNiveau - 1 AS ExpNiveau, ExpTypeNom, ExpTypedescr
          , x.Montant
          , x.TypeMontant 
     
    FROM  CUMUL_ET_DETAILS AS x
         JOIN
              PERSON AS y ON x.PsnId = y.PsnId
         JOIN
              DEPARTMENT AS z ON x.DeptId = z.DeptId
         JOIN 
              EXPENSE_TYPE AS t ON x.ExpTypeId = t.ExpTypeId
         LEFT JOIN 
              CHEF_DEPARTMENT as u ON x.PsnId = u.Psnid 
         JOIN
              DEP_DESC AS v ON  x.DeptId = v.DeptId
         JOIN
           EXP_DEC AS w ON   x.ExpTypeId= w.ExpTypeId
    ;
     
    -- -----------------------------------------------------------------------------------------
    -- Cumul par personne / type de dépense
    -- -----------------------------------------------------------------------------------------
    SELECT *, '' AS '<=  CUMUL_PAR_PERSONNE' FROM  CUMUL_PAR_PERSONNE 
       ORDER BY Deptid, PsnId, ExpNiveau, ExpTypeId, TypeMontant DESC, Echelon DESC ;
     
     
    SELECT *, '' AS '<=  CUMUL_PAR_PERSONNE (cumul seulement)'  FROM  CUMUL_PAR_PERSONNE 
    WHERE  TypeMontant = 'Cumul'
       ORDER BY Deptid, PsnId, ExpNiveau, ExpTypeId, TypeMontant DESC, Echelon DESC ;
     
    INSERT INTO CUMUL_PERSONNES_CONFONDUES (DeptId, DeptParent, DeptNiveau, DeptNom, PsnId, PsnPrenom, PsnNom, Echelon, ExpTypeId, ExpNiveau, ExpTypeNom, ExpTypedescr, Montant) 
        SELECT x.DeptId, y.DeptParent, x.DeptNiveau, DeptNom, 0, '!!', '!!', 'département', ExpTypeId, ExpNiveau, ExpTypeNom, ExpTypedescr, SUM(Montant)
        FROM   CUMUL_PAR_PERSONNE AS x JOIN DEP_DESC AS y ON x.DeptId = y.DeptId
        WHERE  TypeMontant = 'Cumul'
        GROUP BY x.DeptId, ExpTypeId 
       UNION
        SELECT x.DeptId, x.DeptParent, x.DeptNiveau, DeptNom,  0, '!!', '!!', 'département', 0, 1, z.ExpTypeNom, z.ExpTypedescr, 0
        FROM  DEP_DESC AS x JOIN DEPARTMENT AS y ON x.DeptId = y.DeptId 
                            , EXPENSE_TYPE AS z    
        WHERE NOT EXISTS (SELECT ''
                          FROM   CUMUL_PAR_PERSONNE AS t
                          WHERE  x.DeptId = t.DeptId) 
        AND z.ExpTypeId = 0 ;   
     
    SELECT *, '' AS '<= CUMUL_PERSONNES_CONFONDUES' FROM CUMUL_PERSONNES_CONFONDUES
    ORDER BY DeptId, ExpTypeId, ExpNiveau, ExpTypeId ;
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  2. #22
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 965
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 965
    Points : 30 777
    Points
    30 777
    Billets dans le blog
    16
    Par défaut
    4e partie

    Dans un 4e temps, pour chaque département, on cumule les dépenses de ce département et celles de ceux qui lui sont rattachés. On tient compte du fait que des départements intermédiaires ont pu ne pas avoir de dépenses en propre, mais ils héritent néanmoins de celles des départements qui leur sont rattachés (cf. ci-dessous l’exemple du département D2210).La table CUMUL_PAR_DEPARTEMENT_COMPLET fournit le résultat final.


    Procédure utilisée : YmoxRecursionCumulsParDepartement.


    Pour créer et tester la procédure :


    Code SQL : 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
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    224
    225
    226
    227
    228
    229
    230
    231
    232
    233
    234
    235
    236
    237
    238
    239
    240
    241
    242
    243
    244
    245
    246
    247
    248
    249
    250
    251
    252
    253
    254
    255
    256
    257
    258
    259
    260
    261
    262
    263
    264
    265
    266
    267
    268
    269
    270
    271
    272
    273
    274
    275
    276
    277
    278
    279
    280
    281
    282
    283
    284
    285
    286
    287
    288
    289
    290
    291
    292
    -- --------------------------------------------------------------------------------------------------------
    --
    --  Cumul des dépenses par département, en incluant celles des départements hiérarchiquement rattachés 
    --
    -- --------------------------------------------------------------------------------------------------------
     
    USE Ymox ;
     
    DROP TABLE IF EXISTS CUMUL_PAR_DEPARTEMENT_COMPLET ;
    DROP TABLE IF EXISTS CUMUL_PAR_DEPARTEMENT_COMPLET_IMAGE ;
    DROP TABLE IF EXISTS CUMUL_W ;
    DROP TABLE IF EXISTS MA_TRACE_DPT_CUMUL ;
     
    COMMIT ;
     
    CREATE TABLE CUMUL_PAR_DEPARTEMENT_COMPLET
    (
        DeptId            VARCHAR(8)     NOT NULL
      , DeptParent        VARCHAR(8)     NOT NULL
      , DeptNiveau        INT            NOT NULL
      , ExpTypeId         INT            NOT NULL  
      , Montant           DECIMAL(10,2)  NOT NULL
      , Passage           INT            NOT NULL DEFAULT 0 
      , CumulFini         VARCHAR(3)     NOT NULL DEFAULT 'non'
        , PRIMARY KEY (DeptId, ExpTypeId)
    ) ;
     
    CREATE TABLE CUMUL_PAR_DEPARTEMENT_COMPLET_IMAGE
    (
        DeptId            VARCHAR(8)     NOT NULL
      , DeptParent        VARCHAR(8)     NOT NULL
      , DeptNiveau        INT            NOT NULL
      , ExpTypeId         INT            NOT NULL 
      , Montant           DECIMAL(10,2)  NOT NULL
      , Passage           INT            NOT NULL DEFAULT 0 
      , CumulFini         VARCHAR(3)     NOT NULL DEFAULT 'non'
        , PRIMARY KEY (DeptId, ExpTypeId)
    ) ;
     
    CREATE TABLE CUMUL_W
    (
        DeptId            VARCHAR(8)     NOT NULL
      , PRIMARY KEY (DeptId)
    ) ;
     
    -- ----------------------------------------------------------------
    -- Au cas où l'on aurait à tracer ce qui se passe....
    -- ----------------------------------------------------------------
    CREATE TABLE MA_TRACE_DPT_CUMUL
    (
        Trace       VARCHAR(256)     NOT NULL
    ) ;
     
    COMMIT ;
    -- ----------------------------------------------------------------------------------------------------------------------------
     
    DROP PROCEDURE IF EXISTS YmoxRecursionCumulsParDepartement ;
     
    COMMIT ;
     
    DELIMITER GO
     
    CREATE PROCEDURE YmoxRecursionCumulsParDepartement
    (
       -- IN DepartementRacine VARCHAR(8), 
       INOUT Statut VARCHAR(128)
    )
     
    BEGIN
     
    DECLARE thePassage INT DEFAULT 0;
    DECLARE Kount INT ;
    DECLARE StatutOK VARCHAR(20) DEFAULT 'OK' ;
     
     
    INSERT INTO MA_TRACE_DPT_CUMUL VALUES ('========================================================================================') ;
     
    SET thePassage = (SELECT DISTINCT COALESCE (MAX(Passage) + 1, 1) FROM  CUMUL_PAR_DEPARTEMENT_COMPLET) ;
     
    INSERT INTO MA_TRACE_DPT_CUMUL VALUES (CONCAT('thePassage = ', thePassage)) ;
     
    IF thePassage = 1 THEN
     
     --   SET Statut = StatutOK ; 
     
        -- ----------------------------------------------------------------------
        --   1er tour de manège, on amorce la pompe, en traitant des feuilles et de leur parent direct (table CUMUL_PERSONNES_CONFONDUES)
        --   On récupère d'abord les montants des feuilles
     
        INSERT INTO CUMUL_PAR_DEPARTEMENT_COMPLET (DeptId, DeptParent, DeptNiveau, ExpTypeId, Montant, Passage, CumulFini)
            SELECT    x.Deptid, x.DeptParent, x.DeptNiveau,  y.ExpTypeId, y.Montant, thePassage, 'oui' --  'récup des feuilles'
            FROM
                (
                 SELECT Deptid, DeptParent, DeptNiveau
                 FROM    DEP_DESC AS x 
                 WHERE  NOT EXISTS (SELECT '' FROM  DEP_DESC AS y WHERE  x.Deptid = y.DeptParent)
                ) AS x
                 JOIN CUMUL_PERSONNES_CONFONDUES AS y ON x.DeptId = y.DeptId
    ;
        -- --------------------------------------------------------------------------
        -- Pour chaque feuille, on effectue la somme (feuille + parent) des montants portés par le parent direct de chaque feuille
     
        INSERT INTO CUMUL_PAR_DEPARTEMENT_COMPLET (DeptId, DeptParent, DeptNiveau, ExpTypeId, Montant, Passage)
            SELECT x.DeptParent, y.DeptParent, y.DeptNiveau, x.ExpTypeId, SUM(x.Montant), thePassage + 1
            FROM
                (
                  -- Sommation des montants portés par le parent direct de chaque feuille
                 SELECT  x.DeptParent, COALESCE(y.ExpTypeId, 0) AS ExpTypeId, SUM(COALESCE(y.Montant, 0)) AS Montant
                 FROM
                     (
                      SELECT Deptid, DeptParent, DeptNiveau   -- Pour amorcer la pompe : détermination des départements feuilles 
                      FROM   DEP_DESC AS x 
                      WHERE  NOT EXISTS (SELECT '' FROM  DEP_DESC AS y WHERE  x.Deptid = y.DeptParent)
                     ) AS x
                      LEFT JOIN CUMUL_PERSONNES_CONFONDUES AS y ON x.Deptid = y.Deptid
     
                 GROUP BY x.DeptParent, COALESCE(y.ExpTypeId, 0) 
     
                UNION
     
                 SELECT  x.DeptParent, COALESCE(y.ExpTypeId, 0) AS ExpTypeId, COALESCE(y.Montant, 0) -- récup des montants portés par les parents des feuilles
                 FROM
                     (
                      SELECT Deptid, DeptParent, DeptNiveau
                      FROM   DEP_DESC AS x 
                      WHERE NOT EXISTS (SELECT '' FROM  DEP_DESC AS y WHERE  x.Deptid = y.DeptParent)
                     ) AS x
                      LEFT JOIN CUMUL_PERSONNES_CONFONDUES AS y ON x.DeptParent = y.Deptid
     
                 GROUP BY x.DeptParent, COALESCE(y.ExpTypeId, 0)  
                ) AS x
                 JOIN  DEP_DESC AS y ON x.DeptParent = y.DeptId
            GROUP BY x.DeptParent, x.ExpTypeId 
    ;
            INSERT INTO MA_TRACE_DPT_CUMUL VALUES ('Appel YmoxRecursionCumulsParDepartement') ;
     
     --       CALL YmoxRecursionCumulsParDepartement(DepartementRacine, Statut) ;
            CALL YmoxRecursionCumulsParDepartement(Statut) ;
     
            INSERT INTO MA_TRACE_DPT_CUMUL VALUES ('Fin d’itération') ;
    -- --------------------------------------------------------------------------------------------------------
    -- Tours de manège suivants
     
        ELSE 
            INSERT INTO MA_TRACE_DPT_CUMUL VALUES ('----------------------- Nouveau tour de manège ------------------------------') ;
     
            SET Kount = (SELECT COUNT(*)
                         FROM   CUMUL_PAR_DEPARTEMENT_COMPLET
                         WHERE  DeptParent IN  
                                             (SELECT DISTINCT x.DeptParent   
                                              FROM   CUMUL_PAR_DEPARTEMENT_COMPLET AS x
                                                     JOIN DEP_DESC AS y ON x.DeptParent = y.Deptid 
                                              WHERE  x.CumulFini = 'non')
                         ) ;
     
            INSERT INTO MA_TRACE_DPT_CUMUL VALUES (CONCAT('Kount = ', Kount)) ;
     
            IF Kount > 0 THEN
     
                -- ------------------------------------------------------------------------------
                -- Pour éviter de recumuler des montants qui n'ont plus à l'être...
     
                -- A ce stade, on sélectionne chaque département D marqué Cumulfini = 'non' pour lequel les cumuls seront additionnés à ceux de son département parent Dp, 
                -- suite à quoi D devra être marqué Cumulfini = 'oui'.
                -- L'opération est simple a priori : il suffit que les enfants de D soient tous marqués 'oui'. Mais prenons le cas de D2 lors du 2e tour : au vu du contenu 
                -- de la table CUMUL_PAR_DEPARTEMENT_COMPLET, les enfants de D2 certains sont notés 'oui' : D21, D23, D24 (ce sont des feuilles, l'opération a été effectuée 
                -- à l'occasion du 1er tour), mais le département D22 est pour le moment absent de la table CUMUL_PAR_DEPARTEMENT_COMPLET puisqu'il n'est pas feuille. 
                -- Pour vérifier que D2 a un enfant D22 qui n'a pas encore subi l'opération de cumul, empêchant de marquer D2 'oui', on effectue la jointure des
                -- tables CUMUL_PAR_DEPARTEMENT_COMPLET et DEP_DESC, pour constater qu'effectivement D2 a bien pour enfant D22 qui, par construction, n'est pas marqué 'oui'.
                -- D2 n'est donc pas retenu comme candidat a être marqué 'oui'. Cas des autres départements encore marqués 'non':  il s'agit de D2210 et D2211. 
                -- Comme D2, ils sont parents de départements, mais contrairement à D2 dont l'enfant D22 est absent de CUMUL_PAR_DEPARTEMENT_COMPLET,
                -- les enfants de D2210 et D2211 sont présents dans la table CUMUL_PAR_DEPARTEMENT_COMPLET : il faut donc mettre en place une condition supplémentaire pour
                -- empêcher le marquage à 'oui' s'il le faut : pour cela il suffit que les restants aient au moins un enfant marqué 'non'. C'est effectivement le cas de D2210 
                -- dont l'enfant D2211 est effectivement marqué 'non', alors qu'au contraire, tous les enfants de D2211 sont marqués 'oui' : seul D2111 reste candidat au marquage à 'oui'.
     
                DELETE FROM  CUMUL_W ;
     
                INSERT INTO CUMUL_W 
                    SELECT DISTINCT x.DeptId 
                    FROM   CUMUL_PAR_DEPARTEMENT_COMPLET AS x
                    WHERE  x.CumulFini = 'non'
                    AND    x.DeptId NOT IN 
                                         (SELECT DISTINCT y.DeptId
                                         FROM   CUMUL_PAR_DEPARTEMENT_COMPLET AS y JOIN  DEP_DESC AS z ON y.DeptId = z.DeptParent
                                         WHERE  NOT EXISTS (SELECT ''
                                                            FROM   CUMUL_PAR_DEPARTEMENT_COMPLET AS t
                                                            WHERE  z.DeptId = t.DeptId))
                    AND NOT EXISTS (SELECT ''
                                    FROM   CUMUL_PAR_DEPARTEMENT_COMPLET AS y
                                    WHERE  x.DeptId = y.DeptParent
                                      AND  y.CumulFini = 'non') ;   
     
                -- Et on cumule les dépenses des enfants avec celle des parents
     
                -- Passer par la table CUMUL_PAR_DEPARTEMENT_COMPLET_IMAGE permet d'éviter les doublons lors d'un insert direct  
                DELETE FROM  CUMUL_PAR_DEPARTEMENT_COMPLET_IMAGE ;
     
                INSERT INTO CUMUL_PAR_DEPARTEMENT_COMPLET_IMAGE (DeptId, DeptParent, DeptNiveau, ExpTypeId, Montant,  Passage, CumulFini) 
                    SELECT DeptId, DeptParent, DeptNiveau, ExpTypeId, SUM(Montant) AS Montant, thePassage, 'non'
                    FROM  (
                            SELECT x.DeptId, x.DeptParent, x.DeptNiveau, x.ExpTypeId, x.Montant
                            FROM   CUMUL_PERSONNES_CONFONDUES AS x
                            WHERE  x.DeptId IN (SELECT DISTINCT x.DeptParent   
                                                FROM   CUMUL_PAR_DEPARTEMENT_COMPLET AS x
                                                       JOIN  DEP_DESC AS y ON x.DeptParent = y.Deptid 
                                                WHERE  x.CumulFini = 'non')
     
                         UNION
     
                            SELECT DISTINCT y.DeptId, y.DeptParent, y.DeptNiveau, x.ExpTypeId, x.Montant
                            FROM   CUMUL_PAR_DEPARTEMENT_COMPLET AS x
                                   JOIN DEP_DESC AS y ON x.DeptParent = y.DeptId
                            WHERE x.DeptParent IN (SELECT  DISTINCT x.DeptParent   
                                                   FROM    CUMUL_PAR_DEPARTEMENT_COMPLET AS x
                                                           JOIN  DEP_DESC AS y ON x.DeptParent = y.Deptid 
                                                   WHERE x.CumulFini = 'non')
                       ) AS x
                   GROUP BY DeptNiveau, ExpTypeId
                   ;
     
                DELETE FROM  CUMUL_PAR_DEPARTEMENT_COMPLET 
                      WHERE DeptId IN (SELECT DeptId FROM  CUMUL_PAR_DEPARTEMENT_COMPLET_IMAGE) ;
     
     
                INSERT INTO CUMUL_PAR_DEPARTEMENT_COMPLET (DeptId, DeptParent, DeptNiveau, ExpTypeId, Montant,  Passage, CumulFini)
                    SELECT DeptId, DeptParent, DeptNiveau, ExpTypeId, Montant,  Passage, CumulFini
                    FROM   CUMUL_PAR_DEPARTEMENT_COMPLET_IMAGE ;
     
               -- ------------------------------------------------------------------------------
               -- Pour éviter de recumuler des montants qui n'ont plus à l'être : update effectif
     
                UPDATE CUMUL_PAR_DEPARTEMENT_COMPLET
                     SET CumulFini= 'oui'  WHERE DeptId IN (SELECT DeptId FROM CUMUL_W) ;
     
                CALL YmoxRecursionCumulsParDepartement(Statut) ;
     
                INSERT INTO MA_TRACE_DPT_CUMUL VALUES ('Fin d’iération') ;
     
            END IF ;
    END IF ;
     
    END  GO
     
    DELIMITER ;
     
    -- **********************************************************************************************
    -- Dépenses par département, sous-départements
    -- On fait chauffer la routine récursive qui va bien...
    --  **********************************************************************************************
     
    SET @@GLOBAL.max_sp_recursion_depth = 15;
    SET @@SESSION.max_sp_recursion_depth = 15; 
     
    SET  @DEPARTEMENT := '!!' ; 
     
    CALL YmoxRecursionCumulsParDepartement(@STATUT) 
     
    ;
     
    -- Voyons voir...
     
    SELECT 'YmoxRecursionCumulsParDepartement, Statut : ', @STATUT AS Statut ;
     
    SELECT *, '' AS '<= CUMUL_PAR_DEPARTEMENT_COMPLET' FROM CUMUL_PAR_DEPARTEMENT_COMPLET ;
     
    --
    -- On supprime les scories : tuples pour lesquels ExpTypeId = 0 (et montant = 0) alors que le département correspondant à un cumul de dépenses > 0.
    --
     
    DELETE FROM CUMUL_PAR_DEPARTEMENT_COMPLET
        WHERE DeptId IN (SELECT x.DeptId 
                         FROM 
                             (
                               SELECT DeptId, ExpTypeId
                               FROM   CUMUL_PAR_DEPARTEMENT_COMPLET 
                               WHERE  ExpTypeId = 0 
                             ) AS x
            JOIN 
                 (
                  SELECT DeptId 
                  FROM   CUMUL_PAR_DEPARTEMENT_COMPLET
                  GROUP BY DeptId
                  HAVING COUNT(*) > 1
                 ) AS y
            ON x.DeptId = y.DeptId)
        AND ExpTypeId = 0 
        ;
     
    --
    --  En principe ça devrait être correct...
    --
    SELECT DeptId, DeptParent, DeptNiveau, ExpTypeId, Montant, '' AS '<= CUMUL_PAR_DEPARTEMENT_COMPLET' FROM CUMUL_PAR_DEPARTEMENT_COMPLET ;

    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
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
     
    DeptId    DeptParent    DeptNiveau    ExpTypeId    Montant
    D2        D0                     1            2    1117,92
    D2        D0                     1            3     913,59
    D2        D0                     1            4     410,97
    D2        D0                     1            5      40,60
    D2        D0                     1            6    1000,00
    D2        D0                     1            7     990,03
    D2        D0                     1            8     274,00
    D2        D0                     1            9    1277,06
    D2        D0                     1           10    1169,92
    D2        D0                     1           11    2469,91
    D2        D0                     1           12    1290,06
    D2        D0                     1           17     314,00
    D2        D0                     1           20     200,00
    D2        D0                     1           30    1299,99
     
    D21       D2                     2            0       0,00
     
    D21A      D21                    3            0       0,00
     
    D22       D2                     2            2     834,08
    D22       D2                     2            3     629,75
    D22       D2                     2            4     342,73
    D22       D2                     2            5      40,00
    D22       D2                     2            6    1000,00
    D22       D2                     2            7     531,00
    D22       D2                     2            8     274,00
    D22       D2                     2            9    1277,06
    D22       D2                     2           10     886,08
    D22       D2                     2           11    2186,07
    D22       D2                     2           12    1290,06
    D22       D2                     2           17     314,00
    D22       D2                     2           20     200,00
    D22       D2                     2           30    1299,99
     
    D221      D22                    3            2     589,45
    D221      D22                    3            3     395,12
    D221      D22                    3            4     323,10
    D221      D22                    3            5      40,00
    D221      D22                    3            6    1000,00
    D221      D22                    3            7     531,00
    D221      D22                    3            8     274,00
    D221      D22                    3            9    1277,06
    D221      D22                    3           10     641,45
    D221      D22                    3           11    1941,44
    D221      D22                    3           12    1290,06
    D221      D22                    3           17     314,00
    D221      D22                    3           20     200,00
    D221      D22                    3           30    1299,99
     
    D2210     D221                   4            2     110,12
    D2210     D221                   4            3     110,12
    D2210     D221                   4            4      88,10
    D2210     D221                   4            7     332,00
    D2210     D221                   4            9       3,06
    D2210     D221                   4           10     115,12
    D2210     D221                   4           11    1115,11
    D2210     D221                   4           12      16,06
    D2210     D221                   4           17     314,00
    D2210     D221                   4           30     999,99
     
    D2211     D2210                  5            2     100,12
    D2211     D2210                  5            3     100,12
    D2211     D2210                  5            4      78,10
    D2211     D2210                  5            7     326,00
    D2211     D2210                  5            9       3,06
    D2211     D2210                  5           10     100,12
    D2211     D2210                  5           11    1100,11
    D2211     D2210                  5           12      13,06
    D2211     D2210                  5           17     314,00
    D2211     D2210                  5           30     999,99
     
    D2211B    D2211                  6            2      22,02
    D2211B    D2211                  6            3      22,02
    D2211B    D2211                  6           10      22,02
    D2211B    D2211                  6           11    1022,01
    D2211B    D2211                  6           30     999,99
     
    D2211C    D2211                  6            0       0,00
     
    D2211D    D2211                  6            2      78,10
    D2211D    D2211                  6            3      78,10
    D2211D    D2211                  6            4      78,10
    D2211D    D2211                  6            7     326,00
    D2211D    D2211                  6            9       3,06
    D2211D    D2211                  6           10      78,10
    D2211D    D2211                  6           11      78,10
    D2211D    D2211                  6           12       3,06
    D2211D    D2211                  6           17     314,00
     
    D2212     D2210                  5            2      10,00
    D2212     D2210                  5            3      10,00
    D2212     D2210                  5            4      10,00
    D2212     D2210                  5            7       6,00
    D2212     D2210                  5           10      15,00
    D2212     D2210                  5           11      15,00
    D2212     D2210                  5           12       3,00
     
    D2212A    D2212                  6            7       6,00
    D2212A    D2212                  6           10       5,00
    D2212A    D2212                  6           11       5,00
    D2212A    D2212                  6           12       3,00
     
    D2212AA   D2212A                 7            7       6,00
    D2212AA   D2212A                 7           10       5,00
    D2212AA   D2212A                 7           11       5,00
    D2212AA   D2212A                 7           12       3,00
     
    D2212AAA  D2212AA                8            7       6,00
    D2212AAA  D2212AA                8           10       5,00
    D2212AAA  D2212AA                8           11       5,00
    D2212AAA  D2212AA                8           12       3,00
     
    D23       D2                     2            7     406,29
     
    D24       D2                     2            2     207,24
    D24       D2                     2            3     207,24
    D24       D2                     2            4      49,24
    D24       D2                     2           10     207,24
    D24       D2                     2           1      207,24

    Les cumuls sont achevés. Pour regrouper les départements et les personnes et en ajoutant les rubans :

    Code SQL : 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
    SELECT  x.DeptId, x.DeptNom, x.PsnPrenom, x.PsnNom, x.Echelon, x.ExptypeId, x.ExpTypeNom, t.ExpTypeNiveau, x.Montant, 'psn' AS TypeEntite
    FROM   CUMUL_PAR_PERSONNE AS x 
           JOIN 
           EXPENSE_TYPE AS z ON x.ExptypeId = z.ExptypeId 
           JOIN 
           EXP_DEC AS t ON x.ExptypeId = t.ExptypeId 
    UNION
    SELECT x.DeptId, y.DeptNom, '', '', '' , x.ExptypeId, z.ExpTypeNom, t.ExpTypeNiveau, x.Montant, 'dpt' AS TypeEntite        
    FROM   CUMUL_PAR_DEPARTEMENT_COMPLET AS x 
           JOIN
           DEPARTMENT AS y ON x.DeptId = y.DeptId 
           JOIN 
           EXPENSE_TYPE AS z ON x.ExptypeId = z.ExptypeId 
           JOIN
           EXP_DEC AS t  ON x.ExptypeId = t.ExptypeId 
     
     ORDER BY DeptId, TypeEntite, Echelon DESC, PsnNom, ExpTypeNiveau, ExpTypeId ;


    Dans tout cela il y a matière à simplifier, et le défi est de tout ramener à une seule requête récursive... En attendant, j’ai trouvé l’exercice intéressant

    Un retour de votre part serait le bienvenu...
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  3. #23
    Candidat au Club
    Homme Profil pro
    Développeur Web
    Inscrit en
    Avril 2014
    Messages
    11
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Suisse

    Informations professionnelles :
    Activité : Développeur Web

    Informations forums :
    Inscription : Avril 2014
    Messages : 11
    Points : 3
    Points
    3
    Par défaut
    Wow, le code produit est bien au-delà de mes maigres connaissances actuelles, il faudra que je le reprenne plusieurs fois. Mais je pense avoir suivi le raisonnement.

    J'aurais une question par rapport au schéma, si cela ne vous gêne pas : pourquoi passer par une table intermédiaire pour les relations hiérarchiques ?

  4. #24
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 965
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 965
    Points : 30 777
    Points
    30 777
    Billets dans le blog
    16
    Par défaut
    Bonjour Ymox,


    Pourquoi passer par une table intermédiaire pour les relations hiérarchiques ?
    S’il s’agit de la table DEP_DESC, c’est pour éviter que les dépenses des personnes d’un département, par exemple D3 ne viennent se cumuler avec les dépenses des personnes du département D2 en cours de traitement. Si on s’en passait, il faudrait faire du récursif dans la procédure récursive YmoxRecursionExpenseTypeAscendante.

    Sinon, avez-vous fait un test ?
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  5. #25
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 965
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 965
    Points : 30 777
    Points
    30 777
    Billets dans le blog
    16
    Par défaut
    Bonjour Ymox,


    Pourquoi passer par une table intermédiaire pour les relations hiérarchiques ?
    Après tout, la table à laquelle vous faites allusion n’est-elle pas celle du diagramme initial, à savoir DEPARTMENT_HIERARCHIE ?

    Si je mets en œuvre cette table, c’est pour éviter la présence du bonhomme Null, propre à ficher la patouille dans la base de données.

    Votre propre table DEPARTMENT s’autoréférence, en conséquence de quoi la colonne parent_id d peut héberger le bonhomme Null (horresco referens...), c'est-à-dire quand un département est racine, autrement dit qu’il ne dépend d’aucun département (cas de D2 par exemple). Voyez ce que j’en pense ici ou ou encore .
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

+ Répondre à la discussion
Cette discussion est résolue.
Page 2 sur 2 PremièrePremière 12

Discussions similaires

  1. Somme sur conditions imbriquée dans une requête
    Par aleximan dans le forum Requêtes et SQL.
    Réponses: 8
    Dernier message: 25/01/2008, 19h05
  2. [Quote] DVP quoté sur Bashfr
    Par Skyounet dans le forum La taverne du Club : Humour et divers
    Réponses: 16
    Dernier message: 26/09/2006, 19h52
  3. Problème sur Request.ServerVariables("QUERY_STRING"
    Par PrinceMaster77 dans le forum ASP
    Réponses: 3
    Dernier message: 25/03/2005, 12h47

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