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
| ;With MaTable (code, MaDate, Statut) as
(
select 1005, convert(datetime, '02/03/2012', 103), 'A' union all
select 1005, convert(datetime, '03/03/2012', 103), 'A' union all
select 1005, convert(datetime, '04/03/2012', 103), 'B' union all
select 1005, convert(datetime, '05/03/2012', 103), 'A' union all
select 1005, convert(datetime, '06/03/2012', 103), 'A' union all
select 1002, convert(datetime, '02/03/2012', 103), 'C' union all
select 1002, convert(datetime, '03/03/2012', 103), 'C' union all
select 1002, convert(datetime, '04/03/2012', 103), 'B'
)
, T0 (code, MaDate, Statut, grp) as
(
select code, MaDate, Statut
, row_number() over(partition by code order by MaDate asc)
- row_number() over(partition by code, statut order by MaDate asc)
from MaTable
)
select code
, min(MaDate) as DATE_DEB
, max(MaDate) as DATE_FIN
, row_number() over(partition by code order by min(MaDate) asc) as rang
, Statut
from T0
group by code, Statut, grp
order by code DESC, min(MaDate) ASC;
code DATE_DEB DATE_FIN rang Statut
----------- ----------------------- ----------------------- -------------------- ------
1005 2012-03-02 00:00:00.000 2012-03-03 00:00:00.000 1 A
1005 2012-03-04 00:00:00.000 2012-03-04 00:00:00.000 2 B
1005 2012-03-05 00:00:00.000 2012-03-06 00:00:00.000 3 A
1002 2012-03-02 00:00:00.000 2012-03-03 00:00:00.000 1 C
1002 2012-03-04 00:00:00.000 2012-03-04 00:00:00.000 2 B |
Partager