Requête complexe jointures

[passpartout007]

Bonjour à tous,

Voilà habituellement je me débrouille toujours par moi-même pour mes requêtes mais maintenant que c'est mon boulot je pense que ça peut être intéressant d'en parler sur un forum comme celui-ci pour m'assurer que je ne me trompe pas

Alors je bosse sur une appli de gestion d'entreprise. Je peux pas vous détailler tout le fonctionnement et de toute façon je suis pas sûr que ça soit utile, mais voilà, il me faudrait une requête qui me retourne tous les sites clients (table 'SITE_CLIENT') qui sont "à faire", c'est-à dire les sites pour lesquels le dernier compte-rendu associé (table 'CR' : clé étrangère : idSite) date d'il y a plus d'un an (champ dateCR), et pour lesquels il n'existe pas d'élément de planning (table 'ELEMENT_PLANNING' : clés étrangères : idSite, idEtat) à l'état 'programme' ou 'valide' (table ETAT_PLANNING : idEtat, nomEtatPlanning), le tout ordonné par date de compte-rendu du plus ancien au plus récent.

Je sais pas si j'ai été assez clair, mais n'hésitez pas à me demander sinon...

Merci d'avance

[punkoff]

Bonjour,

Quelle requete avez-vous fait déjà ?

Accessoirement pourriez-vous poser une définition des tables plus lisible que ceci ?

table1
champ1
champ2

table2
champ1
champ2

..

[passpartout007]

Bonjour,

Déjà merci d'avoir répondu.
Je me doutais un peu que c'est ce qu'on allait me demander, mais les tables sont en réalité plutôt grosses et j'ai simplifié le problème.
Voilà ce que je peux vous donner pour définir les tables :

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
CR
    [idCR] [int] IDENTITY(1,1) NOT NULL,
    [refCR] [varchar](25) COLLATE French_CI_AS NULL,
    [dateCR] [smalldatetime] NULL,
    [idEtatRegistre] [tinyint] NULL,
    [temps] [smallint] NULL,
    [raisonValidation] [varchar](max) COLLATE French_CI_AS NULL,
    [etatCR] [tinyint] NULL,
    [idTechnicien] [tinyint] NULL,
    [idClient] [smallint] NULL,
    [idSite] [smallint] NULL,
    [contactRegistre] [varchar](50) COLLATE French_CI_AS NULL,
    [idRaison] [tinyint] NULL,
    [observations] [varchar](max) COLLATE French_CI_AS NULL,
    [CRExceptionnel] [bit] NULL,
    [surTablette] [bit] NULL,
 
SITE_CLIENT
    [idSite] [smallint] IDENTITY(1,1) NOT NULL,
    [idClient] [int] NULL,
    [NomSite] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [NumRue] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TypeRue] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [NomRue] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AdresseSuite] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CodePostal] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Ville] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Pays] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [NumTelFixe] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [NumFax] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Mail] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [NumTelMobile] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AdresseFacturation] [bit] NULL,
    [AdresseIntervention] [bit] NULL,
    [Kilometrage] [numeric](18, 2) NULL,
    [idZone] [smallint] NULL,
    [idTechnicien] [tinyint] NULL,
    [Horaires] [varchar](50) COLLATE French_CI_AS NULL,
    [tempsTheorique] [numeric](18, 0) NULL,
 
ELEMENT_PLANNING
    [idElementPlanning] [int] IDENTITY(1,1) NOT NULL,
    [idPlanningEmploye] [int] NULL,
    [idSite] [int] NULL,
    [dateDebut] [smalldatetime] NULL,
    [dateFin] [smalldatetime] NULL,
    [commentaire] [varchar](200) COLLATE French_CI_AS NULL,
    [dateAjout] [datetime] NULL,
    [idEtat] [int] NULL,
    [dureeMinutes] [int] NULL
 
ETAT_PLANNING
    [idEtatPlanning] [int] IDENTITY(1,1) NOT NULL,
    [nomEtatPlanning] [varchar](50) COLLATE French_CI_AS NULL,
    [commentaireEtatPlanning] [varchar](200) COLLATE French_CI_AS NULL

Pour l'instant j'ai un truc comme ça mais il manque des choses et ça peut certainement être moins lourd :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT DISTINCT S.* 
FROM SITE_CLIENT S 
JOIN CR ON CR.idSite = S.idSite 
JOIN ELEMENT_PLANNING EL ON EL.idSite = S.idSite 
  JOIN ETAT_PLANNING ET ON EL.idEtat = ET.idEtatPlanning 
WHERE ET.nomEtatPlanning <> 'programme' 
  AND ET.nomEtatPlanning <> 'valide' 
  AND CR.dateCR = (
    SELECT MAX(dateCR) 
    FROM CR 
    WHERE idSite=CR.idSite
) 
ORDER BY CR.dateCR ASC

Merci de votre aide

[punkoff]

j'ai oublié de vous demander votre SGBD désolé, vu que vous avez une notion de date max, ça peut engendrer des syntaxes différentes.

[SQLpro]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT DISTINCT S.* 
FROM SITE_CLIENT S 
JOIN CR ON CR.idSite = S.idSite 
LEFT OUTER JOIN ELEMENT_PLANNING EL ON EL.idSite = S.idSite 
  JOIN ETAT_PLANNING ET ON EL.idEtat = ET.idEtatPlanning 
WHERE ET.nomEtatPlanning <> 'programme' 
  AND ET.nomEtatPlanning <> 'valide' 
  AND CR.dateCR = (
    SELECT MAX(dateCR) 
    FROM CR 
    WHERE idSite=CR.idSite
) 
AND EL.idSite IS NULL
ORDER BY CR.dateCR ASC

A +

[passpartout007]

Merci SQLPro, je testerai ça demain au boulot
Sinon pour répondre à punkoff, c'est SQL Server
Merci encore, et je posterai le verdict demain

[passpartout007]

Bonjour,

J'ai testé et la requête ne me retourne rien alors que j'ai bien regardé et j'ai au moins un site client qui répond bien à tous les critères et qui devrait être retourné...
Aussi il y a un aspect non pris en compte dans la requête que SQLPro m'a envoyée, c'est que le dernier compte-rendu doit dater de plus d'un an, sinon il ne doit pas être retourné.
Et aussi j'ai un petit doute (mais je peux me tromper) sur la ligne

AND EL.idSite IS NULL

parce que dans la table ELEMENT_PLANNING, jamais le champ idSite ne sera nul. Quand je crée un élément je lui affecte forcément un site, donc à voir...

Merci quand-même, si vous avez des idées n'hésitez pas

[CinePhil]

Quels sont les sites dont le dernier compte-rendu remonte à plus d'un an ?

Code :

1
2
3
4
SELECT idSite, MAX(dateCR) AS date_dernier_CR
FROM CR
GROUP BY idSite
HAVING MAX(dateCR) + INTERVAL 1 YEAR < CURRENT_DATE

=> Selon votre SGBD, il est possible qu'il faille jouer avec les fonctions de date à la place de la formule ci-dessus.

Restreignons à ceux pour lesquels il n'existe pas d'élément de planning à l'état programme ou valide :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT idSite, MAX(dateCR) AS date_dernier_CR
FROM CR
WHERE NOT EXISTS
(
    SELECT *
    FROM ELEMENT_PLANNING EL
    INNER JOIN ETAT_PLANNING ET ON ET.idEtatPlanning = EL.idEtat
    WHERE EL.idSite = CR.idSite
        AND ET.nomEtatPlanning IN ('programme', 'valide')
)
GROUP BY idSite
HAVING MAX(dateCR) + INTERVAL 1 YEAR < CURRENT_DATE
ORDER BY MAX(dateCR)

Si vous voulez toutes les colonnes de la table SITE_CLIENT, il suffit de faire une jointure de cette table avec la requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT S.*
FROM SITE_CLIENT S
INNER JOIN 
(
    SELECT idSite, MAX(dateCR) AS date_dernier_CR
    FROM CR
    WHERE NOT EXISTS
    (
        SELECT *
        FROM ELEMENT_PLANNING EL
        INNER JOIN ETAT_PLANNING ET ON ET.idEtatPlanning = EL.idEtat
        WHERE EL.idSite = CR.idSite
            AND ET.nomEtatPlanning IN ('programme', 'valide')
    )
    GROUP BY idSite
    HAVING MAX(dateCR) + INTERVAL 1 YEAR < CURRENT_DATE
) tmp ON tmp.idSite = S.idSite
ORDER BY MAX(dateCR)

[passpartout007]

Alors là ! Grand Merci CinePhil
J'ai du effectivement modifier les fonctions de date et le ORDER BY, mais tout le reste semble fonctionner niquel !

Voilà la requête finale :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT S.*
FROM SITE_CLIENT S
INNER JOIN 
(
    SELECT idSite, MAX(dateCR) AS date_dernier_CR
    FROM CR
    WHERE NOT EXISTS
    (
        SELECT *
        FROM ELEMENT_PLANNING EL
        INNER JOIN ETAT_PLANNING ET ON ET.idEtatPlanning = EL.idEtat
        WHERE EL.idSite = CR.idSite
            AND ET.nomEtatPlanning IN ('programme', 'valide')
    )
    GROUP BY idSite
    HAVING DATEADD(Year,1,MAX(dateCR)) < GETDATE()
) tmp ON tmp.idSite = S.idSite
ORDER BY date_dernier_CR ASC

Alors maintenant puisqu'on y est, je vais devoir récupérer la liste des sites faits, c'est à dire ceux auxquels sont associés au moins un compte-rendu (CR) de type périodique (CRExceptionnel à False) datant de moins de deux mois et avec etatCR à 3 ou 4.

Merci encore !

[CinePhil]

Envoyé par passpartout007

Alors maintenant puisqu'on y est, je vais devoir récupérer la liste des sites faits, c'est à dire ceux auxquels sont associés au moins un compte-rendu (CR) de type périodique (CRExceptionnel à False) datant de moins de deux mois et avec etatCR à 3 ou 4.

Tu essaies de la faire tout seul et si tu coinces, ouvre une nouvelle discussion, faisant éventuellement référence au message de celle-ci contenant la description des tables pour éviter de la redonner.