1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| declare @t table (Phase int, Horodatage datetime, Evenement varchar(50))
insert into @t select 1, '20090705 17:30:00', 'bla bla 1'
insert into @t select 1, '20090705 17:30:01', 'bla bla 2'
insert into @t select 2, '20090705 17:30:02', 'bla bla 3'
insert into @t select 2, '20090705 17:30:03', 'bla bla 4'
insert into @t select 2, '20090705 17:30:04', 'bla bla 5'
insert into @t select 2, '20090705 17:30:05', 'bla bla 5'
insert into @t select 1, '20090705 17:30:06', 'bla bla 6'
insert into @t select 1, '20090705 17:30:07', 'bla bla 7'
insert into @t select 2, '20090705 17:30:08', 'bla bla 8'
;WITH cte AS
(
SELECT Phase, Horodatage, Evenement,
ROW_NUMBER() OVER
(ORDER BY Horodatage) -
ROW_NUMBER() OVER
(PARTITION BY Phase ORDER BY Horodatage) AS Delta
FROM @t
)
SELECT Phase, Horodatage, Evenement,
DENSE_RANK() OVER (ORDER BY Delta, Phase) AS Rang
FROM cte
ORDER BY Horodatage |
Partager