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
| SQL> with t as (
select 1814 as Empl_id, 11 as pro_id, 'PROJETC' as projet, to_date('18/09/2017','dd/mm/yyyy') as d_start, to_date('09/10/2017','dd/mm/yyyy') as d_end from dual union all
select 1814, 1457, 'PROJETB', to_date('10/10/2017','dd/mm/yyyy'), to_date('16/10/2017','dd/mm/yyyy') from dual union all
select 1814, 1457, 'PROJETB', to_date('17/10/2017','dd/mm/yyyy'), to_date(' 29/10/2017','dd/mm/yyyy') from dual union all
select 1814, 1457, 'PROJETB', to_date('30/10/2017','dd/mm/yyyy'), to_date(' 12/11/2017','dd/mm/yyyy') from dual union all
select 1814, 1457, 'PROJETB', to_date('13/11/2017','dd/mm/yyyy'), to_date(' 01/01/2018','dd/mm/yyyy') from dual union all
select 1814, 1457, 'PROJETB', to_date('02/01/2018','dd/mm/yyyy'), to_date(' 05/08/2018','dd/mm/yyyy') from dual union all
select 1814, 1457, 'PROJETB', to_date('06/08/2018','dd/mm/yyyy'), to_date(' 23/12/2018','dd/mm/yyyy') from dual union all
select 1814, 11, 'PROJETC', to_date('24/12/2018','dd/mm/yyyy'), to_date(' 27/12/2018','dd/mm/yyyy') from dual union all
select 1814, 11, 'PROJETC', to_date('28/12/2018','dd/mm/yyyy'), to_date(' 06/01/2019','dd/mm/yyyy') from dual union all
select 1814, 2482, 'PROJET_J', to_date('07/01/2019','dd/mm/yyyy'), to_date(' 14/01/2019','dd/mm/yyyy') from dual union all
select 1814, 2482, 'PROJET_J', to_date('15/01/2019','dd/mm/yyyy'), to_date(' 10/03/2019','dd/mm/yyyy') from dual union all
select 1814, 2614, 'PROJET_1', to_date('11/03/2019','dd/mm/yyyy'), to_date(' 07/07/2019','dd/mm/yyyy') from dual
)
select t.*
-- Méthode Tabibitosan
, row_number() over (partition by empl_id order by d_start)
- row_number() over (partition by Empl_id, pro_id order by d_start) as grp_tabibitosan
from t;
EMPL_ID PRO_ID PROJET D_START D_END GRP_TABIBITOSAN
---------- ---------- -------- -------- -------- ---------------
1814 11 PROJETC 18/09/17 09/10/17 0
1814 1457 PROJETB 10/10/17 16/10/17 1
1814 1457 PROJETB 17/10/17 29/10/17 1
1814 1457 PROJETB 30/10/17 12/11/17 1
1814 1457 PROJETB 13/11/17 01/01/18 1
1814 1457 PROJETB 02/01/18 05/08/18 1
1814 1457 PROJETB 06/08/18 23/12/18 1
1814 11 PROJETC 24/12/18 27/12/18 6
1814 11 PROJETC 28/12/18 06/01/19 6
1814 2482 PROJET_J 07/01/19 14/01/19 9
1814 2482 PROJET_J 15/01/19 10/03/19 9
1814 2614 PROJET_1 11/03/19 07/07/19 11
12 ligne(s) selectionnee(s).
SQL> |
Partager