Jointure Triangulaire comment l'éviter?

**soazig** · 25/09/2009, 07h27

Bonjour,
J'ai une table contenant un historique notamment 4 colonnes :
CODE
LIBELLE
DATE_OUVERTURE
DATE_FERMETURE

On doit avoir le libellé d'un CODE donné pour une plage comprise entre DATE_OUVERTURE et DATE_FERMETURE.
J'ai réussi à remplir cette table ainsi

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CODE    libelle      DATE_OUVERTURE	DATE_FERMETURE	
01      lib012007    01/01/2007           NULL
01	 lib012007b   01/05/2007           NULL
01      lib012008    01/01/2008           NULL
 
02      Lib2 2007    01/01/2007           NULL
02      Lib2 2007b   05/06/2007           NULL
 
03      LIB3 2007    01/01/2007           NULL
 
04      LIB3 2007    01/01/2007	         15/03/2008

Et je voudrais trouver les dates de fermetures manquantes, qui sont la date d'ouverture immédiatement supérieure pour le même code.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CODE    libelle      DATE_OUVERTURE	DATE_FERMETURE	
01      lib012007    01/01/2007           01/05/2007
01	 lib012007b   01/05/2007           01/01/2008
01      lib012008    01/01/2008           NULL
 
02      Lib2 2007    01/01/2007           05/06/2007
02      Lib2 2007b   05/06/2007           NULL
 
03      LIB3 2007    01/01/2007           NULL
 
04      LIB3 2007    01/01/2007	         15/03/2008

Pour l'instant voici ma requête, elle est triangulaire.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Update PALIER
SET DATE_FERMETURE=
            (SELECT MIN(F.DATE_OUVERTURE)
            from PALIER F
            where PALIER.CODE=F.CODE
            AND F.DATE_OUVERTURE>PALIER.DATE_OUVERTURE
            )
 
where DATE_FERMETURE is null
AND  EXISTS ( 	SELECT * from PALIER E 
		WHERE E.CODE=PALIER.CODE 
		AND E.DATE_OUVERTURE>PALIER.DATE_OUVERTURE
	    );

le EXISTS sert à ne pas mettre à jour les lignes qui ne changent pas dont la date de fermeture reste à NULL, il est utile car il y a des trigger sur update dans cette table.
Avez vous une autre façon d'écrire cette requête qui soit plus élégante et sans jointure triangulaire?
Ps : la requête doit fonctionner en SQL Server 2000 et 2005

Merci de votre aide
Soazig

**elsuket** · 25/09/2009, 07h55

Bonjour,

Essayez la requête suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
UPDATE PALIER
SET DATE_FERMETURE = F.DATE_OUVERTURE
FROM dbo.PALIER AS P
JOIN (
		SELECT CODE, MIN(DATE_OUVERTURE) AS MIN_DATE_OUVERTURE
		FROM dbo.PALIER
		GROUP BY CODE
	) AS F (CODE, MIN_DATE_OUVERTURE)
	ON P.CODE = F.CODE
WHERE DATE_FERMETURE IS NULL

Elle est peut-être incomplète au regard de la requête que vous donnez puisqu'il n'y a pas le prédicat EXISTS : j'ai enlevé celui-ci car on procède à une auto-jointure (coûteuse) dont je ne comprends pas le prédicat (AND E.DATE_OUVERTURE>P.DATE_OUVERTURE).

@++

**soazig** · 25/09/2009, 08h59

Bonjour,
Je ne crois pas que ta requête donne le bon résultat.
Pour moi elle donnera cela

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CODE    libelle      DATE_OUVERTURE       DATE_FERMETURE	
01      lib012007    01/01/2007           01/01/2007
01      lib012007b   01/05/2007           01/01/2007
01      lib012008    01/01/2008           01/01/2007
 
02      Lib2 2007    01/01/2007           01/01/2007
02      Lib2 2007b   05/06/2007           01/01/2007
 
03      LIB3 2007    01/01/2007           01/01/2007
 
04      LIB3 2007    01/01/2007	         15/03/2008

C'est à dire que toutes les dates de fermeture d'un code sont les mêmes.
Alors que la date de fermeture d'un code doit être la date d'ouverture du palier suivant pour le même code s'il y en a un.

Tu as raison de ne pas comprendre E.DATE_OUVERTURE>P.DATE_OUVERTURE
car il fallait lire E.DATE_OUVERTURE>PALIER.DATE_OUVERTURE dans le exists.
Je vais éditer le premier message pour corriger cette coquille.
A+
Soazig

**Waldar** · 25/09/2009, 09h56

En créant un ID temporaire par code.
Voici le jeu de test :

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
DECLARE @PALIER AS TABLE
(
  CODE    VARCHAR(2),
  LIBELLE VARCHAR(10),
  DT_OUVR SMALLDATETIME,
  DT_FERM SMALLDATETIME
);
 
INSERT INTO @PALIER
SELECT '01', 'lib012007' , CAST('01/01/2007' AS SMALLDATETIME), CAST(NULL         AS SMALLDATETIME) UNION ALL
SELECT '01', 'lib012007b', CAST('01/05/2007' AS SMALLDATETIME), CAST(NULL         AS SMALLDATETIME) UNION ALL
SELECT '01', 'lib012008' , CAST('01/01/2008' AS SMALLDATETIME), CAST(NULL         AS SMALLDATETIME) UNION ALL
SELECT '02', 'Lib2 2007' , CAST('01/01/2007' AS SMALLDATETIME), CAST(NULL         AS SMALLDATETIME) UNION ALL
SELECT '02', 'Lib2 2007b', CAST('05/06/2007' AS SMALLDATETIME), CAST(NULL         AS SMALLDATETIME) UNION ALL
SELECT '03', 'LIB3 2007' , CAST('01/01/2007' AS SMALLDATETIME), CAST(NULL         AS SMALLDATETIME) UNION ALL
SELECT '04', 'LIB3 2007' , CAST('01/01/2007' AS SMALLDATETIME), CAST('03/15/2008' AS SMALLDATETIME)
;
 
SELECT * FROM @PALIER;
 
CODE LIBELLE    DT_OUVR                 DT_FERM
---- ---------- ----------------------- -----------------------
01   lib012007  2007-01-01 00:00:00     NULL
01   lib012007b 2007-01-05 00:00:00     NULL
01   lib012008  2008-01-01 00:00:00     NULL
02   Lib2 2007  2007-01-01 00:00:00     NULL
02   Lib2 2007b 2007-05-06 00:00:00     NULL
03   LIB3 2007  2007-01-01 00:00:00     NULL
04   LIB3 2007  2007-01-01 00:00:00     2008-03-15 00:00:00

Et la résolution avec une fonction analytique (je ne suis pas sûr pour SQL Server 2000 par contre) :

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
WITH PALIER (CODE, LIBELLE, DT_OUVR, DT_FERM, RN) AS
(
SELECT CODE,LIBELLE, DT_OUVR, DT_FERM,
       ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY DT_OUVR ASC)
FROM @PALIER
)
UPDATE P1
SET P1.DT_FERM = P2.DT_OUVR
FROM
    PALIER AS P1
    INNER JOIN PALIER AS P2
      ON P2.CODE = P1.CODE
     AND P2.RN = P1.RN + 1
WHERE P1.DT_FERM IS NULL;
 
SELECT * FROM @PALIER;
 
CODE LIBELLE    DT_OUVR                 DT_FERM
---- ---------- ----------------------- -----------------------
01   lib012007  2007-01-01 00:00:00     2007-01-05 00:00:00
01   lib012007b 2007-01-05 00:00:00     2008-01-01 00:00:00
01   lib012008  2008-01-01 00:00:00     NULL
02   Lib2 2007  2007-01-01 00:00:00     2007-05-06 00:00:00
02   Lib2 2007b 2007-05-06 00:00:00     NULL
03   LIB3 2007  2007-01-01 00:00:00     NULL
04   LIB3 2007  2007-01-01 00:00:00     2008-03-15 00:00:00

**soazig** · 25/09/2009, 10h40

Bonjour,
waldar, je confirme que sql server 2000 ne connait ni With, ni row_number() ni over.

Serveur*: Msg 156, Niveau 15, État 1, Ligne 1
Incorrect syntax near the keyword 'WITH'.
Serveur*: Msg 195, Niveau 15, État 1, Ligne 4
'ROW_NUMBER' is not a recognized function name.

Merci de ton aide
Soazig

**mikedavem** · 25/09/2009, 11h16

Bonjour,

Une requête qui devrait fonctionner sur 2000.
Il faudra l'adapter pour votre cas mais en principe elle devrait fonctionner. Elle part du même principe que celle proposée par Waldar. Pas sûr qu'elle soit plus optimisée...

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
 
DECLARE @PALIER AS TABLE
(
  CODE    VARCHAR(2),
  LIBELLE VARCHAR(10),
  DT_OUVR SMALLDATETIME,
  DT_FERM SMALLDATETIME
);
 
INSERT INTO @PALIER
SELECT '01', 'lib012007' , CAST('01/01/2007' AS SMALLDATETIME), CAST(NULL         AS SMALLDATETIME) UNION ALL
SELECT '01', 'lib012007b', CAST('01/05/2007' AS SMALLDATETIME), CAST(NULL         AS SMALLDATETIME) UNION ALL
SELECT '01', 'lib012008' , CAST('01/01/2008' AS SMALLDATETIME), CAST(NULL         AS SMALLDATETIME) UNION ALL
SELECT '02', 'Lib2 2007' , CAST('01/01/2007' AS SMALLDATETIME), CAST(NULL         AS SMALLDATETIME) UNION ALL
SELECT '02', 'Lib2 2007b', CAST('05/06/2007' AS SMALLDATETIME), CAST(NULL         AS SMALLDATETIME) UNION ALL
SELECT '03', 'LIB3 2007' , CAST('01/01/2007' AS SMALLDATETIME), CAST(NULL         AS SMALLDATETIME) UNION ALL
SELECT '04', 'LIB3 2007' , CAST('01/01/2007' AS SMALLDATETIME), CAST('15/03/2008' AS SMALLDATETIME)
;
 
UPDATE  @PALIER 
SET DT_FERM = p6.DT_OUVR
FROM @PALIER p0
INNER JOIN 
(
  SELECT p.CODE, P.LIBELLE, P5.DT_OUVR
  FROM
  (
    SELECT 
       (SELECT COUNT(*) FROM @PALIER p2
         WHERE p2.CODE <= p1.CODE AND p2.LIBELLE <= p1.LIBELLE) AS number,
       p1.*
    FROM @PALIER p1
  ) p
  INNER JOIN (SELECT 
	         (SELECT COUNT(*) 
	           FROM @PALIER p4
	           WHERE p4.CODE <= p3.CODE AND p4.LIBELLE <= p3.LIBELLE) AS number,
	      p3.*
	      FROM @PALIER p3) p5
  ON p.number = p5.number - 1 
   AND p.CODE = p5.CODE
) p6
ON p0.CODE = p6.CODE
 AND p0.LIBELLE = p6.LIBELLE
 
SELECT * FROM @PALIER;

++

**soazig** · 25/09/2009, 11h42

Bonjour,
Je ne vois pas ce que viennent faire les libellés dans la requête.
J'ai mis les libellés dans l'exemple pour qu'on comprenne qu'il y a d'autres informations qui changent selon les paliers.
Les différents libellés qui changent pourrait être "obstétrique" qui devient "obstétrique et maternité" qui devient "gynécologie"
Et dans la réalité j'ai d'autres colonnes qui changent d'un palier à l'autre.
Ma requête ne prenait pas en compte la colonne LIBELLE.

Cordialement
Soazig

**mikedavem** · 25/09/2009, 11h57

Comme je vous l'ai dit il faut adapter à votre besoin .

L'idée ici est de simuler le ROW_NUMBER() non disponible sur SQL Server 2000.

++

**Waldar** · 25/09/2009, 12h01

Envoyé par mikedavem

L'idée ici est de simuler le ROW_NUMBER() non disponible sur SQL Server 2000.

Je pense que c'est impossible à réaliser sans effectuer de jointure triangulaire, donc au final ça ne sera pas meilleur que la requête initiale.

**mikedavem** · 25/09/2009, 14h23

Envoyé par Waldar

Je pense que c'est impossible à réaliser sans effectuer de jointure triangulaire, donc au final ça ne sera pas meilleur que la requête initiale.

Tu as raison Waldar... je l'ai bien précisé dans mon précédent post ... je ne pense pas que ce soit plus optimisé. Après lecture des plans d'exécution et IO générés ... ce n'est sûrement pas la meilleur solution

++

**soazig** · 26/09/2009, 10h54

Bonjour,
Eh bien merci de votre aide, je vais donc garder ma requête initiale puis qu'elle semble pas si mauvaise que cela pour SQL Server 2000.

Merci à tous
Soazig

Jointure Triangulaire comment l'éviter?

MS SQL Server

Discussions similaires

Partager

Partager