Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Posts
    2

    Unanswered: Overlapping Sets

    I have the following table structure

    CREATE TABLE [dbo].[QDisc](
    [Id] [int] NOT NULL,
    [MinVal] [int] NOT NULL,
    [MaxVal] [int] NOT NULL,
    [PerVal] [int] NOT NULL,
    CONSTRAINT [PK_QDisc] PRIMARY KEY CLUSTERED
    (
    [Id] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    I need to be able to select unique overlapping sets of data based on the minval and maxval.

    Simple Example
    minval maxval
    5 15
    16 25
    10 20

    query would produce
    minval maxval
    5 10
    11 15
    16 20
    21 25

    More Complex example
    minval maxval
    5 15
    16 25
    10 20
    7 7
    1 100

    query would produce
    minval maxval
    1 5
    6 6
    7 7
    8 10
    11 15
    16 20
    21 25
    26 100

    Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Extra points for doing your homework assignment for you?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by beekde
    Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.
    Gosh - generous and gracious. How super.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2007
    Posts
    2
    I'll give myself extra credit


    ALTER PROCEDURE [dbo].[usp_Select_Disc]
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    select minval, maxval into #QtyRange from qdisc


    declare @minval int
    declare @maxval int
    declare @xminval int
    declare @xmaxval int

    declare @pmaxval int
    declare @pmaxva2 int

    declare @loopflg bit
    set @loopflg = 1
    while @loopflg = 1
    begin
    set @loopflg = 0
    DECLARE curSel CURSOR FOR select minval, maxval from #QtyRange order by minval
    open curSel
    fetch next from curSel into @minval, @maxval
    WHILE (@@FETCH_STATUS <> -1)
    begin

    set @xminval = null
    set @xmaxval = null

    select top(1) @xminval = minval, @xmaxval = maxval from #QtyRange where minval > @minval or maxval > @maxval order by minval


    if @maxval > @xminval
    begin
    set @pmaxval = @maxval
    if @minval = @xminval
    begin
    set @pmaxva2 = @xmaxval
    update #QtyRange set maxval = @xminval where minval = @minval and maxval = @maxval
    update #QtyRange set minval = @xminval + 1, maxval = @pmaxval where minval = @xminval and maxval = @xmaxval
    insert into #QtyRange values (@pmaxval + 1, @pmaxva2)
    set @loopflg = 1
    break
    end
    update #QtyRange set maxval = @xminval - 1 where minval = @minval and maxval = @maxval

    if @xmaxval > @pmaxval
    begin
    set @pmaxva2 = @xmaxval
    update #QtyRange set maxval = @pmaxval where minval = @xminval and maxval = @xmaxval
    insert into #QtyRange values (@pmaxval + 1, @pmaxva2)
    set @loopflg = 1
    break
    end
    if @xmaxval < @pmaxval
    insert into #QtyRange values (@xmaxval + 1, @pmaxval)
    set @loopflg = 1
    end

    if @maxval = @xminval
    begin
    set @pmaxval = @maxval

    if @minval = @maxval
    begin
    update #QtyRange set minval = @pmaxval + 1 where minval = @xminval and maxval = @xmaxval
    set @loopflg = 1
    break
    end
    else
    begin
    update #QtyRange set maxval = @pmaxval - 1 where minval = @minval and maxval = @maxval
    if @xminval <> @xmaxval
    begin
    insert into #QtyRange values (@pmaxval, @pmaxval)
    update #QtyRange set minval = @pmaxval + 1 where minval = @xminval and maxval = @xmaxval
    set @loopflg = 1
    break
    end
    end
    end
    fetch next from curSel into @minval, @maxval
    end
    Close curSel
    DEALLOCATE curSel
    end


    select distinct(a.minval), a.maxval, sum(qdisc.perval) from #QtyRange a
    inner join qdisc on a.minval >= qdisc.minval and a.maxval <= qdisc.maxval
    group by a.minval,a.maxval
    order by minval
    END

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by beekde
    I'll give myself extra credit

    You shouldn't, you used a cursor.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Teddy
    You shouldn't, you used a cursor.
    I'm not sure it can be done without a cursor or some other form of looping. I see no way to do it with a simple SELECT statement.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    neither do I, but that was his self-professed requirement for extra points.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No no no. That was the requirement for YOU to get extra points. He, of course, is free to give himself as much credit as he wants.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    how many points do i need to get a free ship?

  10. #10
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    this worked for both the data sets provided by u. i have tried with a couple of other sets and appears to be working. interested to know if it worked at your end or not....
    Code:
    create table #tt1 (Id int identity (1,1), Val int )
    insert into #tt1 (Val) select Val from (
    	select Minval as Val from QDisc
    	union all
    	select Maxval as Val from QDisc
    	) A order by 1
    
    select B.Id,A.Val AVal, B.Val BVal, C.Val CVal into #tt2
    from #tt1 A, #tt1 B, #tt1 C where A.Id+1 =* B.Id and C.Id-1 =* B.Id
    
    update #tt2 set BVal=BVal-1 where AVal+1=BVal
    update #tt2 set CVal=Null where BVal+1=CVal
    update #tt2 set CVal=CVal-1 from #tt2 A where A.CVal= (select BVal from #tt2 where #tt2.id = A.id+1 and BVal=CVal)
    
    select 
    BVal+ case when BVal=(select min(BVal) from #tt2) then 0 when BVal= CVal then 0 else 1 end as MinVal,
    CVal as MaxVal
    from #tt2
    where CVal is not null

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •