[AC-2007] Requête complexe : statut le plus récent de plusieurs dossiers [Résolu]

[johnny_titan]

Bonjour,

J'essaye de modéliser une base de donnée pour suivre des négociations de plusieurs contrats en simultané. La société (= nous) fait des offres, le client fait des contre-offres, jusqu’à ce qu’un accord soit conclu (montant offre = montant contre-offre). Le but est de créer un tableau qui résume l’avancement des négociations pour tous les contrats (dernière offre, dernière contre-offre, négociations conclues ou non…)

-------------

Plus précisément, on a une première table « Contrats » avec les champs :

• Numéro_contrat (numérique)
• Nom_contrat (texte)

Pour chaque contrat il arrive de nombreux évènements : à une certaine date, un montant est proposé par l’une ou l’autre des parties (nous = offre, ou le client = contre-offre). L’évènement peut donc être de deux types : offre (envoi par nous) ou contre-offre (envoyée par le client).

=> Table « Types_évènement » avec un champ « Type » et les valeurs « offre » et « contre-offre ».

Pour chaque évènement qu’on entre dans la base de données, on spécifie aussi le statut du contrat :

=> Table « Statuts » avec un champ « Statut » et les valeurs « en négociation », « accord conclu », « en attente réponse client », etc.

La table évènements est donc montée de la façon suivante :

=> Table « Évènements » avec les champs :

• Date_évènement (date)
• Numéro_contrat (numérique) : liste de choix faisant référence à la table « Contrats »
• Type : liste de choix faisant référence à la table « Types_évènement »
• Montant (monétaire)
• Statut : liste de choix faisant référence à la table « Statuts »

-------------

Ce que j’ai déjà fait :

États :

• Un état affichant uniquement la liste des contrats
• Un état affichant l’historique complet pour tous les contrats (affichage pour chaque contrat de tous les évènements avec leur type, leur montant et leur statut par ordre chronologique, regroupés par contrat)

Formulaires :

• Des formulaires pour ajouter un nouveau contrat ou un nouvel évènement

Requêtes :

• Une requête qui sort la date de l’offre la plus récente pour chaque contrat. Pour cela :
  • On fait une requête sélection,
  • On ajoute les tables Contrats, Évènements et Types_évènement et on joint les champs correspondants
  • On prend les champs
    • « Numéro_contrat » (avec tri croissant) de la table Contrats
    • « Nom_contrat » de la table Contrats
    • « Date_évènement » de la table Évènements
    • « Type » de la table Types_évènement => avec critère « contre-offre »,
  • On affiche les totaux de colonne et on met Max sur « Date_évènement »

-------------

Ce qu’on veut faire :

La requête indiquée ci-dessus est un début, mais je veux afficher un unique tableau avec une ligne par contrat, où on résume :

• Le numéro et le nom du contrat,
• L’offre la plus récente avec sa date et son montant,
• La contre-offre la plus récente avec sa date et son montant,
• Le statut le plus récent (en négociation, accord conclu, etc.)

Visiblement ça prend pas mal de requêtes et je n’y connais rien en SQL. Des idées ?

[Richard_35]

Bonjour Johnny_titan,

Ce que tu demandes ne semble pas très compliqué à obtenir. Cependant, peut-être faudrait-il examiner la conception de ta base.

Si j'ai bien compris, tes tables sont les suivantes (proposition d'Id en italique à considérer) :

Table Contrats :
- Numéro_contrat (clé primaire numéro auto ?)
- Nom_contrat
...

Table Types_évènement :
- Id_type (clé primaire)
- Type (« offre », « contre-offre »)
...

Table Statuts :
- Id_statut (clé primaire)
- Statut (« en négociation », « accord conclu », « en attente réponse client », etc.)
...

Table Évènements :
- Id_evenement (clé primaire)
- Numéro_contrat
- Date_évènement
- Id_Type
- Montant
- Id_Statut
==> index unique : Numéro_contrat / Date_évènement.


Relations :
Contrats 1----n Évènements, via Numéro_contrat ;
Types_évènement 1----n Évènements, via Id_type ;
Statuts 1----n Évènements, via Id_Statut.


Dans un premier temps, le statut se situe, plutôt au niveau du contrat et non de l'événement, non ?
Sinon, tu es obligé d'indiquer à tous les événements le statut du contrat, même s'il n'a pas changé.

[johnny_titan]

Bonjour,

Merci déjà pour ces précisions : je suis d'accord pour les Id à ajouter et pour les relations entre les tables.

Pour le statut je suis aussi d'accord avec toi, ça semble plus logique de l'intégrer dans la table Contrats.

En fait j'avais fonctionné comme ça pour la raison suivante :
=> Dans ma tête la table Contrats était relativement "figée". On peut toujours en créer un nouveau de temps en temps mais elle ne bouge pas très souvent. En revanche il y a très souvent de nouveaux évènements, qui mettent à jour à chaque fois le statut d'un contrat ou d'un autre. J'avais donc une table Évènements avec un beau formulaire associé : pour chaque nouvel évènement je le remplissais et je rentrais le statut mis à jour...

[Richard_35]

Oui, la table Évènements est une bonne idée : elle permet de conserver l'historique d'un contrat.

Donc, nous validons les changements suivants.

Table Contrats :
- Numéro_contrat (clé primaire numéro auto ?)
- Nom_contrat
- Id_Statut
...

Table Évènements :
- Id_evenement (clé primaire)
- Numéro_contrat
- Date_évènement
- Id_Type
- Montant
==> index unique : Numéro_contrat / Date_évènement.

Concernant ta demande initiale :

Ce qu’on veut faire :
La requête indiquée ci-dessus est un début, mais je veux afficher un unique tableau avec une ligne par contrat, où on résume :
* Le numéro et le nom du contrat,
* L’offre la plus récente avec sa date et son montant,
* La contre-offre la plus récente avec sa date et son montant,
* Le statut le plus récent (en négociation, accord conclu, etc.)

Créer une requête R1, via l'assistant :
- ajouter la table Evenements (FROM) ;
- ajouter la table Type_Evenement (FROM) ;
- lier Evenements à Type_Evenement, via Id_type (JOIN) ;
- passer en regroupement (GROUP BY) ;
- sélectionner Numero_contrat, Id_Type, max(Date_évènement) (SELECT) ;
- critères : Où Id_Type=1 ("offre") (WHERE).
==> donne la liste des contrats avec la date de leur dernière "offre" : 1 ligne par contrat.

Créer une requête R2, via l'assistant :
- ajouter la table Evenements (FROM) ;
- ajouter la table Type_Evenement (FROM) ;
- lier Evenements à Type_Evenement, via Id_type (JOIN) ;
- passer en regroupement (GROUP BY) ;
- sélectionner Numero_contrat, Id_Type, max(Date_évènement) (SELECT) ;
- critères : Où Id_Type=2 ("contre-offre") (WHERE).
==> donne la liste des contrats avec la date de leur dernière "contre-offre" : 1 ligne par contrat.

Créer une requête R3, via l'assistant :
- ajouter la table Contrats (FROM) ;
- ajouter la requête R1 (FROM) ;
- ajouter la requête R2 (FROM) ;
- lier Contrats à R1, via numero_contrat (JOIN) ;
- lier Contrats à R2, via numero_contrat (JOIN) ;
- sélectionner les champs que tu veux (SELECT).
==> tu devrais obtenir ce que tu souhaites.

Non testé, juste pressenti... mais, tu vois le principe, je pense.
Je ne serai pas disponible demain donc, à après-demain.

[johnny_titan]

Envoyé par Richard_35

==> tu devrais obtenir ce que tu souhaites.

Je viens de tester le tout et le principe est là ! Ma requête R3 sélectionne les champs Numéro_Contrat, MaxDeDate_évènement dans R1 et MaxDeDate_évènement dans R2.

Il reste quand même quelques affaires à régler :

• Contrats pour lesquels on a pas encore une offre ET une contre-offre :

R3 retourne seulement les contrats pour lesquels on a une offre ET une contre-offre. Est-ce qu'on peut par exemple modifier R2 pour qu'elle crée quand même le champ mais avec une valeur vide pour les contrats où il n'y a pas encore de contre-offre. Sinon il faut ajouter une autre requête peut-être ?

• Affichage des montants :

Pour le moment R3 retourne seulement des dates (c'est déjà bien !) Mais si je veux aussi afficher sur une même ligne les montants correspondant à la dernière offre ET à la dernière contre-offre, c'est un peu plus compliqué il me semble. Là encore il faut peut-être modifier R1 et R2 pour qu'ils retournent non seulement la date la plus récente mais aussi le montant correspondant.



Merci en tout cas, ça avance !

Thibaut

[Richard_35]

Bonsoir Johnny_titan,

Contrats pour lesquels on a pas encore une offre ET une contre-offre

Pour la requête R3, il faut indiquer des flèches à droite (double-click sur le lien et choix selon le texte explicatif) : tous les CONTRAT, même s'ils n'ont pas d'événement.

Affichage des montants

C'est vrai mais, là, tu aurais pu trouver...

Créer une requête R1_1 :
- ajouter la requête R1 (FROM) ;
- ajouter la table Evenement (FROM) ;
- lier R1 à Evenement, via Numero_contrat, Id_Type, max(Date_évènement) (JOIN) ;
- sélectionner les champs que tu veux (SELECT).
==> ATTENTION : pas de dates identiques pour un même contrat/Id_type.

Créer une requête R2_1 :
- ajouter la requête R2 (FROM) ;
- ajouter la table Evenement (FROM) ;
- lier R2 à Evenement, via Numero_contrat, Id_Type, max(Date_évènement) (JOIN) ;
- sélectionner les champs que tu veux (SELECT).
==> ATTENTION : pas de dates identiques pour un même contrat/Id_type.


Dans R3, remplacer R1 par R1_1 et R2 par R2_1.
Et là, tu devrais obtenir ce que tu souhaites.

[johnny_titan]

Bonjour,

Effectivement pour les montants j'avais trouvé entre temps. Pour le reste ça marche aussi !

Merci beaucoup en tout cas.

Thibaut