1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| with matable as
(
select 'Store_A' as store, 'BU_A' as bu, '0001' as employee, date '2015-01-01' as dt_start, date '2015-12-01' as dt_end from dual union all
select 'Store_A' , 'BU_A' , '0001' , date '2014-01-01' , date '2015-12-31' from dual union all
select 'Store_B' , 'BU_A' , '0001' , date '2010-01-02' , date '2011-05-20' from dual union all
select 'Store_A' , 'BU_A' , '0002' , date '2015-01-01' , date '2015-12-31' from dual union all
select 'Store_A' , 'BU_A' , '0002' , null , null from dual union all
select 'Store_A' , 'BU_A' , '0003' , null , null from dual union all
select 'Store_A' , 'BU_B' , '0003' , null , null from dual
)
select store, bu, employee, dt_start, dt_end
, greatest(count(distinct store) over(partition by employee), count(distinct bu) over(partition by employee)) as nb_association
from matable;
STORE BU EMPLOYEE DT_START DT_END NB_ASSOCIATION
------- ---- -------- ---------- ---------- --------------
Store_A BU_A 0001 2015-01-01 2015-12-01 2
Store_A BU_A 0001 2014-01-01 2015-12-31 2
Store_B BU_A 0001 2010-01-02 2011-05-20 2
Store_A BU_A 0002 2015-01-01 2015-12-31 1
Store_A BU_A 0002 1
Store_A BU_A 0003 2
Store_A BU_B 0003 2 |
Partager