Author |
Topic  |
|
xholax
Starting Member
Argentina
12 Posts |
Posted - 03/18/2013 : 19:16:32
|
Hello friends hope you can help me, i need the below query for my job but i cant get it :( :

--Sample Data DECLARE @Foo TABLE (oper CHAR(10), [start] int, [end] int , [status] int )
INSERT @Foo select 'James', 0,3,2 union all select 'James', 3,5,2 union all select 'James', 5,8,3 union all select 'James', 8,9,4 union all select 'James', 9,10,2 union all select 'James', 10,14,2 union all select 'James', 14,48,3
select * from @Foo
thanks in advance
|
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 03/18/2013 : 19:41:22
|
;with cte as
(
select *,
ROW_NUMBER() over (order by start,[end])-
ROW_NUMBER() over (PARTITION by status order by start,[end]) Grp
from
@Foo
)
select
oper,
MIN(start) [start],
MAX([end]) [end],
status
from
cte
group by
oper,
status,
grp
order by
[start],
[end] |
 |
|
xholax
Starting Member
Argentina
12 Posts |
Posted - 03/18/2013 : 23:05:13
|
thanks it works perfect, i didnt know about that kind of query partitions and rank where i can find more info about that , some recommend book ? |
Edited by - xholax on 03/18/2013 23:06:06 |
 |
|
|
Topic  |
|
|
|