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 tab0(CMAT, CSRV, CDEB, CFIN) as
(select 52, 2510, '2015-01-01', '2015-06-30' union all
select 52, 2510, '2015-07-01', '2015-12-31' union all
select 52, 2510, '2016-01-01', '2016-06-30' union all
select 52, 3120, '2016-07-01', '2016-12-31' union all
select 52, 3120, '2017-01-01', '2017-12-31' union all
select 52, 2510, '2018-01-01', '2099-12-31' union all
select 61, 2510, '2015-01-15', '2015-04-30' union all
select 61, 2510, '2015-05-01', '2015-10-31' union all
select 61, 3360, '2015-11-01', '2016-12-31' union all
select 61, 2510, '2017-01-01', '2099-12-31'
)
, tgrp as
(select tab0.*
, row_number()
over(partition by CMAT
order by CDEB)
- row_number()
over(partition by CMAT, CSRV
order by CDEB) as CTRI
from tab0
)
select CMAT
, CSRV
, min(CDEB)
, max(CFIN)
from tgrp
group by CMAT
, CSRV
, CTRI
order by CMAT
, min(CDEB) |
Partager