| Author |
 Topic  |
|
|
sigmas
Starting Member
24 Posts |
 Posted - 01/11/2013 : 12:24:53
|
I have a table:
declare @t table(grp, start_nbr, end_nbr)
insert @t values
(1, 1, 5),
(1, 6, 11),
(1, 11, 13),
(1, 13, 15),
(1, 19, 25),
(2, 30, 40),
(2, 40, 50);
Wanted result(merged rows):
grp--start_nbr----end_nbr
1-------1-----------5
1-------6-----------15
1-------19----------25
2-------30----------50
-01020304050607080910111213141516
_________
__________
____
____
1-05 6-15 |
Edited by - sigmas on 01/11/2013 12:27:17
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/11/2013 : 12:38:40
|
sorry i cant understand your output. Explain the logic in words
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
bitsmed
Yak Posting Veteran
Denmark
98 Posts |
Posted - 01/11/2013 : 17:29:49
|
The first part of the requested result, can be queried like this:
select destinct a.grp
,min(a.start_nbr) as start_nbr
,max(b.end_nbr) as end_nbr
from @t as a
left outer join @t as b
on b.grp=a.grp
and b.start_nbr=a.end_nbr
group by a.grp
Now the last part, should be done in your frontend program, as this seems to be the same result, just displayed like a text graph. |
|
|
|
sigmas
Starting Member
24 Posts |
Posted - 01/12/2013 : 03:02:38
|
quote:
Originally posted by bitsmed
The first part of the requested result, can be queried like this:
select destinct a.grp
,min(a.start_nbr) as start_nbr
,max(b.end_nbr) as end_nbr
from @t as a
left outer join @t as b
on b.grp=a.grp
and b.start_nbr=a.end_nbr
group by a.grp
Now the last part, should be done in your frontend program, as this seems to be the same result, just displayed like a text graph.
thanks,
the last part is not result. that shows only the sample data in chart format... |
|
|
|
Jeff Moden
Aged Yak Warrior
USA
643 Posts |
Posted - 01/12/2013 : 17:05:08
|
This will solve most overlapping ranges problems courtesy of Mr. Itzik Ben-Gan. For an explanation of the following code, please see the following URL.
http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx
WITH
C1 AS
(
SELECT Grp,
ts = start_nbr,
Type = +1,
e = NULL,
s = ROW_NUMBER() OVER(PARTITION BY Grp ORDER BY start_nbr)
FROM @t
UNION ALL
SELECT Grp,
ts = end_nbr,
Type = -1,
e = ROW_NUMBER() OVER(PARTITION BY Grp ORDER BY end_nbr),
s = NULL
FROM @t
)
,
C2 AS
(
SELECT C1.*,
se = ROW_NUMBER() OVER(PARTITION BY grp ORDER BY ts, type DESC)
FROM C1
)
,
C3 AS
(
SELECT Grp,
ts,
GrpNum = FLOOR((ROW_NUMBER() OVER(PARTITION BY grp ORDER BY ts) - 1) / 2 + 1)
FROM C2
WHERE COALESCE(s - (se - s) - 1, (se - e) - e) = 0
)
SELECT Grp, MIN(ts) AS Start_Nbr, max(ts) AS End_Nbr
FROM C3
GROUP BY Grp, GrpNum;
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".
First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it." |
|
|
| |
Topic |
|
|
|
Query Needed
