| 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 |
|
|
|
weird group by