Discussion :

[Nhaps]

Bonjour,

J'essaye de faire une requête SQL me permettant de récupérer tous les horaires disponibles pour un jour particulier, sachant que :

- J'ai des horaires d'ouvertures : ex : Lundi 8h-12 / 14h-18h
- J'ai potentiellement dejà des RDV de prévu pour ce jour (Lundi 8h30 déjà reservé)
- Ma plage de RDV est de 30min

J'ai tout d'abord essayer de construire une CTE qui contient tous les horaires possibles pour une journée (déjà là je galere :/) de 00h00 à 24h00 (ex : 0000 - 0030 - 0100 - 0130 - 0200 - ... - 2300 - 2330)
Ensuite faire des jointure entre les plages d'ouverture, et les horaires déjà occupée.

Grosso modo j'imaginais avoir la liste de tous les RDV possibles
MOINS Les rdv qui ne sont pas dans les horaires d'ouverture
MOINS Les rdv déjà pris

Avez vous une piste pour me guider dans la construction de cette requête ?

[al1_24]

Il faudrait déjà connaître la structure de la ou les tables qui entrent en jeu dans cette requête (avec le type des colonnes).

[Invité]

Pour toutes les demi-heures d'une journée :

Code :

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

1
2
3
; WITH SR AS ( SELECT 0 AS N UNION ALL SELECT N + 30 FROM SR WHERE N < 30 * 47) 
SELECT CAST(DATEADD(MINUTE, N , 0) AS TIME(0)) HeureAu30Minutes
FROM SR OPTION (MAXRECURSION 100) ;

[vanagreg]

Bonjour,

Il faudrait nous donner la structure de tes tables avec un jeu de données. Si tu as des plages d'horaires par jour alors la table qui les contient ne doit pas être volumineuse.
En gros, il faut en effet générer tous les lignes de 30 minutes possibles à partir de tes plages horaires, c'est facile en utilisant une requête récursive. Il suffit alors de faire un not exists par rapport à la table des rdv pris.

[Nhaps]

Bonjour,

Merci pour le WITH, je n'ai pas pensé à cast l'entier en heure

Pour les infos de temps dans mes autres tables voici leurs structures :

TABLE_HEURE_OUVERTURE

JourSemaine entier
HeureDebut varchar(4)
HeureFin varchar(4)

Code :

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

1
2
3
 
4	0100	0930
4	1730	2330

TABLE_RDV

DateRDV datetime
HeureRDV varchar(4)

Code :

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

1
2
2014-09-05 00:00:00.000	0800
2014-09-05 00:00:00.000	1600

[Invité]

Code :

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

HeureRDV varchar(4)

Si vous faites le très mauvais choix de stocker vos heures dans un type complètement inadapté, vous vous ajoutez une bonne couche de complexité et des traitements bien moins efficace à cause de conversion entre autre.
Mais pour quelle raison ???
En plus vous avez déjà une colonne en datetime qui, comme son nom l'indique clairement permet de stocker une date ET une heure ! Mais pourquoi faire simple ?
Réviser votre modèle de données tout de suite avant de vous confronter à plein de problèmeS à la con qui n'ont pas lieu d'être.

[Nhaps]

Je sais très bien que le modèle n'est pas correct, et j'ai été tout aussi choqué que vous. Mais je n'étais pas là quand cela a été mis en place, et la modification n'est pas possible...

Je me doute que je vais devoir utiliser des CAST dans tous les sens pour pouvoir faire ce que j'essaye de faire.

Ce qui m’intéresse, c'est malgré ce modèle inadapté, y a-t-il un moyen que la logique exposée dans mon précédent message soit envisageable ?

[StringBuilder]

Voici un exemple rapide (et probablement parfaitement perfectible) :

Code sql :

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
 
with sr (n)
as
(
	select 0
	union all
	select n + 30 from sr where n < 30 * 47
),
plage (hm)
as
(
	select cast(dateadd(minute, n , 0) as time(0))
	from sr
),
horaire (id, jour, debut, fin)
as
(
	select 1, 1, '0900', '1200'
	union all
	select 2, 1, '1400', '1800'
	union all
	select 3, 2, '0800', '1300'
	union all
	select 4, 3, '1030', '1230'
	union all
	select 5, 3, '1600', '1930'
	union all
	select 6, 4, '0900', '1200'
	union all
	select 7, 4, '1400', '1800'
	union all
	select 8, 5, '0900', '1200'
	union all
	select 9, 5, '1400', '1800'
),
horairepropre (id, jour, debut, fin)
as
(
	select
		id,
		jour,
		cast(dateadd(minute, cast(substring(debut, 3, 2) as int), dateadd(hour, cast(substring(debut, 1, 2) as int), 0)) as time(0)),
		cast(dateadd(minute, cast(substring(fin, 3, 2) as int), dateadd(hour, cast(substring(fin, 1, 2) as int), 0)) as time(0))
	from horaire
),
rdv (id, creneau)
as
(
	select 1, cast('2020-05-21 15:00:00' as datetime2(0))
	union all
	select 1, cast('2020-05-21 15:30:00' as datetime2(0))
	union all
	select 1, cast('2020-05-22 09:30:00' as datetime2(0))
	union all
	select 1, cast('2020-05-22 11:00:00' as datetime2(0))
	union all
	select 1, cast('2020-05-22 15:30:00' as datetime2(0))
	union all
	select 1, cast('2020-05-22 16:00:00' as datetime2(0))
)
select p.hm dispo
from plage p
inner join horairepropre h on p.hm >= h.debut and p.hm < h.fin
where h.jour = datepart(weekday, '2020-05-22')
and p.hm not in (select cast(creneau as time(0)) from rdv where cast(creneau as date) = cast('2020-05-22' as date))
 
option (maxrecursion 47);

Ce qui donne :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
dispo
----------------
09:00:00
10:00:00
10:30:00
11:30:00
14:00:00
14:30:00
15:00:00
16:30:00
17:00:00
17:30:00
 
(10*lignes affectées)

[Invité]

Ouch, ça sent le gros projet de m*rde. Bon courage à toi !

[Nhaps]

Merci beaucoup pour le retour,

J'ai pu essayé en modifiant à divers endroits ta requête, j'arrive à de bons résultats, j'ajoute quelques contraintes que je ne vous ai pas partager et ça devrait le faire !

Et oui 7gyY9w1ZY6ySRgPeaefZ, c'est LE gros projet qui évolue avec les années mais avec la BDD qui suffoque

[StringBuilder]

Et oui 7gyY9w1ZY6ySRgPeaefZ, c'est LE gros projet qui évolue avec les années mais avec la BDD qui suffoque

Si votre base de données suffoque, mais que vous avez la main sur les devs, alors avant qu'il ne soit trop tard, lisez l'article de mon blog.

En effet, stocker des heures dans un varchar(4) c'est abominable (piètres performances, requêtes illisibles, pas de contrôle de la valeur possible, etc.)
En créant de nouvelles colonnes dans le bon type + vue ou colonne calculée qui retourne la valeur actuelle pour les traitements existants, cela vous permettra de modifier petit à petit le modèle des données pour la rendre plus "propre" sans devoir réécrire tous les programmes existants.

[Nhaps]

Merci StringBuilder, et en effet tu as totalement raison.

J'essaye de mon coté de tirer la sonnette d'alarme. Mais je suis tout nouveau, donc difficile de se faire entendre, et pas envie de forcer non plus

En tout cas ma requête fonctionne nickel avec l'ajout d'autres CTE pour finaliser mon besoin.

Merci

[Invité]

J'essaye de mon coté de tirer la sonnette d'alarme. Mais je suis tout nouveau, donc difficile de se faire entendre, et pas envie de forcer non plus

Tu peux justifier ça en disant que faire des développements avec ce genre de type employé à mauvais escient, ça va te prendre deux fois plus de temps, sans pouvoir garantir totalement des erreurs de formatages et autres.
Et que la dette technique ne fait que s'allonger à chaque nouvelle fonctionnalité.
Si tu chiffres ça en $, ça parle mieux à un petit boss.