Discussion :

[Vincent Valentine]

Bonjour,

je viens chercher des idées pour construire une requête.
Je vous expose rapidement mes besoin :
j'ai un table utilisateur, une table signe_contrat, une table contrat et une table avenant.
signe_contrat contient en clef étrangère l'id_user et id_contrat. La table avenant contient en clef étrangère l'id_contrat.
(désolé si c'est pas très clair)

Je voudrais affiché la liste des utilisateurs avec la date de fin de contrat (en cours ou fini) ou la date d'avenant (en cours ou fini) si après le contrat ils ont signé un avenant.
J'ai donc 4 cas possible : contrat en cours, contrat fini, contrat fini avec avenant en cours, contrat fini avec avenant fini.

Mon problème est d'aller chercher les infos dans la table avenant. Pour les date de contrat, c'est facile, mais pour avenant, comme ils peuvent en avoir signé plusieurs successivement, j'ai besoin de retourner que le plus récent.
Pour le moment, j'ai plein d'entrée en double, triple etc, à cause des avenants.


Je ne sais pas vers quelle solution me diriger : multi requête avec union, sous requête avec in, exists, is nul, etc

Merci de votre aide.

[escartefigue]

bonjour,

Comment sont gérées les dates quand un contrat ou un avenant est encore actif ?
Y a -t-il une date de fin nulle, une date de fin à 9999-12-31, une autre colonne indiquant la fermeture du contrat ou de l'avenant ?

[Vincent Valentine]

Pour chaque contrat / avenant, il y a une date prévu de fin de contrat, sauf dans le cas des CDI où la date est 0000-00-00 (les champs date ne peuvent être non null).
Toutes la gestion se fait à partir des dates de début et de fin de contrat.

[Artemus24]

Salut VV666.

Il nous faut le descriptif de tes tables (le create table), un jeu d'essai par table et ce que tu veux exactement obtenir comme résultat.
Sans les noms des colonnes de tes tables, il est difficile de donner une solution.

@+

[Vincent Valentine]

Voici le SQL simplifié et un jeu d'essai.

Je voudrais obtenir la vue des user avec la date de fin de contrat ou avenant (fini ou en cours), sans de doublon en raison des contrat + avenant(s).
Est-ce que le contrat est en cours ?
- oui -> j'affiche la date de fin de contrat en cours
- non --> Est-ce qu'il a un/des avenant(s) ?
--> non -> j'affiche la date de fin de contrat fini
--> oui -> j'affiche la date de fin d'avenant de le plus récent fini ou en cours (sans tenir compte des ancien avenant)

Ma requête doit me retourner 4 entrées ( = 4 user avec des fin de contrat ou d'avenants) et non 7 entrées (l'ensemble 4 contrats + 3 avenants).

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
--
-- Structure de la table `avenant`
--
 
CREATE TABLE IF NOT EXISTS `avenant` (
`num_avenant` int(2) NOT NULL,
  `profil_contrat` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `quotite_avenant` decimal(4,0) DEFAULT NULL,
  `date_debut_avenant` date DEFAULT NULL,
  `date_fin_avenant` date DEFAULT NULL,
  `date_sign_avenant` date DEFAULT NULL,
  `date_envoi_avenant` date DEFAULT NULL,
  `num_contrat` int(6) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
--
-- Structure de la table `user`
--
 
CREATE TABLE IF NOT EXISTS `user` (
`id_avs` int(6) NOT NULL,
  `num_id_allocataire_avs` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `civilite_avs` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
  `nom_usage_avs` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `nom_famille_avs` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `prenom_avs` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date_de_naissance_avs` date DEFAULT NULL,
  `adresse_avs` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tel_avs` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mobile_avs` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mail_avs` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `employeur_avs` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lieu_naiss_avs` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `observation_avs` text COLLATE utf8_unicode_ci,
  `rqth_avs` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `casier_judiciere_avs` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `idpe_avs` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `remarques_avs` text COLLATE utf8_unicode_ci,
  `remarques2_avs` text COLLATE utf8_unicode_ci,
  `commune` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `corbeille_avs` int(1) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5534 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
--
-- Structure de la table `contrat`
--
 
CREATE TABLE IF NOT EXISTS `contrat` (
`num_contrat` int(6) NOT NULL,
  `type_contrat` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `profil_contrat` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `quotite_contrat` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `date_debut_contrat` date DEFAULT NULL,
  `date_fin_contrat` date DEFAULT NULL,
  `date_sign_contrat` date DEFAULT NULL,
  `date_envoi_contrat` date DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
--
-- Structure de la table `signe_contrat`
--
 
CREATE TABLE IF NOT EXISTS `signe_contrat` (
  `num_contrat` int(6) NOT NULL,
  `id_avs` int(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
--
-- Index pour la table `avenant`
--
ALTER TABLE `avenant`
 ADD PRIMARY KEY (`num_avenant`), ADD KEY `FK_avenant_num_contrat` (`num_contrat`);
 
--
-- Index pour la table `contrat`
--
ALTER TABLE `contrat`
 ADD PRIMARY KEY (`num_contrat`);
 
--
-- Index pour la table `avs`
--
ALTER TABLE `avs`
 ADD PRIMARY KEY (`id_avs`);
 
--
-- Index pour la table `signe_contrat`
--
ALTER TABLE `signe_contrat`
 ADD PRIMARY KEY (`num_contrat`,`id_avs`), ADD KEY `FK_signe_contrat_id_avs` (`id_avs`);
 
 
INSERT INTO `avs` (`id_avs`, `num_id_allocataire_avs`, `civilite_avs`, `nom_usage_avs`, `nom_famille_avs`, `prenom_avs`, `date_de_naissance_avs`, `adresse_avs`, `tel_avs`, `mobile_avs`, `mail_avs`, `employeur_avs`, `lieu_naiss_avs`, `observation_avs`, `rqth_avs`, `casier_judiciere_avs`, `idpe_avs`, `remarques_avs`, `remarques2_avs`, `commune`, `corbeille_avs`) VALUES
(1, '', 'Mme', 'gg', 'gg', 'gg', '1995-04-19', '', '', '', '', '', '', '', '', '', '', '', '', ', 0),
(2, '', 'Mme', 'hh', 'hh', 'hh', '1962-08-11', '', '', '', '', '', '', '', '', '', '', '', '', '', 0),
(3, '', 'Mme', 'tt', 'tt', 'tt', '1995-01-16', '', '', '', '', '', '', '', '', '', '', '', '', '', 0),
(4, '', 'Mme', 'aa', 'aa', 'aa', '1993-05-15', '', '', '', '', '', '', '', '', '', '', '', '', '', 0);
 
INSERT INTO `contrat` (`num_contrat`, `type_contrat`, `profil_contrat`, `quotite_contrat`, `date_debut_contrat`, `date_fin_contrat`, `date_sign_contrat`, `date_envoi_contrat`) VALUES
(1, 'CUI', 'AEH', '10', '2013-12-09', '2014-10-08', '2013-12-09', '2013-12-09'),
(2, 'CUI', 'AEH', '24', '2014-10-01', '2016-09-30', '2014-10-01', '2014-10-01'),
(3, 'CUI', 'AEH', '24', '2013-11-01', '2015-11-01', '2013-11-01', '2013-11-01'),
(7, 'CUI', 'AEH', '36', '2011-12-01', '2012-06-30', '2011-12-01', '2011-12-01');
 
INSERT INTO `avenant` (`num_avenant`, `profil_contrat`, `quotite_avenant`, `date_debut_avenant`, `date_fin_avenant`, `date_sign_avenant`, `date_envoi_avenant`, `num_contrat`) VALUES
(11, 'AEH', 36, '2015-12-01', '2015-12-30', '2015-12-01', '2015-12-01', 3);
(10, 'AEH', 36, '2014-12-01', '2015-11-30', '2014-12-01', '2014-12-01', 7),
(11, 'AEH', 36, '2015-12-01', '2016-11-30', '2015-12-01', '2015-12-01', 7);
 
INSERT INTO `signe_contrat` (`num_contrat`, `id_avs`) VALUES
(1, 1),
(2, 2),
(3, 3),
(7, 7);

Merci :-)

[Artemus24]

Salut VV666.

Je pense avoir compris ce que tu désires obtenir.

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
--------------
select id_avs,      civilite_avs,    nom_famille_avs, prenom_avs  from avs
--------------
 
+--------+--------------+-----------------+------------+
| id_avs | civilite_avs | nom_famille_avs | prenom_avs |
+--------+--------------+-----------------+------------+
|      1 | Mme          | gg              | gg         |
|      2 | Mme          | hh              | hh         |
|      3 | Mme          | tt              | tt         |
|      4 | Mme          | aa              | aa         |
+--------+--------------+-----------------+------------+
--------------
select num_contrat, date_fin_avenant                              from avenant
--------------
 
+-------------+------------------+
| num_contrat | date_fin_avenant |
+-------------+------------------+
|           7 | 2015-11-30       |
|           7 | 2016-11-30       |
+-------------+------------------+
--------------
select num_contrat, date_fin_contrat                              from contrat
--------------
 
+-------------+------------------+
| num_contrat | date_fin_contrat |
+-------------+------------------+
|           1 | 2014-10-08       |
|           2 | 2016-09-30       |
|           3 | 2015-11-01       |
|           7 | 2012-06-30       |
+-------------+------------------+
--------------
select *                                                          from signe_contrat
--------------
 
+-------------+--------+
| num_contrat | id_avs |
+-------------+--------+
|           1 |      1 |
|           2 |      2 |
|           3 |      3 |
|           7 |      4 |
+-------------+--------+
--------------
select 'contrat',
       u.id_avs,
       u.civilite_avs,
       u.nom_famille_avs,
       u.prenom_avs,
       c.num_contrat,
       c.date_fin_contrat as date_fin
 
from       signe_contrat  as s
inner join avs            as u
on s.id_avs      = u.id_avs
 
inner join contrat        as c
on s.num_contrat = c.num_contrat
--------------
 
+---------+--------+--------------+-----------------+------------+-------------+------------+
| contrat | id_avs | civilite_avs | nom_famille_avs | prenom_avs | num_contrat | date_fin   |
+---------+--------+--------------+-----------------+------------+-------------+------------+
| contrat |      1 | Mme          | gg              | gg         |           1 | 2014-10-08 |
| contrat |      2 | Mme          | hh              | hh         |           2 | 2016-09-30 |
| contrat |      3 | Mme          | tt              | tt         |           3 | 2015-11-01 |
| contrat |      4 | Mme          | aa              | aa         |           7 | 2012-06-30 |
+---------+--------+--------------+-----------------+------------+-------------+------------+
--------------
select 'avenant',
       u.id_avs,
       u.civilite_avs,
       u.nom_famille_avs,
       u.prenom_avs,
       a.num_contrat,
       a.date_fin_avenant  as date_fin
 
from       signe_contrat  as s
inner join avs            as u
on s.id_avs      = u.id_avs
 
inner join avenant        as a
on s.num_contrat = a.num_contrat
--------------
 
+---------+--------+--------------+-----------------+------------+-------------+------------+
| avenant | id_avs | civilite_avs | nom_famille_avs | prenom_avs | num_contrat | date_fin   |
+---------+--------+--------------+-----------------+------------+-------------+------------+
| avenant |      4 | Mme          | aa              | aa         |           7 | 2015-11-30 |
| avenant |      4 | Mme          | aa              | aa         |           7 | 2016-11-30 |
+---------+--------+--------------+-----------------+------------+-------------+------------+
--------------
select 'contrat',
       u.id_avs,
       u.civilite_avs,
       u.nom_famille_avs,
       u.prenom_avs,
       c.num_contrat,
       c.date_fin_contrat as date_fin
 
from       signe_contrat  as s
inner join avs            as u
on s.id_avs      = u.id_avs
 
inner join contrat        as c
on s.num_contrat = c.num_contrat
 
where not exists (select 1
                  from avenant as z
                  where z.num_contrat = s.num_contrat
                 )
 
union
 
select 'avenant',
       u.id_avs,
       u.civilite_avs,
       u.nom_famille_avs,
       u.prenom_avs,
       a.num_contrat,
       a.date_fin_avenant  as date_fin
 
from       signe_contrat  as s
inner join avs            as u
on s.id_avs      = u.id_avs
 
inner join avenant        as a
on s.num_contrat = a.num_contrat
 
where a.date_fin_avenant = (select max(date_fin_avenant)
                            from avenant as x
                                                        where x.num_contrat = s.num_contrat
                           )
--------------
 
+---------+--------+--------------+-----------------+------------+-------------+------------+
| contrat | id_avs | civilite_avs | nom_famille_avs | prenom_avs | num_contrat | date_fin   |
+---------+--------+--------------+-----------------+------------+-------------+------------+
| contrat |      1 | Mme          | gg              | gg         |           1 | 2014-10-08 |
| contrat |      2 | Mme          | hh              | hh         |           2 | 2016-09-30 |
| contrat |      3 | Mme          | tt              | tt         |           3 | 2015-11-01 |
| avenant |      4 | Mme          | aa              | aa         |           7 | 2016-11-30 |
+---------+--------+--------------+-----------------+------------+-------------+------------+
 
Appuyez sur une touche pour continuer...

Il y a sept extractions, qui sont :

1) vidage de la table avs ou user.
2) vidage de la table avenant.
3) vidage de la table contrat.
4) vidage de la table signe_contrat.

Jusque là, rien à dire. C'est pour formaliser le jeu d'essai !

Maintenant, je sépare les deux parties, contrat et avenant.

5) Jointure que je nomme 'contrat'.
jointure entre la table 'signe_contrat' (table de départ), puis avec la table 'avs' (ou user) et enfin la table 'contrat'.
Il n'y a aucun critères restrictifs.

6) Jointure que je nomme 'avenant'.
jointure entre la table 'signe_contrat' (table de départ), puis avec la table 'avs' (ou user) et enfin la table 'avenant'.
Il n'y a aucun critères restrictifs.

7) union entre le 5) (jointure contrat) et le 6) jointure avenant).
mais on ajoute maintenant les critères suivants :

a) une ligne de la jointure contrat apparaît si et seulement si, il n'y a pas son équivalent dans la table avenant.

b) une ligne de la jointure avant apparaît en recherchant la date la plus grande pour le même numéro d'avenant.

Aucun test n'est fait entre la date de fin du contrat et la date de fin de l'avenant.
J'espère pour toi qu'il existe une vérification fonctionnelle, entre le fait qu'un avenant produit toujours une date de fin supérieure à celle du contrat.

Est-ce que cela correspond à ce que tu recherches ?
Sinon, tu devrais préciser ce qui ne va pas, voire compléter le jeu d'essai.

@+

[Vincent Valentine]

Merciiiii !!!

Ça ressemble à ce que je voudrais. J'ai volontairement simplifier mon problème car j'ai d'autres jointures pour ramener d'autres infos en plus dans la requête, mais ça ne touchait pas les contrats / avenants. Je test ça et je confirme que c'est bon.
J'arrivais pas à formuler le problème en SQL. À vu de nez ça semble très bien, je test.