IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
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 :

Faire une vue d'historique sur deux tables


Sujet :

Langage SQL

  1. #1
    Candidat au Club
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Novembre 2014
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2014
    Messages : 4
    Points : 4
    Points
    4
    Par défaut Faire une vue d'historique sur deux tables
    Bonjour,


    Pour faire simple : je cherche à construire une vue sur 2 tables : CONTRAT (matricule, num_contrat, date_début, date_fin) et ABSENCES(matricule, num_contrat, date_debut, date_fin)

    le résultat souhaitée est (pour un contrat du 1/1/2014 au 31/12/2014 (1 seul enregistrement dans la Table CONTRAT) avec 2 absences aux dates indiquées (2 enregistrement dans la Table ABSENCES) ) :

    1/1/14 – 3/2/14 Présent
    4/2/14 - 8/2/14 Absent
    9/2/14 – 14/3/14 Présent
    15/3/14 – 25/3/14 Absent
    18/5/14 – 31/12/14 Présent

    dans mes "réflexions" , j'ai essayé plusieurs pistes mais rien ne fonctionne pour l'instant suite aux recherches sur ce forum je suis parti sur :

    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
     
    SELECT
    CASE
    		WHEN abs.DT_START > c.DT_START THEN abs.DT_START
    		WHEN abs.DT_START < c.DT_START THEN c.DT_START
    		ELSE abs.DT_START
    END
    AS debut
    ,
    CASE
    		WHEN abs.DT_END > c.DT_END THEN c.DT_END
    		WHEN abs.DT_END < c.DT_END THEN abs.DT_END
    		ELSE c.DT_END
    END
    AS fin
    ,
    CASE
    		WHEN abs.CODE_ABS  IS NULL THEN 'present'
    		WHEN abs.CODE_ABS  IS NOT NULL THEN 'absent'
    		ELSE abs.CODE_ABS
    END
    AS absence
    FROM  CONTRAT c inner join ABSENCE abs on (c.matricule = abs.matricule and c.num_contrat = abs.num_contrat )
    ORDER BY  1 ;
    Mais là je ne récupère que les lignes d'absences, je dois oublier qqchose dans les conditions pour gérer les périodes sans absences...
    si qqun a une idée...

  2. #2
    Expert confirmé
    Homme Profil pro
    Inscrit en
    Mai 2002
    Messages
    3 173
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 41
    Localisation : France, Rhône (Rhône Alpes)

    Informations forums :
    Inscription : Mai 2002
    Messages : 3 173
    Points : 5 345
    Points
    5 345
    Par défaut
    bonjour,

    Faisable avec un sgbd qui supporte les fonctions de fenetrages : http://sqlpro.developpez.com/article...clause-window/

    L'exemple est fait avec Oracle, mais est adaptable.

    Il vous faut dans un premier lieu construire une table (ou cte / vue / ..) qui recense toutes les dates entre le 1er jour du contrat et le dernier jour.

    Ensuite on applique le filtre de la fonction de fenêtrage et on peut ressortir des bornes ...



    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 calendar as (
    SELECT to_date('01012014', 'ddmmyyyy') + LEVEL - 1 dte_ref
     FROM   dual
     CONNECT BY (to_date('01012014', 'ddmmyyyy') + LEVEL) <= to_date('31122014', 'ddmmyyyy')),
     abscence as(
    select 0 as matricule, 1 as contrat, to_date('15032014', 'ddmmyyyy') as dte_debut, to_date('25032014', 'ddmmyyyy') as dte_fin from dual union all
    select 0, 1, to_date('04022014', 'ddmmyyyy') , to_date('08022014', 'ddmmyyyy') from dual),
    ref_calendar as (select dte_ref, dte_debut, dte_fin, row_number() over(order by dte_ref) - row_number() over(order by dte_debut, dte_ref) as rnk_ref
    from calendar
    left outer join abscence on dte_ref between dte_debut and dte_fin)
    select min(dte_ref) as borne_inf, max(dte_ref) as borne_max, case when max(dte_debut) is null then 'Present' else 'Abscent' end
    from ref_calendar
    group by rnk_ref
    order by 1

    La CTE calendar permet de créer la liste de date (il faudra partir des données de la table contrat pour ce faire).
    La CTE abscence est juste une émulation de votre table abscence.
    La CTE ref_calendar permet de joindre ces deux tables et prépare le ranking des lignes pour le group by qui suit ...


    Au final on obtient :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
    Borne_min    Borne_max    Libellé
    -----------------------------------
    1/1/2014	2/3/2014	Present
    2/4/2014	2/8/2014	Abscent
    2/9/2014	3/14/2014	Present
    3/15/2014	3/25/2014	Abscent
    3/26/2014	12/30/2014	Present

  3. #3
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 966
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 966
    Points : 30 778
    Points
    30 778
    Billets dans le blog
    16
    Par défaut
    Bonsoir,


    A ce jour, tous les SGBD ne proposent pas les CTE (Common Table Expressions), hélas !

    On peut pallier en utilisant une méthode bourrin, consistant à procéder à l’union des présences entre deux absences, des présences en début de période d référence, des présences en fin de période de référence, des présences sans aucune absence, et enfin des absences.


    Avec MySQL qui ne propose pas les CTE, en détaillant :

    Retrouver les présences entre deux absences

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    SELECT matricule, num_contrat, d1, MIN(d2) AS d2, 'présent'
    FROM 
        (SELECT abs1.matricule, abs1.num_contrat, DATE_ADD(abs1.DT_END, INTERVAL 1 DAY) AS d1,  DATE_SUB(abs2.DT_start , INTERVAL 1 DAY) AS d2
         FROM   CONTRAT c JOIN ABSENCE abs1 ON c.matricule = abs1.matricule AND c.num_contrat = abs1.num_contrat
                          JOIN ABSENCE abs2 ON c.matricule = abs2.matricule AND c.num_contrat = abs2.num_contrat
         WHERE  abs1.DT_END < abs2.dt_start) AS t
    GROUP BY matricule, num_contrat, d1


    Présences en début de période

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    SELECT matricule, num_contrat, d1, d2, 'présent !'
    FROM
        (SELECT c.matricule, c.num_contrat, c.DT_START AS d1, DATE_SUB(MIN(abs1.DT_start), INTERVAL 1 DAY) AS d2
         FROM   CONTRAT c JOIN ABSENCE abs1 ON c.matricule = abs1.matricule AND c.num_contrat = abs1.num_contrat
                          JOIN ABSENCE abs2 ON c.matricule = abs2.matricule AND c.num_contrat = abs2.num_contrat
         WHERE  abs1.DT_START > c.DT_START 
           AND NOT EXISTS (SELECT '' 
                           FROM   ABSENCE AS abs3 
                           WHERE  c.matricule = abs3.matricule AND c.num_contrat = abs3.num_contrat
                             AND  c.DT_START = abs3.DT_START)
        ) AS t
     WHERE matricule IS NOT NULL

    Présences en fin de période

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT c.matricule, c.num_contrat, DATE_ADD(MAX(abs1.DT_end), INTERVAL 1 DAY) AS d1, c.DT_END AS d2, 'présent'
    FROM  CONTRAT c JOIN ABSENCE abs1 ON c.matricule = abs1.matricule AND c.num_contrat = abs1.num_contrat
                    JOIN ABSENCE abs2 ON c.matricule = abs2.matricule AND c.num_contrat = abs2.num_contrat
    WHERE  abs1.DT_END < c.DT_END


    Aucune absence pendant la période de référence

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    SELECT matricule, num_contrat, DT_START AS d1, DT_END AS d2, 'présent tout le temps !'
    FROM   CONTRAT c WHERE NOT EXISTS (SELECT '' FROM  ABSENCE abs WHERE c.matricule = abs.matricule AND c.num_contrat = abs.num_contrat)
    Absences

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    SELECT matricule, num_contrat, DT_START, DT_END, CODE_ABS
     FROM ABSENCE

    On met tout ça dans la marmite, on touille et on trie :


    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
    SELECT matricule, num_contrat, d1, MIN(d2) AS d2, 'présent'
    FROM 
        (SELECT abs1.matricule, abs1.num_contrat, DATE_ADD(abs1.DT_END, INTERVAL 1 DAY) AS d1,  DATE_SUB(abs2.DT_start , INTERVAL 1 DAY) AS d2
         FROM   CONTRAT c JOIN ABSENCE abs1 ON c.matricule = abs1.matricule AND c.num_contrat = abs1.num_contrat
                          JOIN ABSENCE abs2 ON c.matricule = abs2.matricule AND c.num_contrat = abs2.num_contrat
         WHERE  abs1.DT_END < abs2.dt_start) AS t
    GROUP BY matricule, num_contrat, d1  
     
    UNION ALL
     
    SELECT matricule, num_contrat, d1, d2, 'présent !'
    FROM
        (SELECT c.matricule, c.num_contrat, c.DT_START AS d1, DATE_SUB(MIN(abs1.DT_start), INTERVAL 1 DAY) AS d2
         FROM   CONTRAT c JOIN ABSENCE abs1 ON c.matricule = abs1.matricule AND c.num_contrat = abs1.num_contrat
                          JOIN ABSENCE abs2 ON c.matricule = abs2.matricule AND c.num_contrat = abs2.num_contrat
         WHERE  abs1.DT_START > c.DT_START 
           AND NOT EXISTS (SELECT '' 
                           FROM   ABSENCE AS abs3 
                           WHERE  c.matricule = abs3.matricule AND c.num_contrat = abs3.num_contrat
                             AND  c.DT_START = abs3.DT_START)
        ) AS t
     WHERE matricule IS NOT NULL
     
    UNION ALL
     
    SELECT c.matricule, c.num_contrat, DATE_ADD(MAX(abs1.DT_end), INTERVAL 1 DAY) AS d1, c.DT_END AS d2, 'présent'
    FROM  CONTRAT c JOIN ABSENCE abs1 ON c.matricule = abs1.matricule AND c.num_contrat = abs1.num_contrat
                    JOIN ABSENCE abs2 ON c.matricule = abs2.matricule AND c.num_contrat = abs2.num_contrat
    WHERE  abs1.DT_END < c.DT_END
     
    UNION ALL
     
    SELECT matricule, num_contrat, DT_START AS d1, DT_END AS d2, 'présent tout le temps !'
    FROM   CONTRAT c WHERE NOT EXISTS (SELECT '' FROM  ABSENCE abs WHERE c.matricule = abs.matricule AND c.num_contrat = abs.num_contrat)
     
    UNION ALL
     
    SELECT matricule, num_contrat, DT_START, DT_END, CODE_ABS
     FROM ABSENCE
     
    ORDER BY matricule, num_contrat, d1 ;


    Bien sûr, il y a plus élégant, plus concis, mais normalement ça fonctionne.
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  4. #4
    Candidat au Club
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Novembre 2014
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2014
    Messages : 4
    Points : 4
    Points
    4
    Par défaut
    Citation Envoyé par fsmrel Voir le message
    A ce jour, tous les SGBD ne proposent pas les CTE (Common Table Expressions), hélas !
    Hélas oui!! surtout que je dois créer une vue compatible Oracle 11g et sqlserver 2005 et 2008...
    Donc autant dire le plus simple SQL possible serait apprécié

    Citation Envoyé par fsmrel Voir le message
    Bien sûr, il y a plus élégant, plus concis, mais normalement ça fonctionne.
    Y a toujours... mais c'est déjà énorme, je teste ça et je te dis, MERCI BEAUCOUP !!!

  5. #5
    Candidat au Club
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Novembre 2014
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2014
    Messages : 4
    Points : 4
    Points
    4
    Par défaut
    Et merci beaucoup punkoff j'ai découvert les CTE du coup ... je ne connaissais pas... (et fmsrel pour l'explication )

    Citation Envoyé par punkoff Voir le message
    La CTE calendar permet de créer la liste de date (il faudra partir des données de la table contrat pour ce faire).
    La CTE abscence est juste une émulation de votre table abscence.
    La CTE ref_calendar permet de joindre ces deux tables et prépare le ranking des lignes pour le group by qui suit ...

  6. #6
    Candidat au Club
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Novembre 2014
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2014
    Messages : 4
    Points : 4
    Points
    4
    Par défaut
    pour info à partir de ta requête finale j'ai réussi à faire ce que je voulais... Merci infiniment

    pour info ma requête finale qui fonctionne très bien :

    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
    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
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
     
    SELECT orga, matricule, num_contrat, deb_abs, fin_abs, code_abs
    FROM (
        SELECT orga
               , matricule 
               , num_contrat 
               , deb_abs 
               , Min(fin_abs) "fin_abs" 
               , 'présent' "code_abs"
        FROM   (SELECT ABS1.orga
                       , ABS1.matricule 
                       , ABS1.num_contrat 
                       , ABS1.fin_abs + 1   "deb_abs" 
                       , ABS2.deb_abs - 1 "fin_abs" 
                FROM   CONTRAT C 
                       JOIN ABSENCE ABS1 
                         ON ( C.matricule = ABS1.matricule 
                              AND C.num_contrat = ABS1.num_contrat
                              AND C.orga=ABS1.orga)
                       JOIN ABSENCE ABS2 
                         ON ( C.matricule = ABS2.matricule 
                              AND C.num_contrat = ABS2.num_contrat
                              AND C.orga=ABS2.orga)
                WHERE  ABS1.fin_abs < ABS2.deb_abs 
                   AND ABS1.orga = ABS2.orga 
                   AND ABS1.orga = '0001') T 
    --    WHERE  matricule = 'R0550934' 
        GROUP  BY orga
                  , matricule 
                  , num_contrat 
                  , deb_abs 
        UNION ALL 
        SELECT orga
               , matricule 
               , num_contrat 
               , deb_abs 
               , fin_abs 
               , 'présent !' 
        FROM   (SELECT C.orga
                       , C.matricule 
                       , C.num_contrat 
                       , C.deb_contrat             "deb_abs" 
                       , Min(ABS1.deb_abs) - 1 "fin_abs" 
                FROM   CONTRAT C 
                       JOIN ABSENCE ABS1 
                         ON (C.matricule = ABS1.matricule 
                            AND C.num_contrat = ABS1.num_contrat 
                            AND C.orga=ABS1.orga)
                       JOIN ABSENCE ABS2 
                         ON (C.matricule = ABS2.matricule 
                            AND C.num_contrat = ABS2.num_contrat 
                            AND C.orga=ABS2.orga)
                WHERE  ABS1.deb_abs > C.deb_contrat 
                   AND NOT EXISTS (SELECT '' 
                                   FROM   ABSENCE ABS3 
                                   WHERE  C.matricule = ABS3.matricule 
                                      AND C.num_contrat = ABS3.num_contrat 
                                      AND C.deb_contrat = ABS3.deb_abs
                                      AND C.orga=ABS3.orga)
                   AND  C.orga='0001'
                GROUP  BY C.orga
                          , C.matricule 
                          , C.num_contrat 
                          , C.deb_contrat) T 
    --    WHERE  matricule = 'R0550934' 
        UNION ALL 
        SELECT C.orga
               , C.matricule 
               , C.num_contrat 
               , Max(ABS1.fin_abs) + 1 "deb_abs" 
               , C.fin_contrat             "fin_abs" 
               , 'présent' 
        FROM   CONTRAT C 
               JOIN ABSENCE ABS1 
                 ON (C.matricule = ABS1.matricule 
                    AND C.num_contrat = ABS1.num_contrat
                    AND C.orga=ABS1.orga)
               JOIN ABSENCE ABS2 
                 ON (C.matricule = ABS2.matricule 
                    AND C.num_contrat = ABS2.num_contrat 
                    AND C.orga = ABS2.orga)
        WHERE  ABS1.fin_abs < C.fin_contrat 
    --       AND C.matricule = 'R0550934' 
            AND C.orga = '0001'
        GROUP  BY C.orga
                  , C.matricule 
                  , C.num_contrat 
                  , C.fin_contrat 
        UNION ALL 
        SELECT C.orga
               , C.matricule 
               , C.num_contrat 
               , C.deb_contrat 
               , C.fin_contrat 
               , 'présent tout le temps !' 
        FROM   CONTRAT C 
        WHERE  NOT EXISTS (SELECT '' 
                           FROM   ABSENCE ABS 
                           WHERE  C.matricule = ABS.matricule 
                              AND C.num_contrat = ABS.num_contrat
                              AND C.orga=ABS.orga) 
    --       AND C.matricule = 'R0550934' 
            AND C.orga='0001'
        UNION ALL 
        SELECT ABS.orga
               , ABS.matricule 
               , ABS.num_contrat 
               , ABS.deb_abs 
               , ABS.fin_abs 
               , ABS.code_abs 
        FROM   ABSENCE ABS 
        WHERE ABS.orga='0001'
    --        AND ABS.matricule = 'R0550934'
        ) PT
    WHERE PT.deb_abs <= PT.fin_abs
    ORDER BY 2, 3, 4
    big up !!

  7. #7
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 966
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 966
    Points : 30 778
    Points
    30 778
    Billets dans le blog
    16
    Par défaut
    Hello !

    Attention, si des psys tombent sur nos requêtes, on pourrait finir à l'asile

    Bonne route !
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  8. #8
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    Bonjour,

    Une petite variante avec un peu moins de sous requêtes (façon SQL Server pour la manipulation des dates...)

    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
     
     
    SELECT num_contrat, date_debut, date_fin, 'absent'
    FROM absences
    UNION ALL
    SELECT A.num_contrat,  DATEADD(DAY, 1, A.date_fin) , DATEADD(DAY, -1,MIN(B.date_debut)), 'present'
    FROM	Absences A 
    INNER JOIN absences B
    	ON B.num_contrat = A.num_contrat
    	AND		B.date_debut > A.date_debut
    GROUP BY A.num_contrat, A.date_fin
    UNION ALL 
    SELECT C.num_contrat, C.date_debut, DATEADD(DAY ,-1,MIN(A.date_debut)), 'present' 
    FROM Contrat C
    INNER JOIN Absences A
    	ON A.num_contrat = C.num_contrat
    GROUP BY C.num_contrat, C.date_debut
    HAVING C.date_debut < MIN(A.date_debut)
    UNION ALL
    SELECT C.num_contrat, COALESCE(DATEADD(DAY, 1, MAX(A.date_fin)), C.date_debut), C.date_fin, 'present' 
    FROM Contrat C
    LEFT JOIN Absences A
    	ON A.num_contrat = C.num_contrat
    GROUP BY C.num_contrat, C.date_fin, C.date_debut
    HAVING MAX(A.date_fin) < C.date_fin OR NOT EXISTS(SELECT 1 FROM Absences X WHERE X.num_contrat = C.num_contrat)
    ORDER BY num_contrat, date_debut


    PS : je pense qu'il y aura un petit bug avec la requête de fsmrel si une absence se termine en même temps que le contrat... mais c'est un cas très particulier...

  9. #9
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 966
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 966
    Points : 30 778
    Points
    30 778
    Billets dans le blog
    16
    Par défaut
    Bonjour,

    Citation Envoyé par aieeeuuuuu Voir le message
    PS : je pense qu'il y aura un petit bug avec la requête de fsmrel si une absence se termine en même temps que le contrat...
    J'ai testé avec MySQL, et n'ai rien constaté d'anormal. Auriez-vous un exemple à proposer ?
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  10. #10
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    Bonjour fsmrel,


    En fait, s'il y a une absence qui démarre en même temps que le contrat, et une autre absence qui se termine en même temps que le contrat, la requête fait ressortir une présence entre la fin de la dernière absence, et la fin du contrat, en plus de l'absence en fin de contrat.

    Je dois reconnaître que le cas est quelque peu particulier...

    Ci-dessous un jeu d'essai.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
    insert into contrat values ('A', 1, '20140101', '20141231');
     
    insert into absence values
     ('A', 1, '20141201', '20141231')
    ,('A', 1, '20140101', '20140208')
    ;
    Cette ligne supplémentaire sera produite par l'antépénultième requete d'UNION :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
     
    SELECT c.matricule, c.num_contrat, DATE_ADD(MAX(abs1.DT_end), INTERVAL 1 DAY) AS d1, c.DT_END AS d2, 'présent'
    FROM  CONTRAT c JOIN ABSENCE abs1 ON c.matricule = abs1.matricule AND c.num_contrat = abs1.num_contrat
                    JOIN ABSENCE abs2 ON c.matricule = abs2.matricule AND c.num_contrat = abs2.num_contrat
    WHERE  abs1.DT_END < c.DT_END

    Par ailleurs, si aucune absence n'est enregistrée, cette même requete fera apparaitre une ligne supplémentaire dans le résultat, avec toutes les dates à NULL
    Sous MySQL seulement car cela est uniquement dû à son interprétation particulière du GROUP BY (ou de son absence) !

    D'autres SGBDR réclamerons d'ailleurs la présence d'une clause GROUP BY dans cette requête ainsi que dans la précédente...

  11. #11
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 966
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 966
    Points : 30 778
    Points
    30 778
    Billets dans le blog
    16
    Par défaut
    Bonsoir,



    Citation Envoyé par aieeeuuuuu Voir le message
    D'autres SGBDR réclamerons d'ailleurs la présence d'une clause GROUP BY dans cette requête ainsi que dans la précédente...
    C’est exact, j’ai utilisé MySQL, lequel est moins regardant que les autres et incite à la facilité...

    Ajoutons donc les GROUP BY c.matricule, c.num_contrat là où il y a une opération d’agrégation (MIN, MAX en l’occurrence).



    Citation Envoyé par aieeeuuuuu Voir le message
    S'il y a une absence qui démarre en même temps que le contrat, et une autre absence qui se termine en même temps que le contrat, la requête fait ressortir une présence entre la fin de la dernière absence, et la fin du contrat, en plus de l'absence en fin de contrat.
    D’accord, Œil-de-Lynx ! Si avec MySQL tout se passe bien, dès qu’on exécute la requête avec un SGBD moins laxiste, celui-ci exige que les GROUP BY soient présents. La requête en cause doit alors être enrichie d’un HAVING, sous peine d’avoir droit à l’erreur que vous mettez en évidence.

    Requête qui donne un résultat correct avec MySQL (laxiste) :
    Code MySQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT c.matricule, c.num_contrat, DATE_ADD(MAX(abs1.DT_end), INTERVAL 1 DAY) AS d1, c.DT_END AS d2, 'présent'
    FROM  CONTRAT c JOIN ABSENCE abs1 ON c.matricule = abs1.matricule AND c.num_contrat = abs1.num_contrat
                    JOIN ABSENCE abs2 ON c.matricule = abs2.matricule AND c.num_contrat = abs2.num_contrat
    WHERE  abs1.DT_END < c.DT_END

    Requête avec PostgreSQL (exigeant) :
    Code PostgreSQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    SELECT c.matricule, c.num_contrat, MAX(abs1.DT_end) + 1 AS d1, c.DT_END AS d2, 'présent'
    FROM  CONTRAT c JOIN ABSENCE abs1 ON c.matricule = abs1.matricule AND c.num_contrat = abs1.num_contrat
                    JOIN ABSENCE abs2 ON c.matricule = abs2.matricule AND c.num_contrat = abs2.num_contrat
    WHERE  abs1.DT_END < c.DT_END
    GROUP BY c.matricule, c.num_contrat
    HAVING MAX(abs2.DT_END) < c.DT_END



    Citation Envoyé par aieeeuuuuu Voir le message
    Par ailleurs, si aucune absence n'est enregistrée, cette même requete fera apparaitre une ligne supplémentaire dans le résultat, avec toutes les dates à NULL
    Sous MySQL seulement car cela est uniquement dû à son interprétation particulière du GROUP BY (ou de son absence) !
    Là, par contre, je n’ai rien constaté de tel, sans GROUP BY ou avec (GROUP BY c.matricule, c.num_contrat).


    En tout cas, les développeurs qui consultent DVP ont de la chance d’avoir affaire à des gens aussi compétents qu’aieeeuuuuu et punkoff en SQL...
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 4
    Dernier message: 20/04/2015, 12h15
  2. [MySQL-5.6] Faire une requête sur deux tables pour avoir la valeur MAX sans le GROUP BY
    Par emykev22 dans le forum Requêtes
    Réponses: 1
    Dernier message: 04/06/2014, 13h12
  3. une non equi-jointure sur deux tables
    Par AliJava dans le forum Langage SQL
    Réponses: 6
    Dernier message: 14/06/2008, 16h26
  4. [Access] Fonction TOP dans une requête sur deux tables
    Par pc75 dans le forum Langage SQL
    Réponses: 4
    Dernier message: 04/07/2007, 10h31
  5. Réponses: 3
    Dernier message: 09/05/2007, 12h43

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo