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_nbr1-------1-----------51-------6-----------151-------19----------252-------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 MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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... |
|
|
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." |
|
|
|
|
|