Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Needed

Author  Topic 

sigmas
Posting Yak Master

172 Posts

Posted - 2013-01-11 : 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 12:38:40
sorry i cant understand your output. Explain the logic in words

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-01-11 : 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.
Go to Top of Page

sigmas
Posting Yak Master

172 Posts

Posted - 2013-01-12 : 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...
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2013-01-12 : 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.
[url]http://www.solidq.com/sqj/Pages/2011-March-Issue/Packing-Intervals.aspx[/url]

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."
Go to Top of Page
   

- Advertisement -