Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38

    Question Unanswered: To check range of data

    Hi Guys,
    Need your help on this problem.
    Let say table name call tbl_range and 2 field call No1 and No2
    I have this set of record :-
    No1 No2
    1000 2000
    2001 3000
    5000 6000

    My problem, i want to check if user insert another set no No1 : 1500 No2 : 2500. So, this means that range already clash with another range. If this happened it will return 2 record (1000 - 2000) and (2001 - 3000). Can it be done and how?

    Regards,
    Shaffiq

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    see this example
    Code:
    -- create table---
    create table #tbl_range
    (No1 int,
    No2 int)
    --insert sample data
    insert into #tbl_range  select 1000,2000
    union
    select 1000,2000
    union
    select 2001,3000
    union
    select 5001,6000
    --select statement--
    declare @No1 int,@No2 int
    set @No1=3500
    set @No2=4000
    select * from #tbl_range 
    where
    (@No1 between No1 and No2) or
    (@No2 between No1 and No2)
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    He will need to check for ranges contained with ranges as well:
    Code:
    -- create table---
    create table #tbl_range
    (No1 int,
    No2 int)
    --insert sample data
    insert into #tbl_range  select 1000,2000
    union
    select 1000,2000
    union
    select 2001,3000
    union
    select 5001,6000
    --select statement--
    
    declare @No1 int,@No2 int
    set @No1=500
    set @No2=4000
    select	*
    from	#tbl_range 
    where	(@No1 between No1 and No2)
    	or (@No2 between No1 and No2)
    	or (@No1 < No1 and @No2 > No2)
    
    --cleanup
    drop table #tbl_range
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Quote Originally Posted by blindman
    He will need to check for ranges contained with ranges as well:
    Code:
    -- create table---
    create table #tbl_range
    (No1 int,
    No2 int)
    --insert sample data
    insert into #tbl_range  select 1000,2000
    union
    select 1000,2000
    union
    select 2001,3000
    union
    select 5001,6000
    --select statement--
     
    declare @No1 int,@No2 int
    set @No1=500
    set @No2=4000
    select    *
    from    #tbl_range 
    where    (@No1 between No1 and No2)
        or (@No2 between No1 and No2)
        or (@No1 < No1 and @No2 > No2)
     
    --cleanup
    drop table #tbl_range
    good eye sight blindman
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38
    Hi guys,
    all solution make my problem solve. Really appriaciate it. Thx guys

    Regards,
    Shaffiq

Posting Permissions

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