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
| with t2 (id, date_abs, code_abs) as
(
select 1, 20130101, 101 from dual union all
select 1, 20130102, 101 from dual union all
select 1, 20130103, 101 from dual union all
select 1, 20130201, 145 from dual union all
select 1, 20130202, 145 from dual union all
select 1, 20130203, 145 from dual union all
select 1, 20130204, 145 from dual union all
select 2, 20130101, 101 from dual union all
select 2, 20130102, 101 from dual
)
SELECT id
, min(date_abs) AS dtdeb
, MAX(date_abs) AS dtfin
, code_abs
, COUNT(*) AS nbjr
FROM (SELECT id
, date_abs
, code_abs
, ROW_NUMBER() over( ORDER BY date_abs ASC)
- ROW_NUMBER() over(partition BY code_abs ORDER BY date_abs ASC) AS grp
FROM t2)
group by id, code_abs, grp
order by id, dtdeb;
ID DTDEB DTFIN CODE_ABS NBJR
-- -------- -------- -------- ----
1 20130101 20130103 101 3
1 20130201 20130204 145 4
2 20130101 20130102 101 2 |
Partager