How can I allow to add or update score range in SQL; so that the newly added or updates score range does not get overlapped with the existing one.

Question

0.00/5 (No votes)

See more:

Hi all,

I have a table with some bands defined like

BandID | ScoreFrom  | ScoreTo
---------------------------------------
1      |   0        |  95
2      |   96       |  97
3      |   98       |  100

I would like to add a new score range with some validations like they should not get overlapped with existing ranges.

Like when adding ScoreFrom 97 and ScoreTo 100 it shouldn't allow it to be added.

Also while updating score range it should allow ScoreFrom and ScoreTo in a way so that the range not gets overlapped.

Please help.

Posted 25-Jan-14 2:07am

Nilesh Agniohtri

Updated 25-Jan-14 2:14am

André Kraak

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2014-01-25T03:16:00

Assuming your table is called score and BandID is auto-increment, inserting new score range is pretty straight forward:

SQL

CREATE PROCEDURE InsertScore
@newScoreFromValue INT,
@newScoreToValue INT
AS
BEGIN
INSERT INTO score s1 (s1.ScoreFrom, s1.ScoreTo) VALUES (@newScoreFromValue, @newScoreToValue)
WHERE @newScoreFromValue < @newScoreToValue AND NOT EXISTS
(
SELECT * FROM score s2 WHERE s2.ScoreTo >= @newScoreFromValue
)
END

For updating, if I understand your question correctly, there are 3 criteria to consider in order not to "overlap":

SQL

CREATE PROCEDURE UpdateScore 
@BandID INT,
@newScoreFromValue INT,
@newScoreToValue INT 
AS
BEGIN
UPDATE score s1 SET s1.ScoreFrom = @newScoreFromValue, s1.ScoreTo = @newScoreToValue 
WHERE s1.BandID = @BandID AND @newScoreFromValue < @newScoreToValue 
AND NOT EXISTS
(
SELECT * FROM score s2 WHERE s2.BandID <> @BandID AND s2.ScoreFrom BETWEEN @newScoreFromValue AND @newScoreToValue
)
AND NOT EXISTS
(
SELECT * FROM score s3 WHERE s3.BandID <> @BandID AND s3.ScoreTo BETWEEN @newScoreFromValue AND @newScoreToValue
)
AND NOT EXISTS
(
SELECT * FROM score s4 WHERE  s4.BandID <> @BandID AND s4.ScoreFrom <= @newScoreFromValue AND s4.ScoreTo >= @newScoreToValue
)
END

Hope it works. If not, you may want to adapt and modify.