Plusieurs SUM sur une même table [Résolu]

[fbms18]

Bonjour,

J'ai fait plusieurs recherches sur le forum, mais je n'arrive pas à trouver de sujet similaire alors je me lance.

Tout d'abord, la structure de mes tables versions simplifiées pour expliquer mon problème :
- occupation(numero_occupation,individu,type_occupation#,duree)
- type_occupation(type_occupation,libelle_occupation)

La table type_occupation contient une liste de nomenclature, celles qui m'intéressent :
1-Heures supplémentaires
2-Récup heures supp.

Et dans la table occupation, j'ai un enregistrement par individu pour chaque type occupation et par occupation.
Par exemple pour l'individu JEAN j'aurais autant d'enregistrements avec un type_occupation=1 (heures supp) qu'il n'en a fait réellement

589-JEAN-1-120
590-JEAN-1-30
591-JEAN-1-15

592-JEAN-2-30
593-JEAN-2-45

Durée en minutes.
Maintenant mon problème, je souhaiterais avec un select pouvoir cumuler la somme des heures supp, puis la somme des congés compensateurs sur une même ligne pour chaque individu.
Dans l'exemple du dessus, je voudrais pouvoir avoir :

JEAN - Heures supp:165minutes - Récup : 75minutes - Reste:90minutes
FRED - ..................

J'ai tenté avec sum(duree) over partition by également en faisant deux fois appels à ma table occupation avec des alias différents, mais que nenni ! Je bloque depuis un moment dessus et je n'arrive pas à trouver de solution, si ce n'est la solution pl_sql mais j'ai par habitude de créer mes requètes par des vues qui sont plus simples pour mes collègues pour ré-extraire les infos actualisées en temps réel.

Toute proposition est la bienvenue, et désolé si s'est une question de newbie :/
Merci à tous.

Frédéric.

[Waldar]

Vous cherchez à effectuer un PIVOT, on le fait ainsi :

Code :

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
WITH type_occupation AS
(
SELECT 1 AS type_occupation, 'Heures supplémentaires' AS libelle_occupation FROM dual union ALL
SELECT 2                   , 'Récup heures supp.'                           FROM dual
)
  ,  occupation AS
(
SELECT 589 AS numero_occupation, 'JEAN' AS individu, 1 AS type_occupation, 120 AS duree FROM dual union ALL
SELECT 590                     , 'JEAN'            , 1                   ,  30          FROM dual union ALL
SELECT 591                     , 'JEAN'            , 1                   ,  15          FROM dual union ALL
SELECT 592                     , 'JEAN'            , 2                   ,  30          FROM dual union ALL
SELECT 593                     , 'JEAN'            , 2                   ,  45          FROM dual
)
  SELECT occ.individu,
         sum(case toc.type_occupation when 1 then occ.duree end) AS heures_suppl,
         sum(case toc.type_occupation when 2 then occ.duree end) AS heures_recup,
         sum(case toc.type_occupation when 1 then occ.duree end) -
         sum(case toc.type_occupation when 2 then occ.duree end) AS restes_a_recup
    FROM occupation occ
         INNER JOIN type_occupation toc
           ON toc.type_occupation = occ.type_occupation
   WHERE toc.type_occupation IN (1, 2)
GROUP BY occ.individu;
 
INDIVIDU HEURES_SUPPL HEURES_RECUP RESTES_A_RECUP
-------- ------------ ------------ --------------
JEAN              165           75             90

[fbms18]

Merci pour cette réponse aussi rapide, je tente depuis ce midi de mettre en application l'exemple sur ma base réelle mais sans succès :s

J'ai finalement repris le principe de ton exemple, deux requêtes et une troisième pour fusionner les résultats et effectuer le calcul du solde, ce qui me donne donc 3 vues. Moins propre que ce que j'aurais souhaité, mais là clairement, je pense pas avoir les compétences requises :/

A moins que quelqu'un ne sache retranscrire mes requêtes :

Code :

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
 
SELECT 
DISTINCT
ind.no_individu AS numero
,ind.nom_affichage AS Nom
,ind.prenom_affichage AS Prénom
,str.ll_structure AS Service
,decode(occ.type,11,'Congés compensateurs',12,'Heures supplémentaires',occ.type) AS Type
,sum(occ.duree) AS dureeMinute
,trunc(sum(occ.duree)/60) || 'h' || decode(mod(sum(occ.duree),60),0,'00',1,'01',2,'02',3,'03',4,'04',5,'05',6,'06',7,'07',8,'08',9,'09',mod(sum(occ.duree),60)) AS dureeHeure
 
FROM
grhum.individu_ulr ind
,mangue.affectation aff
,grhum.structure_ulr str
,conges.plng_per_aff_ann pan
,conges.plng_aff_ann aan
,conges.plng_occ occ
 
WHERE ind.no_individu=aff.no_dossier_pers
-- Structures et affectations
AND aff.c_structure=str.c_structure
AND aff.d_deb_affectation <= (SELECT sysdate FROM dual)
AND (aff.d_fin_affectation >= (SELECT sysdate FROM dual) OR aff.d_fin_affectation IS NULL)
AND aff.tem_valide='O'
-- Planning
AND aff.no_seq_affectation=pan.oid_affectation
AND pan.oid_aff_ann=aan.oid
 
-- Heures supplémentaires 
AND aan.oid=occ.oid_aff_ann
-- Etat validé
AND occ.STATUS=0
-- Sur du planning réél
AND occ.flg_nature='R'
AND occ.type IN('12') 
 
GROUP BY ind.no_individu, ind.nom_affichage, ind.prenom_affichage, str.ll_structure, decode(occ.type,11,'Congés compensateurs',12,'Heures supplémentaires',occ.type) 
ORDER BY ind.nom_affichage, ind.prenom_affichage

Code :

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
 
SELECT 
DISTINCT
ind.no_individu AS numero
,ind.nom_affichage AS Nom
,ind.prenom_affichage AS Prénom
,str.ll_structure AS Service
,decode(occ.type,11,'Congés compensateurs',12,'Heures supplémentaires',occ.type) AS Type
,sum(occ.duree) AS dureeMinute
,trunc(sum(occ.duree)/60) || 'h' || decode(mod(sum(occ.duree),60),0,'00',1,'01',2,'02',3,'03',4,'04',5,'05',6,'06',7,'07',8,'08',9,'09',mod(sum(occ.duree),60)) AS dureeHeure
 
FROM
grhum.individu_ulr ind
,mangue.affectation aff
,grhum.structure_ulr str
,conges.plng_per_aff_ann pan
,conges.plng_aff_ann aan
,conges.plng_occ occ
 
WHERE ind.no_individu=aff.no_dossier_pers
-- Structures et affectations
AND aff.c_structure=str.c_structure
AND aff.d_deb_affectation <= (SELECT sysdate FROM dual)
AND (aff.d_fin_affectation >= (SELECT sysdate FROM dual) OR aff.d_fin_affectation IS NULL)
AND aff.tem_valide='O'
-- Planning
AND aff.no_seq_affectation=pan.oid_affectation
AND pan.oid_aff_ann=aan.oid
 
-- Heures supplémentaires 
AND aan.oid=occ.oid_aff_ann
-- Etat validé
AND occ.STATUS=0
-- Sur du planning réél
AND occ.flg_nature='R'
AND occ.type IN('11') 
 
GROUP BY ind.no_individu, ind.nom_affichage, ind.prenom_affichage, str.ll_structure, decode(occ.type,11,'Congés compensateurs',12,'Heures supplémentaires',occ.type) 
ORDER BY ind.nom_affichage, ind.prenom_affichage

Et celle qui fusionne :

Code :

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
 
 
SELECT 
DISTINCT
ind.no_individu AS numero
,ind.nom_affichage AS Nom
,ind.prenom_affichage AS Prénom
,str.ll_structure AS Service
,REPLACE(trunc(decode(h.dureeminute,'',0,h.dureeminute)/60) || 'h' || decode(mod(decode(h.dureeminute,'',0,h.dureeminute),60),0,'00',1,'01',2,'02',3,'03',4,'04',5,'05',6,'06',7,'07',8,'08',9,'09',mod(decode(h.dureeminute,'',0,h.dureeminute),60)),'h-','h') AS HeureSupp
,REPLACE(trunc(decode(c.dureeminute,'',0,c.dureeminute)/60) || 'h' || decode(mod(decode(c.dureeminute,'',0,c.dureeminute),60),0,'00',1,'01',2,'02',3,'03',4,'04',5,'05',6,'06',7,'07',8,'08',9,'09',mod(decode(c.dureeminute,'',0,c.dureeminute),60)),'h-','h') AS CongesComp
,REPLACE(trunc((decode(h.dureeminute,'',0,h.dureeminute) + decode(c.dureeminute,'',0,c.dureeminute))/60) || 'h' || decode(mod((decode(h.dureeminute,'',0,h.dureeminute) + decode(c.dureeminute,'',0,c.dureeminute)),60),0,'00',1,'01',2,'02',3,'03',4,'04',5,'05',6,'06',7,'07',8,'08',9,'09',mod((decode(h.dureeminute,'',0,h.dureeminute) + decode(c.dureeminute,'',0,c.dureeminute)),60)),'h-','h') AS Solde
 
FROM 
grhum.individu_ulr ind
,mangue.affectation aff
,grhum.structure_ulr str
,conges.plng_per_aff_ann pan
,ensib_hsupp h
,ensib_ccomp c
 
 
WHERE ind.no_individu=aff.no_dossier_pers
-- Structures et affectations
AND aff.c_structure=str.c_structure
AND aff.d_deb_affectation <= (SELECT sysdate FROM dual)
AND (aff.d_fin_affectation >= (SELECT sysdate FROM dual) OR aff.d_fin_affectation IS NULL)
AND aff.tem_valide='O' 
-- Planning
AND aff.no_seq_affectation=pan.oid_affectation
 
AND ind.no_individu=c.numero(+)
AND ind.no_individu=h.numero(+) 
 
GROUP BY ind.no_individu, ind.nom_affichage, ind.prenom_affichage, str.ll_structure, decode(h.dureeminute,'',0,h.dureeminute), decode(c.dureeminute,'',0,c.dureeminute), decode(h.dureeminute,'',0,h.dureeminute) + decode(c.dureeminute,'',0,c.dureeminute)
 
ORDER BY Nom, Prénom, Service

Le but n'est pas non plus d'y perdre trop de temps dessus.

[Waldar]

Il y a un petit condensé de mauvais et/ou vieux code dans ces requêtes !
Quelle est votre version d'Oracle ?

[fbms18]

Envoyé par Waldar

Il y a un petit condensé de mauvais et/ou vieux code dans ces requêtes !
Quelle est votre version d'Oracle ?

Ca vient de moi les mauvais et vieux codes

La version : 10.2.0.4.0

[Waldar]

Vraiment sans aucune arrière pensée, essayez de passer de temps en temps sur ce forum, même sur des sujets qui à priori ne vous intéressent pas : il regorge d'excellentes idées et de syntaxe moderne !

Revenons à vos requêtes.
J'ai procédé aux modifications suivantes :

• Jointures ANSI (syntaxe normative, séparation des jointures et des filtres)
• Fonction decode (propriétaire Oracle) -> case (multi sgbd, et beaucoup plus puissante) -- en fait la fonction a disparu dans le pivot
• Formatage de la durée avec un code plus court (peut encore être raccourci si les durées sont toujours inférieures à 24h)
• Suppression des sous-requêtes sur dual pour sysdate
• Suppression des distincts inutiles (puisqu'il y a déjà un regroupement agrégé, les données seront déjà distinctes)
• Ajout du pivot selon l'exemple que je vous ai proposé

De ce que j'ai compris de votre code, les durées des congés compensateurs sont saisies en négatifs.
Au final, ça devrait donner ceci (et ça remplace vos trois requêtes) :

Code :

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
  SELECT ind.no_individu      AS numero
       , ind.nom_affichage    AS Nom
       , ind.prenom_affichage AS Prénom
       , str.ll_structure     AS Service
       , to_char(trunc(sum(case occ.type when 12 then      occ.duree else 0 end) / 60)) || 'h' || 
         to_char(  mod(sum(case occ.type when 12 then      occ.duree else 0 end) , 60), 'fm00') AS HeureSupp
       , to_char(trunc(sum(case occ.type when 11 then -1 * occ.duree else 0 end) / 60)) || 'h' || 
         to_char(  mod(sum(case occ.type when 11 then -1 * occ.duree else 0 end) , 60), 'fm00') AS CongesComp
       , to_char(trunc(sum(occ.duree) / 60)) || 'h' || 
         to_char(  mod(sum(occ.duree) , 60), 'fm00') AS Solde
    FROM grhum.individu_ulr ind
         INNER JOIN mangue.affectation aff
           ON aff.no_dossier_pers = ind.no_individu
         INNER JOIN grhum.structure_ulr str
           ON str.c_structure = aff.c_structure
         INNER JOIN conges.plng_per_aff_ann pan
           ON pan.oid_affectation = aff.no_seq_affectation
         INNER JOIN conges.plng_aff_ann aan
           ON aan.oid = pan.oid_aff_ann
         INNER JOIN conges.plng_occ occ
           ON occ.oid_aff_ann = aan.oid
   WHERE aff.tem_valide = 'O'
    -- Affectations
     AND  aff.d_deb_affectation <= sysdate
     AND (aff.d_fin_affectation >= sysdate OR aff.d_fin_affectation IS NULL)
    -- Etat validé
     AND occ.STATUS = 0
    -- Sur du planning réél
     AND occ.flg_nature = 'R'
     AND occ.type IN (11, 12) 
GROUP BY ind.no_individu     
       , ind.nom_affichage   
       , ind.prenom_affichage
       , str.ll_structure
ORDER BY ind.nom_affichage    ASC
       , ind.prenom_affichage ASC
       , str.ll_structure     ASC;

[fbms18]

"Ca vient de moi les mauvais et vieux codes"
S'était sans arrières pensées et ironique, je suis un peu resté bloqué sur les syntaxes sql d'il y a 10 ans :/

C'est comment dire... magique !

3 fois moins de codes.
Mille merci! je vais me plonger dedans corps et âmes pour pouvoir le reproduire moi même et intégrer ses nouvelles syntaxes.

Merci infiniment pour ce temps et ces explications.

Frédéric.