SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Needed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sigmas
Posting Yak Master

Belarus
172 Posts

Posted - 01/11/2013 :  12:24:53  Show Profile  Reply with Quote
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
52249 Posts

Posted - 01/11/2013 :  12:38:40  Show Profile  Reply with Quote
sorry i cant understand your output. Explain the logic in words

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

Go to Top of Page

bitsmed
Constraint Violating Yak Guru

277 Posts

Posted - 01/11/2013 :  17:29:49  Show Profile  Reply with Quote
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

Belarus
172 Posts

Posted - 01/12/2013 :  03:02:38  Show Profile  Reply with Quote
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

USA
649 Posts

Posted - 01/12/2013 :  17:05:08  Show Profile  Reply with Quote
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."
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000