how to Avoid the intervals
-
hi to all,
i have a problem in getting some logic here this is my existing table structure
DECLARE @tbl AS TABLE (ID INT IDENTITY(1,1), LNAme VARCHAR(50),FrmPoint INT, ToPoint INT)
INSERT INTO @tbl (LNAme, FrmPoint, ToPoint)
SELECT 'Level1',101,200
INSERT INTO @tbl (LNAme, FrmPoint, ToPoint)
SELECT 'Level2',201,300
INSERT INTO @tbl (LNAme, FrmPoint, ToPoint)
SELECT 'Level3',301,400
SELECT * from @tbl
my problem is when i insert a new record i must avoid the existing intervals
i insert data's to this table through a stored procedure,
i have made a checking on that procedure like this
IF EXISTS(
SELECT *
FROM PLevel
WHERE FrmPoint<= @FrmPoint AND ToPoint>= @FrmPoint
)
BEGIN
RAISERROR ('Interval Does Not Exists', 16, 1)
GOTO ErrLbl
END
IF EXISTS(
SELECT *
FROM PLevel
WHERE FrmPoint<= @ToPoint AND ToPoint>= @ToPoint
)
BEGIN
RAISERROR ('Interval Does Not Exists', 16, 1)
GOTO ErrLbl
END
it seems working fine 🙂
until i enter this values Fr0m point ->25 and to point -> 600 :crying:
This interval contains some other intervals already Entered,
i mentioned above Sample data like 101-200, and so on
so i must avoid this entry
i tried in a lot of ways nothing will help me
i think my checking is wrong here? :unsure: :unsure: :unsure:
i think i need some help, hope i can get some help here
thanks in advance for helping me
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
I am assuming that selecting MAX(ToPoint) and making sure that your range is above that is not adequate?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
please elaborate the select statemet howshould i use the max statement
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Something like this:
declare @MaxToPoint Intselect @MaxToPoint = max(ToPoint) from PLevel
If @frmPoint <= @MaxToPoint then --> error
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
DECLARE @tbl AS TABLE (ID INT IDENTITY(1,1), LNAme VARCHAR(50),FrmPoint INT, ToPoint INT)
INSERT INTO @tbl (LNAme, FrmPoint, ToPoint)
SELECT 'Level1',101,200
INSERT INTO @tbl (LNAme, FrmPoint, ToPoint)
SELECT 'Level2',201,300
INSERT INTO @tbl (LNAme, FrmPoint, ToPoint)
SELECT 'Level3',301,400
SELECT * from @tbl
declare @frompoint int
set @frompoint = 225
declare @topoint int
set @topoint = 250
if not exists ( select 1 from @tbl where (@frompoint between Frmpoint and Topoint) or (@topoint between FrmPoint and ToPoint ))
BEgin
select 1
end
-
Jayanth_Kurup (6/27/2011)
Phil Parkin (6/27/2011)
Something like this:declare @MaxToPoint Intselect @MaxToPoint = max(ToPoint) from PLevel
If @frmPoint <= @MaxToPoint then --> error
Hi
This would remove scope for inserting a range between 1 and 100 though.
Indeed it would. And that was why I said this:
I am assuming that selecting MAX(ToPoint) and making sure that your range is above that is not adequate?
All you needed to do was answer 'yes' to this question and it would have saved us both some time.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
hi SSC
it is the same thing what i did but in a different way
thanks ssCrazy
but i can insert 25-99 this interval because this interval not lies in the above
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
These are the inputs
(@Fpoint, @Tpoint)
25,99 - allowed to insert
25,225 - raise a error
125,500- raise a error
25,500 - raise a error
401,500 - allowed to insert
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
thava (6/27/2011)
hi SSCit is the same thing what i did but in a different way
--
It is not. Your method would allow invalid ranges to be inserted, mine would not.
But to get the functionality you require will need a more-advanced technique. I'm sure someone else will have a neat solution for you soon.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
for give me phil i think sscrazy is your name, i said that statement to jayanth
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
thava (6/27/2011)
for give me phil i think sscrazy is your name, i said that statement to jayanthHaha, no problem. SSCrazy would frequently be a better name for me than Phil 🙂
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
after a long war i found this from the hint from phil
DECLARE @Fpoint INT,@Tpoint INT
SET @Fpoint =25
SET @Tpoint =205
DECLARE @tbl AS TABLE (ID INT IDENTITY(1, 1), LNAme VARCHAR(50), FrmPoint INT, ToPoint INT)
INSERT INTO @tbl
(
LNAme, FrmPoint, ToPoint
)
SELECT 'Level1', 101, 200
INSERT INTO @tbl
(
LNAme, FrmPoint, ToPoint
)
SELECT 'Level2', 201, 300
INSERT INTO @tbl
(
LNAme, FrmPoint, ToPoint
)
SELECT 'Level3', 301, 400
--
IF EXISTS(
SELECT *
FROM @tbl
WHERE FrmPoint<= @Fpoint AND ToPoint>= @Fpoint
)
BEGIN
PRINT 'ERROR'
END
IF EXISTS(
SELECT *
FROM @tbl
WHERE FrmPoint<= @Tpoint AND ToPoint>= @Tpoint
)
BEGIN
PRINT 'ERROR'
END
IF (
(
SELECT MAX(ToPoint)
FROM @tbl
)<@Tpoint
) AND (
(
SELECT MIN(FrmPoint)
FROM @tbl
)>@Fpoint
)
BEGIN
PRINT 'ERROR'
END
hope fully this is not a clean one but now it satisfies my condition so any one simplify it
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
thava (6/27/2011)
hope fully this is not a clean one but now it satisfies my condition so any one simplify itThis is a variation of the overlapping date ranges problem. Here is the simplest solution. (This assumes that there is error checking on the UI to prevent people from @FrmDate and @ToDate in the wrong order.)
IF EXISTS (
SELECT *
FROM @tbl
WHERE @FrmPoint <= ToPoint
AND @ToPoint >= FrmPoint
)
BEGIN
RAISERROR ('Interval Does Not Exists', 16, 1)
GOTO ErrLbl
END
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply