Faire une vue d'historique sur deux tables

Version imprimable

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" :cfou:, j'ai essayé plusieurs pistes mais rien ne fonctionne pour l'instant suite aux recherches sur ce forum je suis parti sur :
Code:

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... :ave:

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:

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:

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

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:

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:

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:

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:

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:

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:

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.

28/11/2014, 11h06
nonoxxxx

Citation:

Envoyé par fsmrel

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

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 !!! :ave:
28/11/2014, 11h09
nonoxxxx

Et merci beaucoup punkoff j'ai découvert les CTE du coup ... 8O je ne connaissais pas... :oops: (et fmsrel pour l'explication ;) )
;)

Citation:

Envoyé par punkoff

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 ...

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:

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 !!

28/11/2014, 17h29
fsmrel

Hello !

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

Bonne route !

Bonjour,

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

Code:

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...

01/12/2014, 13h23
fsmrel

Bonjour,

Citation:

Envoyé par aieeeuuuuu

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 ?

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:

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:

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...

Bonsoir,

Citation:

Envoyé par aieeeuuuuu

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

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:

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:

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

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...