Discussion :

[shiro-kurogane]

Bonjour à tous,

l'intitulé n'aide pas trop à comprendre ce que je souhaite, je vais essayé de m'exprimer le plus clairement possible.

Tout d'abord je vous montre ce que j'ai :



j'ai construit ma base de données physique suivant ce modèle, j'aimerai connaître les chambres disponible. Pour cela je me suis lancé dans la conception d'une vue.

Premièrement, je regarde si dans mes réservations actuelle les dates de fin de réservation sont "inférieur" à la date courante, puis je sélectionne toutes les autres chambres qui n'ont jamais était réserver.

Cependant, voilà mon problème, lorsque j'effectue une nouvelle réservation d'une même chambre (puisque maintenant la date de fin est "inférieur" à la date courante) et que je précise que la date de fin est "supérieur" à la date courante pour cette nouvelle réservation.

Elle s'affiche tout de même comme étant disponible. Je pense que c'est parce qu'elle prend la valeur de la première réservation.

Actuellement

Code :

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

1
2
3
4
5
6
7
8
create view test
 
as
 
select distinct chambre.numchambre  AS  "Chambre(s) disponibles", chambre.numHotel as "Dans l'hotel" from chambre inner join concerner on chambre.numChambre = concerner.numChambre
 inner join reservation on reservation.numReservation = concerner.numReservation where DATEDIFF( dateFin, CURDATE( ) ) < 0
UNION
SELECT chambre.numchambre  AS  "Chambre(s) disponibles", chambre.numHotel as "Dans l'hotel" FROM chambre WHERE numChambre NOT IN (SELECT numChambre FROM concerner);

Donc, je m'y suis pris autrement :

Code :

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

1
2
3
4
5
6
7
8
9
create view test
 
as
 
select distinct chambre.numchambre  AS  "Chambre(s) disponibles", chambre.numHotel as "Dans l'hotel" from chambre inner join concerner on chambre.numChambre = concerner.numChambre
 inner join reservation on reservation.numReservation = concerner.numReservation where reservation.dateFin = (SELECT MAX(numReservation) from reservation where chambre.numChambre = concerner.numChambre)
and DATEDIFF( dateFin, CURDATE( ) ) < 0
UNION
SELECT chambre.numchambre  AS  "Chambre(s) disponibles", chambre.numHotel as "Dans l'hotel" FROM chambre WHERE numChambre NOT IN (SELECT numChambre FROM concerner);

Cette fois ci le soucis c'est, qu'elle ne prend plus les chambres disponible précédent, c'est à dire qu'elle ne considère que la dernière ligne (à cause du MAX()) de la table "concerner"*

*pourquoi la table concerner ? Car je regarde si dans mes réservations actuelle les dates de fin de réservation sont "inférieur" à la date courante.

J'espère que je me suis fait comprendre .

Je vous remercie d'avance pour l'aide et les conseils.

[SQLpro]

Dans votre modèle il vous manque une entité calendrier contenant toutes les dates. Une résa étant associé à une date du calendrier. Une fois ce modèle rectifié trouver les dates des chambres disponibles est un jeu d'enfant....

A +

[shiro-kurogane]

Suis-je obligé de le faire de cette manière car en faite j'ai un cahier des charges à respecter, mais si la solution s'impose je m’exécute. Je reviendrai pour dire si ça a fonctionné ou si j'ai eu un problème entre temps

[aieeeuuuuu]

bonjour,

Essayez ceci (pas testé)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
select 
	chambre.numchambre  AS  "Chambre(s) disponibles", 
	chambre.numHotel as "Dans l'hotel" 
from chambre 
LEFT JOIN concerner 
		inner join reservation 
			on reservation.numReservation = concerner.numReservation 
	ON chambre.numChambre = concerner.numChambre
	AND CURDATE( )  BETWEEN DateDebut AND DateFin
WHERE concerner.numChambre IS NULL

[shiro-kurogane]

J'ai le même résultat que la deuxième solution que j'ai proposé tout en haut, merci quand même.

[shiro-kurogane]

J’essaie toujours sans faire de modification de la base de données (sans ajouter d'entité date) mais si j'y arrive vraiment pas je le ferai. Je continue mes recherches.

[Artemus24]

Salut à tous.

j'aimerai connaître les chambres disponible.

Pour ce que vous recherchez, vous avez juste besoin de la table des réservations et de connaitre les périodes des réservations des chambres.
Prenons un exemple, soit la chambre 201, qui possède deux réservations :
--> du 2017-05-20 jusqu'au 2017-05-21
--> du 2017-05-25 jusqu'au 2017-05-28

En ce qui concerne ces réservations, les dates sont comprises.
Soit deux jours pour la première réservation et quatre jours pour la seconde réservation.

Admettons que l'on désire connaitre si la chambre 201 est disponible de la période allant du 2017-05-23 jusqu'au 2017-05-24, soit deux jours.
Il faut donc avoir la condition suivante :
--> du 2017-05-20 jusqu'au 2017-05-21 < 2017-05-23
--> du 2017-05-25 jusqu'au 2017-05-28 > 2017-05-24
Ce qui va se traduire en prenant les noms des colonnes :
Pour la première réservation, il faut que periode_fin < 2017-05-23.
Et pour la seconde réservation, il faut que periode_deb > 2017-05-24.

Ainsi pour avoir une chambre de disponible durant la période indiquée, il faut que :
--> l'une des réservations se termine avant le début de la demande et que
--> l'autre réservation commence juste après la fin de la demande.

En MySql, cela va se traduire par l'extraction de deux lignes par chambre.
Et pour ce faire, nous extrayons une ligne contenant la période de fin de la ligne précédente et la période de début de la ligne suivante.
Nous faisons le test de la périodicité en cherchant l'inclusion, c'est-à-dire :
--> periode_deb > libre_fin et libre_deb > periode_fin

Pour mieux comprendre le pourquoi de ce test, le rôle joué par periode_deb et periode_fin sont inversés.
En fait, periode_fin concerne le début de l'intervalle et periode_deb concerne la fin de l'intervalle.

Ce qui donne le résultat recherché sur cet exemple basique :

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `reservation`
--------------
 
--------------
CREATE TABLE `reservation`
( `num_reservation`   integer unsigned  NOT NULL auto_increment primary key,
  `date_reservation`  date              NOT NULL,
  `periode_deb`       date              NOT NULL,
  `periode_fin`       date              NOT NULL,
  `nom`               varchar(255)      NOT NULL,
  `prenom`            varchar(255)      NOT NULL,
  `num_chambre`       integer unsigned  NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `reservation` (`date_reservation`,`periode_deb`,`periode_fin`,`nom`,`prenom`,`num_chambre`) values
 ('2017-05-01', '2017-05-18', '2017-05-20', 'nom 01', 'prenom 01', 101),
 ('2017-05-02', '2017-05-21', '2017-05-25', 'nom 02', 'prenom 02', 101),
 ('2017-05-03', '2017-05-26', '2017-05-28', 'nom 03', 'prenom 03', 101),
 ('2017-05-04', '2017-05-29', '2017-05-31', 'nom 04', 'prenom 04', 101),
 
 ('2017-05-01', '2017-05-18', '2017-05-19', 'nom 11', 'prenom 11', 102),
 ('2017-05-02', '2017-05-20', '2017-05-22', 'nom 12', 'prenom 12', 102),
 ('2017-05-03', '2017-05-24', '2017-05-27', 'nom 13', 'prenom 13', 102),
 ('2017-05-04', '2017-05-28', '2017-05-31', 'nom 14', 'prenom 14', 102),
 
 ('2017-05-01', '2017-05-18', '2017-05-18', 'nom 21', 'prenom 21', 103),
 ('2017-05-02', '2017-05-19', '2017-05-21', 'nom 22', 'prenom 22', 103),
 ('2017-05-03', '2017-05-23', '2017-05-26', 'nom 23', 'prenom 23', 103),
 ('2017-05-04', '2017-05-27', '2017-05-31', 'nom 24', 'prenom 24', 103),
 
 ('2017-05-07', '2017-05-18', '2017-05-19', 'nom 31', 'prenom 31', 201),
 ('2017-05-07', '2017-05-20', '2017-05-21', 'nom 32', 'prenom 32', 201),
 ('2017-05-07', '2017-05-25', '2017-05-28', 'nom 33', 'prenom 33', 201),
 ('2017-05-07', '2017-05-29', '2017-05-31', 'nom 34', 'prenom 34', 201),
 
 ('2017-05-08', '2017-05-18', '2017-05-19', 'nom 41', 'prenom 41', 202),
 ('2017-05-08', '2017-05-20', '2017-05-22', 'nom 42', 'prenom 42', 202),
 ('2017-05-09', '2017-05-26', '2017-05-29', 'nom 43', 'prenom 43', 202),
 ('2017-05-09', '2017-05-30', '2017-05-31', 'nom 44', 'prenom 44', 202),
 
 ('2017-05-10', '2017-05-18', '2017-05-21', 'nom 51', 'prenom 51', 203),
 ('2017-05-10', '2017-05-22', '2017-05-24', 'nom 52', 'prenom 52', 203),
 ('2017-05-11', '2017-05-26', '2017-05-28', 'nom 53', 'prenom 53', 203),
 ('2017-05-11', '2017-05-29', '2017-05-31', 'nom 54', 'prenom 54', 203)
--------------
 
--------------
select * from `reservation`
--------------
 
+-----------------+------------------+-------------+-------------+--------+-----------+-------------+
| num_reservation | date_reservation | periode_deb | periode_fin | nom    | prenom    | num_chambre |
+-----------------+------------------+-------------+-------------+--------+-----------+-------------+
|               1 | 2017-05-01       | 2017-05-18  | 2017-05-20  | nom 01 | prenom 01 |         101 |
|               2 | 2017-05-02       | 2017-05-21  | 2017-05-25  | nom 02 | prenom 02 |         101 |
|               3 | 2017-05-03       | 2017-05-26  | 2017-05-28  | nom 03 | prenom 03 |         101 |
|               4 | 2017-05-04       | 2017-05-29  | 2017-05-31  | nom 04 | prenom 04 |         101 |
|               5 | 2017-05-01       | 2017-05-18  | 2017-05-19  | nom 11 | prenom 11 |         102 |
|               6 | 2017-05-02       | 2017-05-20  | 2017-05-22  | nom 12 | prenom 12 |         102 |
|               7 | 2017-05-03       | 2017-05-24  | 2017-05-27  | nom 13 | prenom 13 |         102 |
|               8 | 2017-05-04       | 2017-05-28  | 2017-05-31  | nom 14 | prenom 14 |         102 |
|               9 | 2017-05-01       | 2017-05-18  | 2017-05-18  | nom 21 | prenom 21 |         103 |
|              10 | 2017-05-02       | 2017-05-19  | 2017-05-21  | nom 22 | prenom 22 |         103 |
|              11 | 2017-05-03       | 2017-05-23  | 2017-05-26  | nom 23 | prenom 23 |         103 |
|              12 | 2017-05-04       | 2017-05-27  | 2017-05-31  | nom 24 | prenom 24 |         103 |
|              13 | 2017-05-07       | 2017-05-18  | 2017-05-19  | nom 31 | prenom 31 |         201 |
|              14 | 2017-05-07       | 2017-05-20  | 2017-05-21  | nom 32 | prenom 32 |         201 |
|              15 | 2017-05-07       | 2017-05-25  | 2017-05-28  | nom 33 | prenom 33 |         201 |
|              16 | 2017-05-07       | 2017-05-29  | 2017-05-31  | nom 34 | prenom 34 |         201 |
|              17 | 2017-05-08       | 2017-05-18  | 2017-05-19  | nom 41 | prenom 41 |         202 |
|              18 | 2017-05-08       | 2017-05-20  | 2017-05-22  | nom 42 | prenom 42 |         202 |
|              19 | 2017-05-09       | 2017-05-26  | 2017-05-29  | nom 43 | prenom 43 |         202 |
|              20 | 2017-05-09       | 2017-05-30  | 2017-05-31  | nom 44 | prenom 44 |         202 |
|              21 | 2017-05-10       | 2017-05-18  | 2017-05-21  | nom 51 | prenom 51 |         203 |
|              22 | 2017-05-10       | 2017-05-22  | 2017-05-24  | nom 52 | prenom 52 |         203 |
|              23 | 2017-05-11       | 2017-05-26  | 2017-05-28  | nom 53 | prenom 53 |         203 |
|              24 | 2017-05-11       | 2017-05-29  | 2017-05-31  | nom 54 | prenom 54 |         203 |
+-----------------+------------------+-------------+-------------+--------+-----------+-------------+
--------------
set @libre_deb = '2017-05-23'
--------------
 
--------------
set @libre_fin = '2017-05-24'
--------------
 
--------------
select  *
 
  from  (
 
    select           t1.num_chambre, t1.periode_fin, t2.periode_deb
               from  reservation as t1
 
    left outer join  reservation as t2
                 on  t2.num_chambre = t1.num_chambre
                and  t2.periode_deb > t1.periode_fin
 
           group by  t1.num_chambre, t1.periode_fin
           order by  t1.num_chambre, t1.periode_fin
        ) as x
 
 where  periode_fin < @libre_deb
   and  periode_deb > @libre_fin
--------------
 
+-------------+-------------+-------------+
| num_chambre | periode_fin | periode_deb |
+-------------+-------------+-------------+
|         201 | 2017-05-21  | 2017-05-25  |
|         202 | 2017-05-22  | 2017-05-26  |
+-------------+-------------+-------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

EDIT: mon jeu d'essai n'était pas assez représentatif de la problématique.
Je l'ai complété et j'ai revu la requête produisant le résultat recherché.

@+

[shiro-kurogane]

Ok merci, je vois, est donc si j'ai bien compris "@libre_deb" et "@libre_fin" je doit les récupérer lorsque je ferai ma réservation via mon site web. Je le tente immédiatement.

[Artemus24]

Salut shiro-kurogane.

Je viens de corriger un bug dans mon exemple.

Pour déterminer l'intervalle, disons d’inoccupation de la chambre, il faut récupérer la période de fin de la ligne précédente avec la période de début de la ligne suivante.
Ce qui fait que le rôle joué par ces deux colonnes est inversée.
En terme d'intervalle, cela devient ] periode_fin ; periode_deb [
Les crochets déterminent l'exclusion.

Et donc avec l'intervalle suivant : [ libre_deb ; libre_fin ]
les crochets déterminent ici l'inclusion, nous recherchons l'inclusion stricte, ce qui va se traduire par :
periode_fin < libre_deb et libre_fin < periode_deb.

Je n'ai pas précisé mais il faut aussi que periode_fin < periode_deb mais aussi libre_deb <= libre_fin.

@+

[shiro-kurogane]

Merci, je pense avoir compris la nuance dans la comparaison des dates, merci de m'aider autant