Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Récupérer les horaires disponibles


Sujet :

Développement SQL Server

  1. #1
    Membre confirmé
    Récupérer les horaires disponibles
    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 ?
    Windev 23 - SQL SERVER - PHP
    Play : TFT - Jeux indé

  2. #2
    Modérateur

    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).
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  3. #3
    Expert éminent
    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) ;
    les règles du forum - mode d'emploi du forum
    Aucun navigateur ne propose d'extension boule-de-cristal : postez votre code et vos messages d'erreurs. (Rappel : "ça ne marche pas" n'est pas un message d'erreur)
    JE NE RÉPONDS PAS aux questions techniques par message privé.

  4. #4
    Membre confirmé
    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.

  5. #5
    Membre confirmé
    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
    Windev 23 - SQL SERVER - PHP
    Play : TFT - Jeux indé

  6. #6
    Expert éminent
    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.
    les règles du forum - mode d'emploi du forum
    Aucun navigateur ne propose d'extension boule-de-cristal : postez votre code et vos messages d'erreurs. (Rappel : "ça ne marche pas" n'est pas un message d'erreur)
    JE NE RÉPONDS PAS aux questions techniques par message privé.

  7. #7
    Membre confirmé
    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 ?
    Windev 23 - SQL SERVER - PHP
    Play : TFT - Jeux indé

  8. #8
    Expert éminent
    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)
    On ne jouit bien que de ce qu’on partage.

  9. #9
    Expert éminent
    Ouch, ça sent le gros projet de m*rde. Bon courage à toi !
    les règles du forum - mode d'emploi du forum
    Aucun navigateur ne propose d'extension boule-de-cristal : postez votre code et vos messages d'erreurs. (Rappel : "ça ne marche pas" n'est pas un message d'erreur)
    JE NE RÉPONDS PAS aux questions techniques par message privé.

  10. #10
    Membre confirmé
    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
    Windev 23 - SQL SERVER - PHP
    Play : TFT - Jeux indé

  11. #11
    Expert éminent
    Citation Envoyé par Nhaps Voir le message
    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.
    On ne jouit bien que de ce qu’on partage.

  12. #12
    Membre confirmé
    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
    Windev 23 - SQL SERVER - PHP
    Play : TFT - Jeux indé

  13. #13
    Expert éminent
    Citation Envoyé par Nhaps Voir le message
    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.
    les règles du forum - mode d'emploi du forum
    Aucun navigateur ne propose d'extension boule-de-cristal : postez votre code et vos messages d'erreurs. (Rappel : "ça ne marche pas" n'est pas un message d'erreur)
    JE NE RÉPONDS PAS aux questions techniques par message privé.