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

Langage SQL Discussion :

Différence entre deux dates avec exclusions


Sujet :

Langage SQL

  1. #1
    Membre régulier
    Différence entre deux dates avec exclusions
    Bonjour,

    Je travaille sur un outil de ticketing.

    J'ai une table avec une colonne DATE_DEMANDE_OPEN qui correspond à l'ouverture d'un ticket et une DATE_INTER_OPEN qui correspond à date/heure d'ouverture de la première intervention (première prise en charge du ticket).

    Je n'ai pas de problème pour avoir le délai de prise en charge entre la colonne DATE_DEMANDE_OPEN et DATE_INTER_OPEN.

    Seulement, le support qui prend en charge ces tickets n'est ouvert que du lundi au vendredi de 9h/12 et 14h/18h.

    Comment exclure les périodes ou le support n'est pas ouvert :
    - entre midi et deux
    - la nuit
    - le week-end

    Merci pour votre aide,

    Matt

  2. #2
    Expert éminent sénior
    Bonjour,

    La seule façon de connaître les périodes d'ouverture des services est d'avoir un calendrier par service avec les plages d'ouverture

  3. #3
    Membre régulier
    Bonjour,

    Merci pour ton retour.

    Je n'arrive pas à construire la requête pour savoir les périodes à prendre en compte entre DATE_DEMANDE_OPEN et DATE_INTER_OPEN.

    Imaginons une table avec les horaires d'ouverture sur la semaine lundi au vendredi de 9h00 à 18h00 (1 enregistrement par jour de la semaine).

    Un client ouvre une demande le dimanche à 12h00 avec prise en charge le lundi à 10h30.

    Donc DATE_DEMANDE_OPEN = '29/09/2019 - 12:00' et DATE_INTER_OPEN = '30/09/2019 - 11h30'.

    Je n'arrive pas à construire ma requête pour que le DATEDIFF entre ma DATE_DEMANDE_OPEN et DATE_INTER_OPEN pour que celle-ci me retourne 2h30 et non 23h30.

    Merci pour votre aide,

    Matt

  4. #4
    Expert éminent sénior
    Comme mentionné précédemment, il faut une autre table dans laquelle on a toutes les périodes enregistrées, il faudra ensuite cumuler le nombre d'heures ouvrées comprises entre la date/heure de demande et la date/heure d'intervention
    Vous ne pouvez pas utiliser datediff() qui n'est applicable que pour faire une différence entre deux dates, sans tenir compte des périodes fermées

  5. #5
    Membre régulier
    Bonjour,

    je comprend qu'il me faut une table avec les horaires d'ouverture dont je mentionne :

    "Imaginons une table avec les horaires d'ouverture sur la semaine lundi au vendredi de 9h00 à 18h00 (1 enregistrement par jour de la semaine)."

    Mais je n'arrive pas à construed la requête pour savoir que dans tel plage entre DATE_DEMANDE_OPEN et DATE_INTER_OPEN ça correspond à tel ou tel jour de ma ligne

    Matt

  6. #6
    Expert éminent sénior
    La réponse dépend du SGBD, les fonctions dates étant sensiblement différentes de l'un à l'autre.

    voici un exemple avec DB2 :


    Création du jeu d'essais
    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
    with Cal_Per_Ouv (DT1, DT2) as
        (select '2019-09-30 08:00:00', '2019-09-30 12:30:00'   from sysibm.sysdummy1
         union all
         select '2019-09-30 13:45:00', '2019-09-30 19:00:00'   from sysibm.sysdummy1
         union all
         select '2019-10-01 08:00:00', '2019-10-01 18:00:00'   from sysibm.sysdummy1
         union all
         select '2019-10-02 08:00:00', '2019-10-02 18:00:00'   from sysibm.sysdummy1
         union all
         select '2019-10-03 08:00:00', '2019-10-03 18:00:00'   from sysibm.sysdummy1
         union all
         select '2019-10-04 08:00:00', '2019-10-04 18:00:00'   from sysibm.sysdummy1
         union all
         select '2019-10-05 08:00:00', '2019-10-05 13:00:00'   from sysibm.sysdummy1
        )


    Requête de calcul du nombre d'heures ouvrées entre 2 dates/heures
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    select sum(timestampdiff(8, char(timestamp(dt2) - timestamp(DT1)))  )
    from cal_per_ouv
    where dt1 >= '2019-10-02 00:00:00'
      and dt2 <= '2019-10-05 23:59:59'
    ;

    Résultat (en heures, le paramètre 8 de la fonction timestampdiff retourne un résultat en heures) :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    1            
    -------------
    35


    Ce résultat est conforme : il y a bien 35 heures ouvrées d'après le calendrier entre le 2 octobre à 0h00 et le 5 octobre à minuit
    La difficulté c'est le cas où la période commence ou bien s'arrête à une heure différente de l'heure début ou de fin des plages d'ouverture.
    Par exemple si je demande entre le 2 octobre à10h30 et le 5 octobre à 10h00.
    À vous de voir quelle règle de gestion appliquer dans ce cas.

  7. #7
    Membre régulier
    Ok.

    Merci pour cet exemple très clair.

    Bonne journée,

    Matt

###raw>template_hook.ano_emploi###