| Author |
Topic |
|
xholax
Starting Member
12 Posts |
 Posted - 2013-03-18 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-18 : 19:41:22
|
[code];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][/code] |
 |
|
|
xholax
Starting Member
12 Posts |
Posted - 2013-03-18 : 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 ? |
|
|
|
|
|
|
weird group by