Discussion :

[stany91]

bonjour à tous,
sous une base mysql5.
Voic mes 2 tables

table Logement
id
adresse
proprietaire

table location
id
logement_id (id du logement qui est lié à la location)
debut (date timestamp debut de location)
fin (date timestamp fin de location)

Je veux que la requete me récupère tous les id des logements (une seule fois par id) qui n'ont pas ou du moins pas en totalité de location durant une periode donnée (en gros entre deux variables debut_loc et fin_loc).
Je tourne en rond.
Attention, je veux aussi les id des logements qui n'ont jamais été entré dans la table location (C.A.D qui n'ont jamais été sujet à location).
Merci de votre aide à tous !

[SQLpro]

SQL ne saurait inventer des données qui n'existent pas dans votre base. En l'occurrence votre modèle de données est incomplet et c'est une faute très classique de modélisation... Avez vous un MCD ? Je suppose que non évidemment sinon la faute vous sauterais aux yeux...

Pour que vous puissiez faire ce genre de requête il vous faut ajouter à votre modèle une table des dates avec une ligne pour toutes les dates de 1900 à 2100...
Dès lors vos requêtes vont devenir triviales.
Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/gestiontemps/

Démonstration :

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
CREATE TABLE T_LOGEMENT_LOG
(LOG_ID         INT NOT NULL PRIMARY KEY,
 LOG_PROPRIO    VARCHAR(16))
 
INSERT INTO T_LOGEMENT_LOG VALUES (1, 'DUPONT')
INSERT INTO T_LOGEMENT_LOG VALUES (2, 'DUFOUR')
INSERT INTO T_LOGEMENT_LOG VALUES (3, 'DUBOUT')
 
CREATE TABLE T_LOCATION_LOC
(LOC_ID         INT NOT NULL PRIMARY KEY,
 LOG_ID         INT NOT NULL FOREIGN KEY REFERENCES T_LOGEMENT_LOG (LOG_ID),
 LOC_DATE_DEBUT DATE NOT NULL,
 LOC_DATE_FIN   DATE NOT NULL)
 
INSERT INTO T_LOCATION_LOC VALUES (1, 2, '2007-08-03', '2007-08-05')
INSERT INTO T_LOCATION_LOC VALUES (2, 3, '2007-08-01', '2007-08-10')
 
CREATE TABLE T_CALENDRIER_CLD
(CLD_JOUR       DATE NOT NULL PRIMARY KEY)
 
INSERT INTO T_CALENDRIER_CLD VALUES ('2007-07-31')
INSERT INTO T_CALENDRIER_CLD VALUES ('2007-08-01')
INSERT INTO T_CALENDRIER_CLD VALUES ('2007-08-02')
INSERT INTO T_CALENDRIER_CLD VALUES ('2007-08-03')
INSERT INTO T_CALENDRIER_CLD VALUES ('2007-08-04')
INSERT INTO T_CALENDRIER_CLD VALUES ('2007-08-05')
INSERT INTO T_CALENDRIER_CLD VALUES ('2007-08-06')
INSERT INTO T_CALENDRIER_CLD VALUES ('2007-08-07')
INSERT INTO T_CALENDRIER_CLD VALUES ('2007-08-08')
INSERT INTO T_CALENDRIER_CLD VALUES ('2007-08-09')
INSERT INTO T_CALENDRIER_CLD VALUES ('2007-08-10')
INSERT INTO T_CALENDRIER_CLD VALUES ('2007-08-11')

Recherchons les logements ayant au moins 1 jour vacant entre le 1 et le 10 aout 2007 inclus....
Alors la requête suivante vous donne la solution :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT LOG.LOG_ID, LOG_PROPRIO
FROM   T_LOGEMENT_LOG LOG
       LEFT OUTER JOIN T_LOCATION_LOC LOC
            ON LOG.LOG_ID = LOC.LOG_ID
       CROSS JOIN T_CALENDRIER_CLD CLD
WHERE  CLD.CLD_JOUR BETWEEN '2007-08-01' AND '2007-08-10'
GROUP BY LOG.LOG_ID, LOG_PROPRIO
HAVING COALESCE(SUM(INTERVAL LOC_DATE_FIN - LOC_DATE_DEBUT DAY + 1) /
                COUNT(DATEDIFF(day, LOC_DATE_DEBUT, LOC_DATE_FIN) + 1), 0)
       < COUNT(CLD_JOUR)

on peut même l'améliorer en affichant le nombre de jours loués et le nombre de jours de la période :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT LOG.LOG_ID, LOG_PROPRIO, COUNT(CLD_JOUR) AS NOMBRE_JOUR_PERIODE,
       COALESCE(SUM(DATEDIFF(day, LOC_DATE_DEBUT, LOC_DATE_FIN) + 1)
       /  COUNT(DATEDIFF(day, LOC_DATE_DEBUT, LOC_DATE_FIN) + 1), 0)
       AS NOMBRE_JOUR_OCCUPE
FROM   T_LOGEMENT_LOG LOG
       LEFT OUTER JOIN T_LOCATION_LOC LOC
            ON LOG.LOG_ID = LOC.LOG_ID
       CROSS JOIN T_CALENDRIER_CLD CLD
WHERE  CLD.CLD_JOUR BETWEEN '2007-08-01' AND '2007-08-10'
GROUP BY LOG.LOG_ID, LOG_PROPRIO
HAVING COALESCE(SUM(INTERVAL LOC_DATE_FIN - LOC_DATE_DEBUT DAY + 1) /
                COUNT(DATEDIFF(day, LOC_DATE_DEBUT, LOC_DATE_FIN) + 1), 0)
       < COUNT(CLD_JOUR)

Avec le résultat suivant :

Code :

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

1
2
3
4
LOG_ID      LOG_PROPRIO      NOMBRE_JOUR_PERIODE NOMBRE_JOUR_OCCUPE 
----------- ---------------- ------------------- ------------------ 
1           DUPONT           10                  0
2           DUFOUR           10                  3

Et pour un cours sur SQL, mon site web SQLpro, comme mes bouquins peuvent vous aider à l'apprendre...

PS : désolé de poster en "pur" SQL, mais j'évite MySQL car il utilise beaucoup de choses qui n'ont rien à voir avec le langage SQL...

A +