Trouver la période commune à plusieurs plage de dates

Version imprimable

16/04/2015, 12h05
pierrotDev867

2 pièce(s) jointe(s)

Trouver la période commune à plusieurs plage de dates

Bonjour,

Je post sur ce forum car je rencontre une certaine difficulté à écrire une requête SQL et j'aurais aimer savoir si vous pouviez m'aiguiller pour trouver une solution..

Je m'explique, pour mon projet, je réalise un site internet sous Symfony.

Ce site est destiné à une association qui souhaite organiser des départs en vacances. Pour ce faire un tableau de disponibilités est à leur disposition. Chaque membre de l'association peut ajouter une plage de disponibilités pour un séjour donné.

J'ai donc dans ma base de données PHPMyAdmin une table intitulée "Availability" (disponibilité) qui contient entre autres un champs "date de début" et "date de fin" (dateFrom et dateTo).

Ce que je souhaite mettre en place est une recherche de la plage de date commune à toutes les disponibilités. J'aimerai faire une requête qui parcoure toutes les disponibilités qui se chevauchent et qui me récupère la période la plus commune.

J'avoue qu'après plusieurs recherches et réflexions je ne vois pas trop comment mettre en place ma requête. J'avais pensé à une intersection des plages de dates qui seraient dans les tables Availability1 et Availability2 avec les jours de la première table qui existent dans la deuxième et qui me retourne la date de début et date de fin de la période la plus commune.

Je vous ai mis en pièces-jointes les captures d'écran de la page du tableau des disponibilité du site, ainsi que ma table MySQL.

Si vous avez du temps je vous en remercie par avance ou si vous pouviez m'aiguiller vers une documentation qui pourrait m'aider.

Cordialement.

Pièce jointe 174991 Pièce jointe 174992
16/04/2015, 12h27
CinePhil
La requête ci-dessous va donner la date de début la plus lointaine et la date de fin la plus proche.
Si la date de fin trouvée est supérieure ou égale à la date de début trouvée, alors c'est la plage de dates commune à tous, sinon, il n'y a pas de plage de dates commune.
Code:

1 2 3 SELECT MAX(dateFrom) AS debut_periode_commune, MIN(dateTo) AS fin_periode_commune, FROM Availability
16/04/2015, 12h34
pierrotDev867

Merci beaucoup pour la réponse, en effet la solution est peut être bien plus simple que je ne pouvais m'imaginer :/

Cependant j'ai testé cette requête dans PHPMyAdmin mais il me renvoie ceci une erreur de syntaxe apparemment:

MySQL a répondu: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM Availability
LIMIT 0, 30' at line 3
16/04/2015, 12h35
CinePhil

Il y a une virgule en trop avant le FROM. :oops:
16/04/2015, 12h37
pierrotDev867

Autant pour moi après vérification la requête prend le jour de début maximum et la date de fin minimum, cependant il prends ces dates même si elles ne font pas partie des autres plages de disponibilités. :?

Par exemple dans mon cas j'ai ces dispos:

Du 02-02-2017 au 10-03-2017
Du 13-06-2015 au 09-07-2015
Du 01-01-2017 au 16-09-2017

et il me retourne :

02/02/17 au 09/07/15

alors que je voudrai:

du 02-02-2017 au 10-03-2017 à savoir celle qui est comprise dans les autres plages de disponibilité et dont les jours sont compris dans les autres périodes.
16/04/2015, 13h11
pierrotDev867

Il faudrait peut être rajouter une condition qui vérifie l'existence des dates dans les autres périodes. ce n'est pas réalisable avec l'équivalent d'un INTERSECT en MySQL?
16/04/2015, 15h20
CinePhil

Citation:

Envoyé par pierrotDev867

Autant pour moi après vérification la requête prend le jour de début maximum et la date de fin minimum, cependant il prends ces dates même si elles ne font pas partie des autres plages de disponibilités. :?
/
Par exemple dans mon cas j'ai ces dispos:

Du 02-02-2017 au 10-03-2017
Du 13-06-2015 au 09-07-2015
Du 01-01-2017 au 16-09-2017

et il me retourne :

02/02/17 au 09/07/15

alors que je voudrai:

du 02-02-2017 au 10-03-2017 à savoir celle qui est comprise dans les autres plages de disponibilité et dont les jours sont compris dans les autres périodes.

Euh... du 02/02/2017 au 10/03/2017, ce n'est pas compris dans la plage du 13/06/2015 au 09/07/2015 !
Quand bien même cette dernière période serait située en 2017 comme les deux autres, la première période n'aurait rien de commun avec la deuxième.

C'est donc bien ce que j'ai dit dans mon message :

Citation:

Envoyé par CinéPhil

Si la date de fin trouvée est supérieure ou égale à la date de début trouvée, alors c'est la plage de dates commune à tous, sinon, il n'y a pas de plage de dates commune.

Le besoin exprimé était celui-ci :

Citation:

Envoyé par pierrotDev867

Ce que je souhaite mettre en place est une recherche de la plage de date commune à toutes les disponibilités.

Si vous voulez filtrer sur certaines plages à analyser, il faut ajouter un WHERE à ma requête. Ainsi, si on exclue de la requête la deuxième plage de votre liste ci-dessus, ma requête devrait bel et bien donner la plage du 02/02/2017 au 10/03/2017.

Citation:

Il faudrait peut être rajouter une condition qui vérifie l'existence des dates dans les autres périodes. ce n'est pas réalisable avec l'équivalent d'un INTERSECT en MySQL?

INTERSECT n'existe pas en MySQL et ne serait même d'aucune utilité ici. Il existe aussi, en SQL, la fonction OVERLAPS mais qui n'existe pas non plus chez MySQL.

Vous pouvez lire ce billet de SQLPro sur les calculs sur les périodes :
http://sqlpro.developpez.com/cours/gestiontemps/
16/04/2015, 15h39
pierrotDev867

Oui en effet je me suis mal exprimé dsl.

La période la plus commune correspond aux dates comprises dans le maximum de périodes possibles voisines et non à toutes les périodes.

Le problème devient beaucoup plus compliqué ! :calim2:

Commence par vérifier si cette requête te donne les bonnes dates communes pour chaque période avec toutes les autres :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT a1.id AS id_1, a1.dateFrom AS debut_1, a1.dateTo AS fin_1,
	a2.id AS id_2, a2.dateFrom AS debut_2, a2.dateTo AS fin_2,
	CASE
		WHEN a1.dateFrom >= a2.DateFrom AND a1.dateFrom < a2.dateTo THEN a1.dateFrom
		WHEN a1.dateFrom < a2.dateFrom AND a1.dateFrom < a2.dateTo THEN a2.dateFrom
		ELSE NULL
	END AS date_debut_commune,
	CASE
		WHEN a1.dateTo <= a2.dateTo AND a1.dateTo > a2.dateFrom THEN a1.dateTo
		WHEN a1.dateTo > a2.dateTo AND a1.dateTo > a2.dateFrom THEN a2.dateTo
		ELSE NULL
	END AS date_fin_commune
FROM Availability a1
INNER JOIN Availability a2 ON a1.id <> a2.id
ORDER BY a1.id

Si une ligne de résultat présente un NULL, c'est qu'il n'y a pas de possibilité de période commune entre les deux périodes considérées par la ligne.

Si c'est bon, on peut appliquer ma première requête de tout à l'heure pour obtenir, pour chaque période considérée, la période commune à toutes les autres et le nombre de fois que cette période commune fonctionne :

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
SELECT tmp.id_1,
	MAX(tmp.date_debut_commune) AS debut_periode_commune,
	MIN(tmp.date_fin_commune) AS fin_periode_commune,
	COUNT(*) AS nb_plages_communes
FROM 
(
	SELECT a1.id AS id_1, a1.dateFrom AS debut_1, a1.dateTo AS fin_1,
		a2.id AS id_2, a2.dateFrom AS debut_2, a2.dateTo AS fin_2,
		CASE
			WHEN a1.dateFrom >= a2.DateFrom AND a1.dateFrom < a2.dateTo THEN a1.dateFrom
			WHEN a1.dateFrom < a2.dateFrom AND a1.dateFrom < a2.dateTo THEN a2.dateFrom
			ELSE NULL
		END AS date_debut_commune,
		CASE
			WHEN a1.dateTo <= a2.dateTo AND a1.dateTo > a2.dateFrom THEN a1.dateTo
			WHEN a1.dateTo > a2.dateTo AND a1.dateTo > a2.dateFrom THEN a2.dateTo
			ELSE NULL
		END AS date_fin_commune
	FROM Availability a1
	INNER JOIN Availability a2 ON a1.id <> a2.id
) tmp
WHERE tmp.date_debut_commune IS NOT NULL
	AND tmp.date_fin_commune IS NOT NULL
GROUP BY tmp.id_1
ORDER BY tmp.id_1

Faisons une vue de cette requête qu'on va réutiliser, ce sera plus simple :

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
CREATE VIEW v_plages_communes AS
SELECT tmp.id_1,
	MAX(tmp.date_debut_commune) AS debut_periode_commune,
	MIN(tmp.date_fin_commune) AS fin_periode_commune,
	COUNT(*) AS nb_plages_communes
FROM 
(
	SELECT a1.id AS id_1, a1.dateFrom AS debut_1, a1.dateTo AS fin_1,
		a2.id AS id_2, a2.dateFrom AS debut_2, a2.dateTo AS fin_2,
		CASE
			WHEN a1.dateFrom >= a2.DateFrom AND a1.dateFrom < a2.dateTo THEN a1.dateFrom
			WHEN a1.dateFrom < a2.dateFrom AND a1.dateFrom < a2.dateTo THEN a2.dateFrom
			ELSE NULL
		END AS date_debut_commune,
		CASE
			WHEN a1.dateTo <= a2.dateTo AND a1.dateTo > a2.dateFrom THEN a1.dateTo
			WHEN a1.dateTo > a2.dateTo AND a1.dateTo > a2.dateFrom THEN a2.dateTo
			ELSE NULL
		END AS date_fin_commune
	FROM Availability a1
	INNER JOIN Availability a2 ON a1.id <> a2.id
) tmp
WHERE tmp.date_debut_commune IS NOT NULL
	AND tmp.date_fin_commune IS NOT NULL
GROUP BY tmp.id_1

Maintenant je peux chercher la période commune qui marche le plus de fois :

Code:

1
2
SELECT MAX(nb_plages_communes) AS meilleur_score
FROM v_plages_communes

Puis on récupère la ou les plages qui ont le meilleur score :

Code:

1
2
3
4
5
6
7
SELECT debut_periode_commune, fin_periode_commune
FROM v_plages_communes
WHERE nb_plages_communes = 
(
	SELECT MAX(nb_plages_communes) AS meilleur_score
	FROM v_plages_communes
)

À tester étape par étape.

Ok déjà merci beaucoup de vous pencher autant sur mon problème ! 8O

Je n'ai le temps pour le moment de ne tester que la première requête qui me prenait pour le deuxieme WHEN de chaque CASE les périodes complètement différentes j'ai par conséquent rajouté ceci:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT a1.id AS id_1, a1.dateFrom AS debut_1, a1.dateTo AS fin_1, a2.id AS id_2, a2.dateFrom AS debut_2, a2.dateTo AS fin_2, CASE WHEN a1.dateFrom >= a2.DateFrom AND a1.dateFrom < a2.dateTo THEN a1.dateFrom WHEN a1.dateFrom < a2.dateFrom AND a1.dateFrom < a2.dateTo AND a1.dateTo <= a2.dateTo THEN a2.dateFrom ELSE NULL END AS date_debut_commune, CASE WHEN a1.dateTo <= a2.dateTo AND a1.dateTo > a2.dateFrom THEN a1.dateTo WHEN a1.dateTo > a2.dateTo AND a1.dateTo > a2.dateFrom AND a1.dateFrom <= a2.dateTo THEN a2.dateTo ELSE NULL END AS date_fin_commune FROM Availability a1 INNER JOIN Availability a2 ON a1.id <> a2.id
De cette manière je suis sûr que les dates de fin et de début concernant une autre période dont les dates début et fin sont différentes retourneront NULL.

Malheureusement je n'ai pas le temps ce soir de tester le reste, je teste les autres requêtes demain dès que je peux.

Encore merci!

En lisant ta réponse hier soir et en revoyant ma requête, je me suis rendu compte de ma bêtise ! :oops:

Code:

WHEN a1.dateFrom < a2.dateFrom AND a1.dateFrom < a2.dateTo

Si la date de début de a1 est inférieure à la date début de a2, elle est forcément inférieure aussi à la date de fin de a2 ! :mrgreen:
Le second test est donc inutile !

Code:

WHEN a1.dateTo > a2.dateTo AND a1.dateTo > a2.dateFrom

Idem ! :mrgreen:

Tu as donc eu raison de changer la condition mais du coup on peut simplifier ta requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT a1.id AS id_1, a1.dateFrom AS debut_1, a1.dateTo AS fin_1,
	a2.id AS id_2, a2.dateFrom AS debut_2, a2.dateTo AS fin_2,
	CASE
		WHEN a1.dateFrom >= a2.DateFrom AND a1.dateFrom < a2.dateTo THEN a1.dateFrom
		WHEN a1.dateFrom < a2.dateFrom AND a1.dateTo <= a2.dateTo THEN a2.dateFrom
		ELSE NULL
	END AS date_debut_commune,
	CASE
		WHEN a1.dateTo <= a2.dateTo AND a1.dateTo > a2.dateFrom THEN a1.dateTo
		WHEN a1.dateTo > a2.dateTo AND a1.dateFrom <= a2.dateTo THEN a2.dateTo
		ELSE NULL
	END AS date_fin_commune
FROM Availability a1
INNER JOIN Availability a2 ON a1.id <> a2.id
ORDER BY a1.id

On peut même encore simplifier puisque, dans un CASE, les conditions WHEN sont, en principe, testées dans l'ordre où elles sont écrites. Si le premier WHEN est satisfait alors on a le résultat du CASE et on passe à la suite de la requête sans examiner le second WHEN.
Ainsi, si le test a1.dateTo <= a2.dateTo n'est pas satisfait, inutile de tester son contraire dans le second WHEN ; il sera automatiquement satisfait.
La requête devient :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT a1.id AS id_1, a1.dateFrom AS debut_1, a1.dateTo AS fin_1,
	a2.id AS id_2, a2.dateFrom AS debut_2, a2.dateTo AS fin_2,
	CASE
		WHEN a1.dateFrom >= a2.DateFrom AND a1.dateFrom < a2.dateTo THEN a1.dateFrom
		WHEN a1.dateTo <= a2.dateTo THEN a2.dateFrom
		ELSE NULL
	END AS date_debut_commune,
	CASE
		WHEN a1.dateTo <= a2.dateTo AND a1.dateTo > a2.dateFrom THEN a1.dateTo
		WHEN a1.dateFrom <= a2.dateTo THEN a2.dateTo
		ELSE NULL
	END AS date_fin_commune
FROM Availability a1
INNER JOIN Availability a2 ON a1.id <> a2.id
ORDER BY a1.id

La vue devient donc celle-ci :

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
CREATE VIEW v_plages_communes AS
SELECT tmp.id_1,
	MAX(tmp.date_debut_commune) AS debut_periode_commune,
	MIN(tmp.date_fin_commune) AS fin_periode_commune,
	COUNT(*) AS nb_plages_communes
FROM 
(
	SELECT a1.id AS id_1, a1.dateFrom AS debut_1, a1.dateTo AS fin_1,
		a2.id AS id_2, a2.dateFrom AS debut_2, a2.dateTo AS fin_2,
		CASE
			WHEN a1.dateFrom >= a2.DateFrom AND a1.dateFrom < a2.dateTo THEN a1.dateFrom
			WHEN a1.dateTo <= a2.dateTo THEN a2.dateFrom
			ELSE NULL
		END AS date_debut_commune,
		CASE
			WHEN a1.dateTo <= a2.dateTo AND a1.dateTo > a2.dateFrom THEN a1.dateTo
			WHEN a1.dateFrom <= a2.dateTo THEN a2.dateTo
			ELSE NULL
		END AS date_fin_commune
	FROM Availability a1
	INNER JOIN Availability a2 ON a1.id <> a2.id
) tmp
WHERE tmp.date_debut_commune IS NOT NULL
	AND tmp.date_fin_commune IS NOT NULL
GROUP BY tmp.id_1

17/04/2015, 10h57
pierrotDev867

Ok j'ai essayé de décortiquer votre vue et c'est vraiment bien vu!!

Cependant PHPMyAdmin me retourne cette erreur lorsque j'essaye d'exécuter la vue:

MySQL a répondu: Documentation

#1349 - View's SELECT contains a subquery in the FROM clause

:?

Encore un beau défaut de MySQMerde ! :aie:

Citation:

Envoyé par MySQL_Doc

A view definition is subject to the following restrictions:

The SELECT statement cannot contain a subquery in the FROM clause.

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

Donc il faut faire deux vues :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE VIEW v_recherche_plages_communes AS
SELECT a1.id AS id_1, a1.dateFrom AS debut_1, a1.dateTo AS fin_1,
	a2.id AS id_2, a2.dateFrom AS debut_2, a2.dateTo AS fin_2,
	CASE
		WHEN a1.dateFrom >= a2.DateFrom AND a1.dateFrom < a2.dateTo THEN a1.dateFrom
		WHEN a1.dateTo <= a2.dateTo THEN a2.dateFrom
		ELSE NULL
	END AS date_debut_commune,
	CASE
		WHEN a1.dateTo <= a2.dateTo AND a1.dateTo > a2.dateFrom THEN a1.dateTo
		WHEN a1.dateFrom <= a2.dateTo THEN a2.dateTo
		ELSE NULL
	END AS date_fin_commune
FROM Availability a1
INNER JOIN Availability a2 ON a1.id <> a2.id

Et la vue finale qui devient :

Code:

1
2
3
4
5
6
7
8
9
CREATE VIEW v_plages_communes AS
SELECT tmp.id_1,
	MAX(tmp.date_debut_commune) AS debut_periode_commune,
	MIN(tmp.date_fin_commune) AS fin_periode_commune,
	COUNT(*) AS nb_plages_communes
FROM v_recherche_plages_communes tmp
WHERE tmp.date_debut_commune IS NOT NULL
	AND tmp.date_fin_commune IS NOT NULL
GROUP BY tmp.id_1

17/04/2015, 12h07
pierrotDev867

Eh bien chapeau bas l'artiste, je dois avouer que je suis assez impressionné ! 8O

Les vues ronronnent et me pondent le résultat attendu, je pense que je n'y serai jamais arrivé sans votre aide encore une fois merci beaucoup! tout fonctionne à présent ! :D