• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

Ensurie a number is not within the range of existing numbers.

I have a table that has a "start number", an "end number"  and a user id.

I need to be able to determine whether or not that a new series of numbers are within any of the ranges already entered into the database.

For example:
Start----End----User
1           10        user-id
11         20        user-id

If I enter 4 to 12, I should get a false result while entering 21 to 400 would give a positive result.

Thoughts?
0
bryan_z
Asked:
bryan_z
  • 2
  • 2
1 Solution
 
chapmandewCommented:
declare @no int
set @no  = 21
select case when @no between startdate and enddate then 'exists' else 'not' end
from tablename
0
 
sameer2010Commented:
Use the following code:
declare @startno int;
declare @endno int;
declare @userid varchar(8);
create table userrange(startno int, endno int,userid varchar(8));
set @startno = 21;
set @endno = 400;
set @userid = 'a';
select case when count(startno) = 0 then 'negative' else 'positive' end as resulttext
from userrange 
where userid = @userid
and @startno not between startno and endno
and @endno not between startno and endno;

Open in new window

0
 
SharathData EngineerCommented:
Is there any gam between start and end numbers for different users as mentioned like this.

Start----End----User
1           3        user-id
13         20        user-id

If yes and you enter 4 to 12, then what would be the result you are expecting?
0
 
SharathData EngineerCommented:
*gam = gap
0
 
sameer2010Commented:
I think, it should return positive. Because there are no rows that overlap with the given interval.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now