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
| declare @t table (c1 smalldatetime, c2 tinyint)
insert into @t (c1, c2) values (getdate()-12, 4)
insert into @t (c1, c2) values (getdate()-11, 5)
insert into @t (c1, c2) values (getdate()-10, 12)
insert into @t (c1, c2) values (getdate()- 9, 12)
insert into @t (c1, c2) values (getdate()- 8, null)
insert into @t (c1, c2) values (getdate()- 7, null)
insert into @t (c1, c2) values (getdate()- 6, 8)
insert into @t (c1, c2) values (getdate()- 5, 4)
insert into @t (c1, c2) values (getdate()- 4, 4)
insert into @t (c1, c2) values (getdate()- 3, 5)
insert into @t (c1, c2) values (getdate()- 2, 12)
insert into @t (c1, c2) values (getdate()- 1, 12)
insert into @t (c1, c2) values (getdate() , 8)
;with CTInit (c1, c2, grp) as
(
select c1, c2,
row_number() over( order by c1 asc) -
row_number() over(partition by c2 order by c1 asc)
from @t
)
select min(c1) as c1, c2
from CTInit
group by c2, grp
order by c1 asc;
c1 c2
----------------------- ----
2010-12-24 15:22:00 4
2010-12-25 15:22:00 5
2010-12-26 15:22:00 12
2010-12-28 15:22:00 NULL
2010-12-30 15:22:00 8
2010-12-31 15:22:00 4
2011-01-02 15:22:00 5
2011-01-03 15:22:00 12
2011-01-05 15:22:00 8 |
Partager