Discussion :

[bib34690]

Bonjour,

J'ai 1 table qui contient des Rdv : DateTime pour date et heure début, Time pour la durée (toujours multiple de 5 mn, n'excède jamais 12 heures). Le Rdv n'est jamais à cheval sur 2 jours.
Comment faire pour trouver tous les conflits de la base : 2 Rdv sur la même plage le même jour ?
Il peut y avoir 1 très grand nombre de Rdv sur plusieurs années.

Attention, il y a conflit dès qu'il se chevauchent de 5 mn, même s'ils ne commencent pas ou ne finissent pas à la même heure.

Merci pour votre aide.
Bib

[pcaboche]

Ok, analysons les contraintes :

Il peut y avoir 1 très grand nombre de Rdv sur plusieurs années.

Donc forte volumétrie. (contrainte 1)

Attention, il y a conflit dès qu'il se chevauchent de 5 mn, même s'ils ne commencent pas ou ne finissent pas à la même heure.

Ce qui oblige à faire des DATEADD (de -5 minutes sur le début, et +5 minutes sur la fin).
De par l'usage de ces fonctions, SQL Server sera incapable d'utiliser d'éventuels index, donc ça serait lent (contrainte 2).

Le Rdv n'est jamais à cheval sur 2 jours.

Bon, au moins c'est ça de gagné... ("contrainte" 3)

----

D'une certaine façon, on peut voir cela comme un problème de géométrie :
- des polygones avec comme coordonnées :
-> en abscise : une représentation numérique des dates
-> en ordonnées : une représentation numérique des plages horaires (début -5min, fin +5min)
- déterminer quels polygones s'intersectent

Pour cela, on pourrait utiliser les fonctions de géométrie de 2008 :
http://msdn.microsoft.com/en-us/library/bb933899.aspx

Cependant :
- on ne sait pas quelle version de SQL Server vous utilisez (c'est pour cela qu'il est important de le préciser dans le titre du sujet )
- je n'ai jamais essayé, donc je ne sais pas quelles performances on peut avoir (en utilisant des index spatiaux bien sûr)

Donc à la limite, vous pouvez envisager de rajouter une colonne de géométrie (avec un index spatial) pour pouvoir faire ce genre de requête (intersect).

---

Sinon, solution plus traditionnelle, au vu des contraintes :

On va avoir besoin d'une table temporaire, indexée, sinon ça va être trop lent (contraintes 1 et 2).

Cette table intermédiaire (#t) contiendra :
- le rendez-vous (RdvID ?)
- le jour (Day), sans les heures (00:00)
- heure de début, moins 5 minutes (TimeStart)
- heure de fin, plus 5 minutes (TimeEnd)

-> Remplir cette table (calcul du jour, heures de début et fin)

-> Créer un index couvrant les champs sus-mentionnés, pour faire différentes jointures
(oui, on créé l'index après avoir inséré les données, ça va plus vite...)

-> Pour détecter les conflits, faire une requête avec une jointure du genre :

Code :

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

1
2
3
4
5
6
7
8
9
FROM #t A
INNER JOIN #t B
  ON (A.Day = B.Day
    AND (
       (A.TimeStart < B.TimeStart AND B.TimeStart < A.TimeEnd)
    OR (A.TimeStart < B.TimeEnd   AND B.TimeEnd   < A.TimeEnd)
    -- OR (B.TimeStart < A.TimeStart AND A.TimeStart < B.TimeEnd)
    -- OR (B.TimeStart < A.TimeEnd   AND A.TimeEnd   < B.TimeEnd)
  ))

Si vous voulez que la relation soit réflexive (deux lignes par conflit : A conflicte avec B, et B conflicte avec A), enlevez les commentaires.

(note : je n'aime pas l'opérateur BETWEEN car il est inclusif, et ce n'est pas toujours ce que l'on veut)

-> Pour finir, jointures sur votre table de rendez-vous (RdvID)


Voilà.

Si quelqu'un a une autre idée...

[tbc92]

Presque comme pcaboche,
sauf que la requête qu'il propose va renvoyer 2 lignes pour chaque rendez-vous ( a est en conflit avec B et B en conflit avec A ).
Pour avoir une seule ligne par rendez-vous, il faut retirer la ligne qui commence par OR ( ...) .
Et autre problème dans cette requête, elle ne va pas afficher les rendez-vous A et B s'ils commencent exactement en même temps, ou s'ils finissent exactement en même temps.
J'imagine qu'il faut aussi ajouter un autre filtre : and A.Individu = B.Individu.

Reste aussi à mieux définir le besoin :
Si 2 rendez-vous sont en conflit, veut-on 2 lignes (A en conflit avec B, et B en conflit avec A) ou veut-on une seule ligne. Et dans ce cas, quelle ligne ?
Faute de précisions complémentaires, la requête ci-dessous est une base :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT *
FROM #t A
INNER JOIN #t B
  ON A.Day = B.Day 
    AND A.Individu = B.Individu
    AND A.TimeStart <= B.TimeStart 
    AND B.TimeStart < A.TimeEnd

Si 2 rendez-vous A et B commencent en même temps, veut-on une seule ligne, ou bien 2

[aieeeuuuuu]

Bonjour,

DateTime pour date et heure début, Time pour la durée (toujours multiple de 5 mn, n'excède jamais 12 heures).

Vous avez déjà un premier problème : TIME est conçu pour stocker des heures, non pas des durées.
Vous devriez passer cette colonne en INT, voire SMALLINT.

Vu le contexte, il me semblerait même plus adéquat de placer l'heure de début et l'heure de fin (vous pouvez éventuellement prévoir une colonne calculée pour indiquer la durée)
ça permettrait des calculs simplifiés avec des index sur ces deux colonnes.

[pcaboche]

Vous avez déjà un premier problème : TIME est conçu pour stocker des heures, non pas des durées.
Vous devriez passer cette colonne en INT, voire SMALLINT.

Ok, j'avais cru que c'était le nom de la colonne, et une mauvaise traduction comme on sait si bien les faire en français :
durée -> temps => "time"

ça permettrait des calculs simplifiés avec des index sur ces deux colonnes.

Le problème, c'est qu'il veut rajouter encore 5 minutes de battement au temps prévu, donc les index ne marcheront plus (à cause du DATEADD).

Au passage, si on ajoute 5 minutes au début et 5 minutes à la fin, ça fait un battement de 10 minutes, pas 5.
Donc par rapport à ma réponse précédente, il faut ajouter 5 minutes à la fin, et c'est tout (on garde la date de début telle quelle).

[bib34690]

Woaw...ça c'est de la réponse, merci les gars.
Merci de prendre le temps d'expliquer.
Je suis novice en SQL, je n'avais pas eu l'idée de faire 1 Join d'1 table sur elle même...bien sûr !

Désolé pour mes imprécisions :
- Version SQL : 2012 Express.
- Les champs : c'est bien l'heure de fin (au format Time) qui figure en base et non la durée.
- Si 2 Rdv sont en conflits, on veut les 2 : oui.

Pour le concept des STIntersects, l'idée est géniale, c'est effectivement exactement cela, mais l'exemple MS est trop limité et je n'ai pas réussi à le réaliser.
Je ferai qq recherches pour voir si je trouve des exemples plus complets...si vous en avez 1 sous la main...

Pour la solution traditionnelle :
Je pense qu'on peut se passer d'ajouter 5 mn en jouant sur les >= ou >.
Je crois aussi qu'il y a 1 petite erreur dans la formule qui doit être :

Code :

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

B.TimeStart < A.TimeEnd and B.TimeEnd > A.TimeStart

Du coup, je me demande s'il est nécessaire de créer la table temporaire ?
Est-ce que je ne pourrais pas essayer directement sur la table :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT *
FROM MesRdv A
INNER JOIN MesRdv B
  ON A.RdvId <> B.RdvId
    AND A.Individu = B.Individu
    AND CAST(A.DateTimeStart, DATE) = CAST(B.DateTimeStart, DATE)
    AND CAST(B.DateTimeStart, TIME) < A.TimeEnd
    AND B.TimeEnd < CAST(A.DateTimeDeb, TIME)

Je ne comprends par contre pas le concept du Rdv sélectionné : si A est en conflit avec B, B l'est forcément avec A, non ?
Pourquoi n'en sortirait-il qu'un seul et rajouter (si j'ai bien compris) un test pour sortir le 2ème ?

@tbc92 : Je n'ai pas compris l'allusion à "Individu" (sauf si tu as lu dans mes pensées que je pouvais avoir des Rdv pour +sieurs individus, ce que je n'avais pas mentionné pour ne pas compliquer) mais je suppose que A.RdvId <> B.RdvId sera nécessaire pour ne pas sortir chaque Rdv en conflit avec lui-même.

En tout cas merci beaucoup pour votre aide précieuse et pertinente : je vais faire des tests et je vous tiendrai informés des résultats...

[pcaboche]

Je pense qu'on peut se passer d'ajouter 5 mn en jouant sur les >= ou >.
Je crois aussi qu'il y a 1 petite erreur dans la formule qui doit être :

On veut tester si l'heure de début de B se trouve entre l'heure de début de A et l'heure de fin de A.
Donc :

Code :

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

A.TimeStart <= B.TimeStart  AND B.TimeStart < A.TimeEnd

Inversez A et B si ça vous parle plus.

Du coup, je me demande s'il est nécessaire de créer la table temporaire ?

Le problème, c'est que dès l'instant qu'on utilise une fonction (DATEADD, CAST...), les index ne marchent plus (contrainte 2).
Avec une forte volumétrie (contrainte 1), c'est lent.

Donc on calcul d'abord, on index le résultat (temporaire), et on fait la jointure.

Je ne comprends par contre pas le concept du Rdv sélectionné : si A est en conflit avec B, B l'est forcément avec A, non ?
Pourquoi n'en sortirait-il qu'un seul et rajouter (si j'ai bien compris) un test pour sortir le 2ème ?

Pour tout conflit entre a et b, vous pouvez choisir dávoir en retour une ligne ( [(a,b)] ) ou deux ( [(a,b); (b,a)] ).

Dans certains cas, on veux avoir 2 lignes et choisir laquelle on affiche (ex: on souhaite que a soit le rendez-vous qui commence en premier : A.TimeStart < B.TimeStart mauvais exemple, vu qu'on fait déjà ce test...
ex2: on souhaite que a soit le rendez-vous qui dure le plus longtemps)

Edit: Mauvais exemple (il y a des jours, je manque de caféine...)

[SQLpro]

Bonjour,

J'ai 1 table qui contient des Rdv : DateTime pour date et heure début, Time pour la durée (toujours multiple de 5 mn, n'excède jamais 12 heures). Le Rdv n'est jamais à cheval sur 2 jours.
Comment faire pour trouver tous les conflits de la base : 2 Rdv sur la même plage le même jour ?
Il peut y avoir 1 très grand nombre de Rdv sur plusieurs années.

Attention, il y a conflit dès qu'il se chevauchent de 5 mn, même s'ils ne commencent pas ou ne finissent pas à la même heure.

Merci pour votre aide.
Bib

C'est votre modèle qui est foireux. Lorsque l'on veut gérer un planning de RV on créé une table de planning comportant tous les créneaux de RV dans le futur, par exemple pour 30 ans, et ensuite on utilise 1 ou plusieurs créneau pour assigner un RV à une personne.
Avec les contraintes d'unicité qui vont bien, vous n'aurez jamais de chevauchement...

Tout autre modèle sera imbitable au niveau des requêtes et catastrophiquement contre performant !

A +