Check on range values in tables??????? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Check on range values in tables???????

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2007-07-19 : 01:03:03

hi,

I have columns in table having range values stored in it like

table name as tblRange
column as RangeID,FromRange,ToRange
values like
11,1,2
22,5,7
33,8,9
44,12,15

now my question is how i check the FromRange to ToRange values exists or not
these values are user input like FromRange = 3 and ToRange = 4
or FromRange = 10 and ToRange = 11 or FromRange = 11 and ToRange = 12 (these are correct values)
If not then these values stored in table and if these values fall in above
any range then it should not allowed user to enter
like FromRange = 6 and ToRange = 8 (this is incorrect value)

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-19 : 01:47:51

DECLARE @RangeID

SELECT @RangeID = RangeID
FROM Table1
WHERE FromRange <= @FromRange
AND ToRange >= @ToRange

IF @RangeID IS NULL
PRINT 'Range does not exists.'
ELSE
PRINT 'Range ' + CAST(@RangeID AS VARCHAR(11) + ' does exists.'

Peter Larsson
Helsingborg, Sweden

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2007-07-20 : 05:26:48

Hi,

As per your reply I tried above query like

declare @FromRange int
DECLARE @RangeID int
declare @ToRange int

set @FromRange = 3
set @ToRange = 4

set @RangeID = ''

SELECT @RangeID = ID
FROM tblRange
WHERE RangeI <= @FromRange
AND RangeII >= @ToRange

select @RangeID
-----------------------------

it work fine
for values

set @FromRange = 10
set @ToRange = 12

but for these values

set @FromRange = 3
set @ToRange = 6

set @FromRange = 3
set @ToRange = 10

I am getting wrong output .. i will explain scenario..

I have some kind of work chain that I want to assign to my employees

suppose total chain work is from 1 to 12

I divide it into like 1 to 3,3 to 5,5 to 8,8 to 9,9 to 10 and lastly 10 to 12 like that..

suppose if I assign 1 to 3,3 to 5 to employee ID 11 and
8 to 9,10 to 12 to employee ID 22..

now i want to assign another chain of work to another employee
and if I give him work chain number 5 to 8 and 4 to 7 then my application will allow only 5 to 8 as valid and for 4 to 7 it should give me message that "these range is not valid work chain"

Regards
like work chain numbers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources