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
| With rd_cds_ts as
(
select 150 as obj_id, 30 as cds, to_date('01/01/2009', 'dd/mm/yyyy') as asof from dual union all
select 150 , 30 , to_date('02/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('05/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('06/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('07/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('08/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('09/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('12/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('13/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('14/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('15/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('16/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('19/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('20/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('21/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('22/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('23/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('26/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('27/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('28/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('29/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('30/01/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('02/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('03/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('05/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('06/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('09/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('10/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('11/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('13/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('16/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('17/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('18/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('19/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('20/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('23/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('24/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('25/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('26/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('27/02/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('02/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('03/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('04/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('05/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('06/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('09/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('10/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('11/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('12/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('13/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('16/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('17/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('18/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30.01 , to_date('19/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('20/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('23/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('24/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('25/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('26/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('27/03/2009', 'dd/mm/yyyy') from dual union all
select 150 , 30 , to_date('30/03/2009', 'dd/mm/yyyy') from dual
)
, filtre1 AS
(
SELECT obj_id, cds AS j, asof,
row_number() over( ORDER BY asof ASC) -
row_number() over(partition BY obj_id, cds ORDER BY asof ASC) AS grp_id
FROM rd_cds_ts
WHERE asof BETWEEN to_date('01/01/2009', 'dd/mm/yyyy')
AND to_date('31/12/2009', 'dd/mm/yyyy')
)
select obj_id, j,
min(asof) as dte_debut,
max(asof) as dte_fin
from filtre1
group by obj_id, j, grp_id
order by obj_id asc, dte_debut asc;
OBJ_ID J DTE_DEBUT DTE_FIN
150 30 01/01/2009 26/02/2009
150 30.01 27/02/2009 19/03/2009
150 30 20/03/2009 30/03/2009 |
Partager