Rollup et multilignes

**jopont** · 29/06/2012, 07h58

Bonjour,

Dans le code ci-dessous, je fais des sommes avec des 'case'
La table comporte plusieurs lignes pour chaque date.

Je ne sais pas comment faire pour indiquer que pour une même date et un même matricule si on trouve dans les lignes le motif DISPO et SPOASTJ de ne pas mettre la valeur à 1
Merci pour votre aide :

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
SELECT h.DAT, h.MOTIF,
SUM(
		CASE
			WHEN  h.MOTIF = 'SPOASTJ' THEN 1
			ELSE 0 
		  END
	) AS ASTJ,
SUM(
		CASE
			WHEN  (h.MOTIF = 'DISPO' AND h.HRADEB = '480' AND h.HRAFIN = '1920'AND h.MOTIF != 'SPOASTJ')  OR (h.MOTIF = 'DISPO' AND h.HRADEB = '480' AND h.HRAFIN = '1200')  THEN 1
			ELSE 0 
		  END
	) AS DISPJOUR,
SUM(
		CASE
			WHEN (h.MOTIF = 'DISPO' AND h.HRADEB = '480' AND h.HRAFIN = '1920') OR (h.MOTIF = 'DISPO' AND h.HRADEB = '1200' AND h.HRAFIN = '1920') THEN 1
			ELSE 0
		  END
	) AS DISPNUIT,
SUM(
		CASE
			WHEN (h.MOTIF = 'SPOG12JV' OR h.MOTIF = 'SPOG24JV') THEN 1
			ELSE 0
		END
	) AS POURVUJOUR,
SUM(
		CASE
			WHEN (h.MOTIF = 'SPOG12NV' OR h.MOTIF = 'SPOG24NV') THEN 1
			ELSE 0
		END
	) AS POURVUNUIT
FROM HOPHABS h
INNER JOIN HOPEMPL e ON e.MATRI = h.MATRI
WHERE (h.MOTIF = 'DISPO' Or h.MOTIF ='SPOASTJ' OR (SUBSTR(h.MOTIF, 1, 4) = 'SPOG' AND SUBSTR(h.MOTIF, 8,1) = 'V'))
AND EXTRACT (YEAR FROM h.DAT) = 2012 
AND (((h.JOUR BETWEEN 1 and 7) AND h.TYPJOU = 'F' ) OR ((h.JOUR = 6 Or h.JOUR = 7) AND h.TYPJOU != 'F'))
GROUP BY h.DAT,ROLLUP (h.MOTIF)
ORDER BY h.DAT

**jopont** · 29/06/2012, 12h50

dans cette partie de code ça ne fonctionne pas :

Code :

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

1
2
3
4
5
6
SUM(
		CASE
			WHEN  (h.MOTIF = 'DISPO' AND h.HRADEB = '480' AND h.HRAFIN = '1920'AND h.MOTIF != 'SPOASTJ')  OR (h.MOTIF = 'DISPO' AND h.HRADEB = '480' AND h.HRAFIN = '1200')  THEN 1
			ELSE 0 
		  END
	) AS DISPJOUR,

Les retours sur DISPJOUR sont à 1 même si il y a un enregistrement = SPOASTJ
pour une même date et un même matricule.

**Waldar** · 29/06/2012, 13h13

Il faudrait un jeu de données, je pense que vous avez un problème de division relationnelle mais je n'en suis pas certain.
Et préciser votre SGBD et sa version.

**jopont** · 29/06/2012, 14h58

Le SGBD est Oracle.

Les donnée sont retournées comme ci-dessous.
Dasn la colonne JOUR pour DISPO, je devrais être à 3 au lieu de 4, car un matricule est à la fois DISPO et SPOASTJ sur la même date.

DAT      MOTIF    ASTJ JOUR NUIT POURVUJOUR POURVUNUIT
-------- -------- ---- ---- ---- ---------- ----------
01/01/12 DISPO       0    4    4          0          0
01/01/12 SPOASTJ     4
01/01/12 SPOG12JV    0    0    0          3          0
01/01/12 SPOG12NV    0    0    0          0          4
01/01/12             4    4    4          3          4

**jopont** · 01/07/2012, 12h18

bonjour,

Quelqu'un a-t-il une solution ?

Merci

**mnitu** · 02/07/2012, 09h47

Pourriez-vous fournir le jeu d’essai demandé et non pas seulement le résultat de votre requête ?

**jopont** · 02/07/2012, 14h12

Bonjour,

Vous trouverez, en exemple, un jeux de données pour un matricule.

Merci pour votre aide

**jopont** · 02/07/2012, 19h33

Le jeu de données est-il suffisant ?

**mnitu** · 03/07/2012, 09h37

Si j’ai bien compris votre demande vous avez besoin de savoir pour chaque tuple matricule, date si les deux valeurs DISPO et SPOASTJ sont présentes parmi vos données.
Voilà une solution qui emploie des fonctions analytiques : Min et Max après avoir transformées les valeurs de la zone Motif pour obtenir Min pour DISPO et Max pour SPOASTJ (la table t_horaire contient les données du votre fichier excel)

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> Select *
  2    From (
  3          Select a.matri, a.dat, a.motif, a.hradeb, a.hrafin,
  4                 min(col) over (partition by matri, dat) minm,
  5                 max(col) over (partition by matri, dat) maxm
  6            From (
  7                  Select t.matri, t.dat, t.motif, t.hradeb, t.hrafin,
  8                         Case Motif
  9                           When 'DISPO' Then 1
 10                           When 'SPOASTJ' Then 9
 11                         End col
 12                    From t_horaire t
 13                 ) a
 14         )
 15  Where  minm != maxm
 16  /
 
  MATRI DAT         MOTIF                           HRADEB  HRAFIN       MINM       MAXM
------- ----------- ------------------------------ ------- ------- ---------- ----------
   5243 01/01/2012  DISPO                              480    1920          1          9
   5243 01/01/2012  SPOASTJ                            480    1200          1          9
   5243 01/01/2012  SPOG12NV                          1200    1920          1          9
 
SQL>

**jopont** · 03/07/2012, 10h37

Bonjour,

Pas sûr d'avoir bien compris.

Ce que je souhaiterais, c'est pouvoir diminuer la colonne DISPJOUR de 1 si on trouve pour une même date et un même matricule le motif SPOASTJ et le MOTIF DISPO pour hradeb = 480 et hrafin = 1920 ou hradeb = 480 et hrafin = 1200

Merci

**mnitu** · 03/07/2012, 14h51

Oui c'est ça. Vous prenez mes données en entrée et dans la partie

Code :

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

1
2
 
WHEN  (h.MOTIF = 'DISPO' AND h.HRADEB = '480' AND h.HRAFIN = '1920'AND h.MOTIF != 'SPOASTJ')

Vous remplacez votre restriction incorrecte h.MOTIF != 'SPOASTJ' par minm = maxm

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
 
SELECT h.DAT, h.MOTIF,
SUM(
    CASE
      WHEN  h.MOTIF = 'SPOASTJ' THEN 1
      ELSE 0 
      END
  ) AS ASTJ,
SUM(
    CASE
      WHEN  (h.MOTIF = 'DISPO' AND h.HRADEB = '480' AND h.HRAFIN = '1920' And h.minm = h.maxm)  OR (h.MOTIF = 'DISPO' AND h.HRADEB = '480' AND h.HRAFIN = '1200')  THEN 1
      ELSE 0 
      END
  ) AS DISPJOUR,
SUM(
    CASE
      WHEN (h.MOTIF = 'DISPO' AND h.HRADEB = '480' AND h.HRAFIN = '1920') OR (h.MOTIF = 'DISPO' AND h.HRADEB = '1200' AND h.HRAFIN = '1920') THEN 1
      ELSE 0
      END
  ) AS DISPNUIT,
SUM(
    CASE
      WHEN (h.MOTIF = 'SPOG12JV' OR h.MOTIF = 'SPOG24JV') THEN 1
      ELSE 0
    END
  ) AS POURVUJOUR,
SUM(
    CASE
      WHEN (h.MOTIF = 'SPOG12NV' OR h.MOTIF = 'SPOG24NV') THEN 1
      ELSE 0
    END
  ) AS POURVUNUIT
From 
(SELECT *
    FROM (
          SELECT a.matri, a.dat, a.motif, a.hradeb, a.hrafin,
                 min(col) over (partition BY matri, dat) minm,
                 max(col) over (partition BY matri, dat) maxm
            FROM (
                  SELECT t.matri, t.dat, t.motif, t.hradeb, t.hrafin,
                         Case Motif
                           When 'DISPO' Then 1
                           When 'SPOASTJ' Then 9
                         End col
                    FROM t_horaire t
                 ) a
         )
) h
GROUP BY h.DAT,ROLLUP (h.MOTIF)
ORDER BY h.DAT

**jopont** · 04/07/2012, 21h04

Bonjour,
Tout d'abord merci pour votre aide.
Je vais essayer, mais j'ai encore deux ou trois questions :
1) Où est passé ma table HOPHABS dans le select ?
La table t_horaire t est une table virtuelle ?

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
(SELECT *
    FROM (
          SELECT a.matri, a.dat, a.motif, a.hradeb, a.hrafin,
                 min(col) over (partition BY matri, dat) minm,
                 max(col) over (partition BY matri, dat) maxm
            FROM (
                  SELECT t.matri, t.dat, t.motif, t.hradeb, t.hrafin,
                         Case Motif
                           When 'DISPO' Then 1
                           When 'SPOASTJ' Then 9
                         End col
                    FROM t_horaire t
                 ) a
         )
) h

2) Avec ce code, y-a-t-il un risque d'écrire dans la table ?
3) Si je voulais analyser d'autre couple, avec par exemple DISPO et SPOASTN, peut-on créer en CASE Motif2 à la suite du premier avec then égale à 2 et à 8 ?
4) Est-il possible d'ajouter INNER et WHERE avant le GROUP BY :

Code :

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

1
2
3
4
INNER JOIN HOPEMPL e ON e.MATRI = h.MATRI
WHERE (h.MOTIF = 'DISPO' OR h.MOTIF ='SPOASTJ' OR (SUBSTR(h.MOTIF, 1, 4) = 'SPOG' AND SUBSTR(h.MOTIF, 8,1) = 'V'))
AND EXTRACT (YEAR FROM h.DAT) = 2012 
AND (((h.JOUR BETWEEN 1 AND 7) AND h.TYPJOU = 'F' ) OR ((h.JOUR = 6 OR h.JOUR = 7) AND h.TYPJOU != 'F'))

Merci pour vos réponses.

**mnitu** · 05/07/2012, 10h21

1) La table t_horaire est à l’image de votre fichier Excel. Comme vous n’avez pas pris la peine de fournir un jeu d’essai complet : création des tables script SQL d’insertions des données, etc. j’ai pris ma liberté d’appeler la table comme bon il m'a plu.

2) Je ne comprends pas votre question.

3 et 4) Essayez d’abord de comprendre la solution que j’ai vous ai proposée. L’idée est que pour chaque couple matricule date vous avez plusieurs enregistrements. Votre décision de compter ou pas l’enregistrement est basé d’un part sur les valeurs des divers colonnes de cet enregistrement et d’un autre parte sur des valeurs des divers colonnes des autres enregistrements. L’usage des fonctions de fenêtrage permet dans une certaine mesure d’obtenir ces informations et les rendre disponible pour l’enregistrement courant. Une fois que vois est arrivé à comprendre le mécanisme vous pouvez décider ce qui marche et ce qui ne marche pas.

Une autre idée sera de concevoir plusieurs requêtes mises ensemble via l’opérateur UNION ALL pour lesquelles les données sont sélectionnées par rapport à la conformité des règles de gestion. Exemple : on prend d’une part tous les tuples matricule/date pour lesquels le deux MOTIF : DISPO et SPOASTJ ne sont pas présentes simultanément et on applique les règles sur les valeurs des colonnes HRADEB et HRAFIN etc. et d’autre parte tous les tuples matricule/date qui présent les deux MOTIF et on applique des autres règles pour les colonnes HRADEB et HRAFIN etc.

**jopont** · 05/07/2012, 12h38

Bonjour merci pour vos réponses.

1) il faut que je remplace la t_horaire t par HOPHABS t dans le code, puisque les enregistrements sont dans la table HOPHABS ?

Merci

**mnitu** · 05/07/2012, 13h51

Oui

Rollup et multilignes

SQL Oracle

Discussions similaires

Partager

Partager