Requête select

Version imprimable

Requête select

Bonjour,

J'ai une table de la forme :

Code:

ID_EVE,ID_GROUP,ID_ACT_INI,ID_ACT_DST,DATE_DEBUT_EV,DATE_FIN_EV

J'ai une seconde table de la forme

Code:

ID_EVE DATE_DEBUT_PAUSE DATE_FIN_PAUSE

et on a si pause il y eu durant la ligne d'évènement.

Code:

DATE_DEBUT_EV < DATE_DEBUT_PAUSE < DATE_FIN_PAUSE < DATE_FIN_EV

plusieurs pauses sont possibles dans ce cas on a :
Code:

1 2 DATE_DEBUT_EV < DATE_DEBUT_PAUSE_1 < DATE_FIN_PAUSE_1 < DATE_DEBUT_PAUSE_2 < DATE_FIN_PAUSE_2 < .... < DATE_FIN_EV
J'aimerais obtenir le résultat suivant :
Code:

1 2 3 4 5 ID_EVE,ID_GROUP,ID_ACT_INI,ID_ACT_DST,DATE_DEBUT_EV,DATE_DEBUT_PAUSE_1 ID_EVE,ID_GROUP,ID_ACT_INI,ID_ACT_DST,DATE_FIN_PAUSE_1,DATE_DEBUT_PAUSE_2 ... ID_EVE,ID_GROUP,ID_ACT_INI,ID_ACT_DST,DATE_FIN_PAUSE_(N-1),DATE_DEBUT_PAUSE_(N) ID_EVE,ID_GROUP,ID_ACT_INI,ID_ACT_DST,DATE_FIN_PAUSE_N,DATE_FIN_EV
Merci pour votre aide

23/09/2011, 14h25
Waldar

Qu'avez-vous essayé comme requête ?
23/09/2011, 16h12
sfoggy

Je traite actuellement par programme java et je souhaite tout faire dans une procédure stockée plus optimisée.

Je n'ai aucune idée sur la manière de procéder et le premier point est de savoir s'il est possible de le faire.

Cordialement

Une solution, j'ai supprimé du jeu de test les colonnes non essentielles :

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
With Tab1 as
(
select 1 as id_eve, trunc(sysdate) +  8/24 as dt_ev_deb, trunc(sysdate) + 18/24 as dt_ev_fin from dual union all
select 2          , trunc(sysdate) + 32/24             , trunc(sysdate) + 42/24              from dual
)
  ,  Tab2 as
(
select 1 as id_eve, trunc(sysdate) +  10.5/24 as dt_ps_deb, trunc(sysdate) +  10.75/24 as dt_ps_fin from dual union all
select 1          , trunc(sysdate) +  13  /24             , trunc(sysdate) +  14   /24              from dual union all
select 1          , trunc(sysdate) +  16  /24             , trunc(sysdate) +  16.5 /24              from dual union all
select 2          , trunc(sysdate) +  36  /24             , trunc(sysdate) +  37   /24              from dual
)
   , Tab as
(
select id_eve, dt_ev_deb as dt_deb, dt_ev_fin as dt_fin from Tab1
 union all
select id_eve, dt_ps_fin          , dt_ps_deb           from Tab2
)
  select id_eve
       , dt_deb
       , coalesce(lead(dt_fin) over(partition by id_eve order by dt_deb asc),
           first_value(dt_fin) over(partition by id_eve order by dt_deb asc)) as dt_fin
    from Tab
order by id_eve asc
       , dt_deb asc;
 
    ID_EVE DT_DEB              DT_FIN             
---------- ------------------- -------------------
         1 23/09/2011 08:00:00 23/09/2011 10:30:00
         1 23/09/2011 10:45:00 23/09/2011 13:00:00
         1 23/09/2011 14:00:00 23/09/2011 16:00:00
         1 23/09/2011 16:30:00 23/09/2011 18:00:00
         2 24/09/2011 08:00:00 24/09/2011 12:00:00
         2 24/09/2011 13:00:00 24/09/2011 18:00:00

En fait tu veux ressortir toutes les périodes d'activités ?

En PL :

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
 
DROP TABLE TEST_T1;
DROP TABLE TEST_T2;
 
CREATE TABLE TEST_T1
AS
SELECT 1 AS ID_EVE,TO_DATE('10/09/2011','DD/MM/YYYY') AS DATE_DEBUT_EV, TO_DATE('23/09/2011','DD/MM/YYYY') AS DATE_FIN_EV FROM DUAL UNION ALL
SELECT 2 AS ID_EVE,TO_DATE('01/07/2011','DD/MM/YYYY') AS DATE_DEBUT_EV, TO_DATE('30/07/2011','DD/MM/YYYY') AS DATE_FIN_EV FROM DUAL
;
 
CREATE TABLE TEST_T2
AS
SELECT 1 AS ID_EVE, TO_DATE('15/09/2011','DD/MM/YYYY') AS DATE_DEBUT_PAUSE, TO_DATE('16/09/2011','DD/MM/YYYY') AS DATE_FIN_PAUSE FROM DUAL UNION ALL
SELECT 1 AS ID_EVE, TO_DATE('18/09/2011','DD/MM/YYYY') AS DATE_DEBUT_PAUSE, TO_DATE('20/09/2011','DD/MM/YYYY') AS DATE_FIN_PAUSE FROM DUAL UNION ALL
SELECT 1 AS ID_EVE, TO_DATE('21/09/2011','DD/MM/YYYY') AS DATE_DEBUT_PAUSE, TO_DATE('22/09/2011','DD/MM/YYYY') AS DATE_FIN_PAUSE FROM DUAL UNION ALL
SELECT 2 AS ID_EVE, TO_DATE('15/07/2011','DD/MM/YYYY') AS DATE_DEBUT_PAUSE, TO_DATE('18/09/2011','DD/MM/YYYY') AS DATE_FIN_PAUSE FROM DUAL UNION ALL
SELECT 2 AS ID_EVE, TO_DATE('20/07/2011','DD/MM/YYYY') AS DATE_DEBUT_PAUSE, TO_DATE('23/09/2011','DD/MM/YYYY') AS DATE_FIN_PAUSE FROM DUAL UNION ALL
SELECT 2 AS ID_EVE, TO_DATE('25/07/2011','DD/MM/YYYY') AS DATE_DEBUT_PAUSE, TO_DATE('27/09/2011','DD/MM/YYYY') AS DATE_FIN_PAUSE FROM DUAL 
;
 
DECLARE
 
CURSOR c_TEST_T1 IS
SELECT *
FROM TEST_T1;
 
CURSOR c_TEST_T2(l_id_eve NUMBER) IS
SELECT *
FROM TEST_T2
WHERE TEST_T2.id_eve = l_id_eve
ORDER BY DATE_DEBUT_PAUSE 
;
 
TYPE r_periodes IS RECORD ( ID_EVE TEST_T1.ID_EVE%TYPE
                          , DT_DEB_ACTIVITE DATE
                          , DT_FIN_ACTIVITE DATE
                          );
 
TYPE t_periodes IS TABLE OF r_periodes INDEX BY BINARY_INTEGER;
 
l_periodes t_periodes;
 
BEGIN
 
l_periodes.DELETE;
 
FOR  l_TEST_T1 IN c_TEST_T1 
LOOP
    l_periodes(l_periodes.COUNT+1).id_eve        := l_TEST_T1.id_eve;
    l_periodes(l_periodes.COUNT).DT_DEB_ACTIVITE := l_TEST_T1.DATE_DEBUT_EV;
 
    FOR  l_TEST_T2 IN c_TEST_T2(l_TEST_T1.id_eve)
    LOOP
        l_periodes(l_periodes.COUNT).DT_FIN_ACTIVITE  := l_TEST_T2.DATE_DEBUT_PAUSE;
        l_periodes(l_periodes.COUNT+1).id_eve        := l_TEST_T1.id_eve;   
        l_periodes(l_periodes.COUNT).DT_DEB_ACTIVITE := l_TEST_T2.DATE_FIN_PAUSE;              
    END LOOP;
    l_periodes(l_periodes.COUNT).DT_FIN_ACTIVITE := l_TEST_T1.DATE_FIN_EV;
END LOOP;
 
FOR i in l_periodes.FIRST .. l_periodes.LAST
LOOP
     DBMS_OUTPUT.PUT_LINE (l_periodes(i).id_eve 
                              || '-' 
                              || l_periodes(i).DT_DEB_ACTIVITE 
                              || '-' 
                              ||l_periodes(i).DT_FIN_ACTIVITE );
END LOOP;
 
END;
/
 
1-10/09/11-15/09/11
1-16/09/11-18/09/11
1-20/09/11-21/09/11
1-22/09/11-23/09/11
2-01/07/11-15/07/11
2-18/09/11-20/07/11
2-23/09/11-25/07/11
2-27/09/11-30/07/11

Après, Quid des periodes qui se chevauchent ...

Bonjour,

c'est en effet le but recherché ORA-007. Je teste avec l'ensemble des données nécessaires, car je dois calculer le temps d'activité par évènement et par action. Je regarde comment adapter votre solution.

Pour Waldar, la requête ne donne pas le résultat attendu :

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
 
WITH 
  Tab1 AS
  (
  SELECT 71416 AS id_eve, 1 AS action, TO_DATE('19/09/2011 17:29:33','DD/MM/YYYY HH24:mi:ss') AS dt_ev_deb, TO_DATE('20/09/2011 17:29:36','DD/MM/YYYY HH24:mi:ss') AS dt_ev_fin FROM dual UNION ALL
  SELECT 71416 AS id_eve, 2          , TO_DATE('20/09/2011 17:29:36','DD/MM/YYYY HH24:mi:ss') AS dt_ev_deb, TO_DATE('21/09/2011 13:03:09','DD/MM/YYYY HH24:mi:ss') AS dt_ev_fin FROM dual
  ),
  Tab2 AS
  (
    SELECT 71416 AS id_eve, TO_DATE('19/09/2011 17:29:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_deb, TO_DATE('20/09/2011 13:02:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_fin FROM dual UNION ALL
    SELECT 71416 AS id_eve, TO_DATE('20/09/2011 17:45:00','DD/MM/YYYY HH24:mi:ss') AS dt_ps_deb, TO_DATE('21/09/2011 12:02:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_fin FROM dual
  ),
  Tab AS
  (
  SELECT id_eve, dt_ev_deb AS dt_deb, dt_ev_fin AS dt_fin FROM Tab1
  union ALL
  SELECT id_eve, dt_ps_fin          , dt_ps_deb           FROM Tab2
  )
  SELECT id_eve
       , dt_deb
       , coalesce(lead(dt_fin) over(partition BY id_eve ORDER BY dt_deb ASC),
           first_value(dt_fin) over(partition BY id_eve ORDER BY dt_deb ASC)) AS dt_fin
    FROM Tab
ORDER BY id_eve ASC
       , dt_deb ASC;
 
 
ID_EVE DT_DEB              DT_FIN             
 71416 19.09.2011 17:29:33 19.09.2011 17:29:51
 71416 20.09.2011 13:02:51 21.09.2011 13:03:09
 71416 20.09.2011 17:29:36 20.09.2011 17:45:00
 71416 21.09.2011 12:02:51 20.09.2011 17:29:36

Merci pour vos réponses

J'ai adapté votre requête Waldar. La simplification faite sur l'action ne permettait pas d'avoir le résultat. Voici donc la requête :

Voici la requête donnant les infos souhaitées :

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
 
WITH 
  Tab1 AS
  (
  SELECT 71416 AS id_eve, 1 AS action, TO_DATE('19/09/2011 17:29:33','DD/MM/YYYY HH24:mi:ss') AS dt_ev_deb, TO_DATE('20/09/2011 17:29:36','DD/MM/YYYY HH24:mi:ss') AS dt_ev_fin FROM dual UNION ALL
  SELECT 71416 AS id_eve, 2          , TO_DATE('20/09/2011 17:29:36','DD/MM/YYYY HH24:mi:ss') AS dt_ev_deb, TO_DATE('21/09/2011 13:03:09','DD/MM/YYYY HH24:mi:ss') AS dt_ev_fin FROM dual
  ),
  Tab2 AS
  (
    SELECT 71416 AS id_eve, 1 AS action TO_DATE('19/09/2011 17:29:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_deb, TO_DATE('20/09/2011 13:02:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_fin FROM dual UNION ALL
    SELECT 71416 AS id_eve, 2 AS action TO_DATE('20/09/2011 17:45:00','DD/MM/YYYY HH24:mi:ss') AS dt_ps_deb, TO_DATE('21/09/2011 12:02:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_fin FROM dual
  ),
  Tab AS
  (
  SELECT id_eve, action, dt_ev_deb AS dt_deb, dt_ev_fin AS dt_fin FROM Tab1
  union ALL
  SELECT id_eve, action, dt_ps_fin          , dt_ps_deb           FROM Tab2
  )
  SELECT id_eve
       , dt_deb
       , coalesce(lead(dt_fin) over(partition BY id_eve, action ORDER BY dt_deb ASC),
           first_value(dt_fin) over(partition BY id_eve, action ORDER BY dt_deb ASC)) AS dt_fin
    FROM Tab
ORDER BY id_eve ASC
       , dt_deb ASC;

Merci pour votre aide.

26/09/2011, 12h34
Waldar

Votre jeu de test est faux.
Vous avez mis le même id_eve pour deux événements différents.

Je maintiens donc ma solution.

Le fonctionnement est bien celui-là, et en regardant plus avant dans les données, je trouve des choses comme cela

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
 
WITH 
  Tab1 AS
  (
  SELECT 71416 AS id_eve, 1 AS action, TO_DATE('19/09/2011 17:29:33','DD/MM/YYYY HH24:mi:ss') AS dt_ev_deb, TO_DATE('20/09/2011 17:29:36','DD/MM/YYYY HH24:mi:ss') AS dt_ev_fin FROM dual UNION ALL
  SELECT 71416 AS id_eve, 2          , TO_DATE('20/09/2011 17:29:36','DD/MM/YYYY HH24:mi:ss') AS dt_ev_deb, TO_DATE('21/09/2011 13:03:09','DD/MM/YYYY HH24:mi:ss') AS dt_ev_fin FROM dual UNION ALL
  SELECT 71416 AS id_eve, 1 AS action, TO_DATE('21/09/2011 13:03:09','DD/MM/YYYY HH24:mi:ss') AS dt_ev_deb, TO_DATE('22/09/2011 12:31:22','DD/MM/YYYY HH24:mi:ss') AS dt_ev_fin FROM dual
  ),
  Tab2 AS
  (
    SELECT 71416 AS id_eve,1,TO_DATE('19/09/2011 17:29:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_deb, TO_DATE('20/09/2011 13:02:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_fin FROM dual UNION ALL
    SELECT 71416 AS id_eve,2, TO_DATE('20/09/2011 17:45:00','DD/MM/YYYY HH24:mi:ss') AS dt_ps_deb, TO_DATE('21/09/2011 12:02:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_fin FROM dual UNION ALL
    SELECT 71416 AS id_eve,1, TO_DATE('22/09/2011 09:45:00','DD/MM/YYYY HH24:mi:ss') AS dt_ps_deb, TO_DATE('22/09/2011 10:02:51','DD/MM/YYYY HH24:mi:ss') AS dt_ps_fin FROM dual
  ),
  Tab AS
  (
  SELECT id_eve, action, dt_ev_deb AS dt_deb, dt_ev_fin AS dt_fin FROM Tab1
  union ALL
  SELECT id_eve, NULL AS action, dt_ps_fin          , dt_ps_deb           FROM Tab2
  )
  SELECT id_eve
       , dt_deb                                                         ,
       lead(dt_fin) over(partition BY id_eve, action ORDER BY dt_deb ASC)
       , coalesce(lead(dt_fin) over(partition BY id_eve, action ORDER BY dt_deb ASC),
           first_value(dt_fin) over(partition BY id_eve, action ORDER BY dt_deb ASC)) AS dt_fin
    FROM Tab
ORDER BY id_eve ASC
       , dt_deb ASC;

et du coup, la solution est incorrecte.
id_eve n'est pas unique dans la table, il existe un autre champ pour clé

En reprenant votre solution initiale, je n'arrive pas à la solution attendue.

Code:

1
2
3
4
5
6
7
8
 
ID_EVE DT_DEB              DT_FIN             
 71416 19.09.2011 17:29:33 19.09.2011 17:29:51
 71416 20.09.2011 13:02:51 21.09.2011 13:03:09
 71416 20.09.2011 17:29:36 20.09.2011 17:45:00
 71416 21.09.2011 12:02:51 22.09.2011 12:31:22
 71416 21.09.2011 13:03:09 22.09.2011 09:45:00
 71416 22.09.2011 10:02:51 20.09.2011 17:29:36

28/09/2011, 12h47
Waldar

Merci de communiquer un petit jeu de données réelles, dans votre dernier message vos données initiales n'ont ni queue ni tête.

Bonjour,

voici un jeu de données pour un évènement

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
 
ID_EVE    ID_ACTEUR  ID_ACTION   DEBUT                       FIN
--------- ----------- ---------- -------------------------- -------------------
     70556         21          1        12/09/2011 16:13:22 13/09/2011 16:04:58
     70556         21          2        13/09/2011 16:04:58 13/09/2011 16:07:24
     70556         21          1        21/09/2011 15:30:37 21/09/2011 15:58:48
     70556         21          2        21/09/2011 15:58:48 21/09/2011 16:01:51
     70556         21          1        21/09/2011 16:01:51 21/09/2011 16:41:34
     70556         21          2        21/09/2011 16:41:34 21/09/2011 17:26:40
     70556         21          5        21/09/2011 17:26:40 21/09/2011 17:26:42
     70556         21          6        21/09/2011 17:26:42 21/09/2011 17:26:44
     70556         21          7        21/09/2011 17:26:44 03/10/2011 12:01:52
     70556         21          1        30/08/2011 15:19:56 30/08/2011 15:20:02
     70556         21          2        30/08/2011 15:20:02 30/08/2011 15:23:35
     70556         21          1        30/08/2011 15:23:35 30/08/2011 15:46:48
     70556         21          2        30/08/2011 15:46:48 12/09/2011 16:13:22
     70556         82          1        13/09/2011 16:07:24 15/09/2011 10:33:49
     70556         82          2        15/09/2011 10:33:49 21/09/2011 15:30:37
 
 
ID_EVE          DEBUT_PAUSE          FIN_PAUSE
------------- -------------------- -------------------
        70556   15/09/2011 11:23:35 15/09/2011 16:27:54
        70556   16/09/2011 17:12:36 19/09/2011 11:58:51
        70556   19/09/2011 12:00:19 21/09/2011 15:29:38

En espérant que cela soit plus parlant pour vous.

Merci de attention

D'accord, en fait par événement les périodes doivent être toujours complètement décrites ?

Sur ce jeu de test, est-ce bien le résultat suivant que vous souhaitez obtenir ?

    ID_EVE  ID_ACTEUR  ID_ACTION DT_DEB              DT_FIN             
---------- ---------- ---------- ------------------- -------------------
     70556         21          1 30/08/2011 15:19:56 30/08/2011 15:20:02
     70556         21          2 30/08/2011 15:20:02 30/08/2011 15:23:35
     70556         21          1 30/08/2011 15:23:35 30/08/2011 15:46:48
     70556         21          2 30/08/2011 15:46:48 12/09/2011 16:13:22
     70556         21          1 12/09/2011 16:13:22 13/09/2011 16:04:58
     70556         21          2 13/09/2011 16:04:58 13/09/2011 16:07:24
     70556         82          1 13/09/2011 16:07:24 15/09/2011 10:33:49
     70556         82          2 15/09/2011 10:33:49 15/09/2011 11:23:35
     70556         82          0 15/09/2011 11:23:35 15/09/2011 16:27:54
     70556         82          2 15/09/2011 16:27:54 16/09/2011 17:12:36
     70556         82          0 16/09/2011 17:12:36 19/09/2011 11:58:51
     70556         82          2 19/09/2011 11:58:51 19/09/2011 12:00:19
     70556         82          0 19/09/2011 12:00:19 21/09/2011 15:29:38
     70556         82          2 21/09/2011 15:29:38 21/09/2011 15:30:37
     70556         21          1 21/09/2011 15:30:37 21/09/2011 15:58:48
     70556         21          2 21/09/2011 15:58:48 21/09/2011 16:01:51
     70556         21          1 21/09/2011 16:01:51 21/09/2011 16:41:34
     70556         21          2 21/09/2011 16:41:34 21/09/2011 17:26:40
     70556         21          5 21/09/2011 17:26:40 21/09/2011 17:26:42
     70556         21          6 21/09/2011 17:26:42 21/09/2011 17:26:44
     70556         21          7 21/09/2011 17:26:44 03/10/2011 12:01:52

En gras les données rajoutées (avec 0 en id_action pour les pauses).

03/10/2011, 15h13
sfoggy

Tout à fait.

L'idée reste grosso modo la même que dans la première requête :

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
WITH Tab1 AS
(
select 70556 as id_eve, 21 as id_acteur, 1 as id_action, to_date('12/09/2011 16:13:22', 'dd/mm/yyyy hh24:mi:ss') as debut,  to_date('13/09/2011 16:04:58', 'dd/mm/yyyy hh24:mi:ss') as fin from dual union all
select 70556          , 21             , 2             , to_date('13/09/2011 16:04:58', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('13/09/2011 16:07:24', 'dd/mm/yyyy hh24:mi:ss')        from dual union all
select 70556          , 21             , 1             , to_date('21/09/2011 15:30:37', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 15:58:48', 'dd/mm/yyyy hh24:mi:ss')        from dual union all
select 70556          , 21             , 2             , to_date('21/09/2011 15:58:48', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 16:01:51', 'dd/mm/yyyy hh24:mi:ss')        from dual union all
select 70556          , 21             , 1             , to_date('21/09/2011 16:01:51', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 16:41:34', 'dd/mm/yyyy hh24:mi:ss')        from dual union all
select 70556          , 21             , 2             , to_date('21/09/2011 16:41:34', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 17:26:40', 'dd/mm/yyyy hh24:mi:ss')        from dual union all
select 70556          , 21             , 5             , to_date('21/09/2011 17:26:40', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 17:26:42', 'dd/mm/yyyy hh24:mi:ss')        from dual union all
select 70556          , 21             , 6             , to_date('21/09/2011 17:26:42', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 17:26:44', 'dd/mm/yyyy hh24:mi:ss')        from dual union all
select 70556          , 21             , 7             , to_date('21/09/2011 17:26:44', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('03/10/2011 12:01:52', 'dd/mm/yyyy hh24:mi:ss')        from dual union all
select 70556          , 21             , 1             , to_date('30/08/2011 15:19:56', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('30/08/2011 15:20:02', 'dd/mm/yyyy hh24:mi:ss')        from dual union all
select 70556          , 21             , 2             , to_date('30/08/2011 15:20:02', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('30/08/2011 15:23:35', 'dd/mm/yyyy hh24:mi:ss')        from dual union all
select 70556          , 21             , 1             , to_date('30/08/2011 15:23:35', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('30/08/2011 15:46:48', 'dd/mm/yyyy hh24:mi:ss')        from dual union all
select 70556          , 21             , 2             , to_date('30/08/2011 15:46:48', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('12/09/2011 16:13:22', 'dd/mm/yyyy hh24:mi:ss')        from dual union all
select 70556          , 82             , 1             , to_date('13/09/2011 16:07:24', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('15/09/2011 10:33:49', 'dd/mm/yyyy hh24:mi:ss')        from dual union all
select 70556          , 82             , 2             , to_date('15/09/2011 10:33:49', 'dd/mm/yyyy hh24:mi:ss')         ,  to_date('21/09/2011 15:30:37', 'dd/mm/yyyy hh24:mi:ss')        from dual
)
  ,  Tab2 AS
(
select 70556 as id_eve, to_date('15/09/2011 11:23:35', 'dd/mm/yyyy hh24:mi:ss') as debut_pause, to_date('15/09/2011 16:27:54', 'dd/mm/yyyy hh24:mi:ss') as fin_pause from dual union all
select 70556          , to_date('16/09/2011 17:12:36', 'dd/mm/yyyy hh24:mi:ss')               , to_date('19/09/2011 11:58:51', 'dd/mm/yyyy hh24:mi:ss')              from dual union all
select 70556          , to_date('19/09/2011 12:00:19', 'dd/mm/yyyy hh24:mi:ss')               , to_date('21/09/2011 15:29:38', 'dd/mm/yyyy hh24:mi:ss')              from dual
)
  ,  Tab AS
(
SELECT id_eve, id_acteur, id_action, debut AS dt_deb, fin AS dt_fin FROM Tab1
 UNION ALL
SELECT id_eve, null     , null     , fin_pause      , debut_pause   FROM Tab2
 UNION ALL
SELECT id_eve, null     , null     , debut_pause    , fin_pause     FROM Tab2
)
  SELECT id_eve
       , coalesce(id_acteur, last_value(id_acteur ignore nulls) over(partition BY id_eve ORDER BY dt_deb ASC)) as id_acteur
       , coalesce(id_action, case when dt_fin < dt_deb then last_value(id_action ignore nulls) over(partition BY id_eve ORDER BY dt_deb ASC) else 0 end) as id_action
       , dt_deb                                                         
       , case
           when lead(id_action, 1, id_action) over(partition by id_eve order by dt_deb asc) is null
             or dt_fin < dt_deb
           then lead(dt_deb) over(partition BY id_eve ORDER BY dt_deb ASC)
           else dt_fin
         end as dt_fin
    FROM Tab
ORDER BY id_eve ASC
       , dt_deb ASC;

L'algorithme ne doit par contre fonctionner qu'avec des données "propres", pas de chevauchement de période de pause ou multi acteur, et surement d'autres limitations.

03/10/2011, 17h30
sfoggy

Le chevauchement des périodes est impossible ainsi que le changement d'acteur.

Je jette un coup d' oeil pour comprendre le fonctionnement.

Merci pour la solution.