How to prevent new data to enter between or overlap existing range in SQL table

Question

0.00/5 (No votes)

See more:

There are two columns FromMinutes and ToMinutes) in my table and i entered data like 5-15 , 16-30, 31-60
Now i want to prevent clashing of range when i enter new range. E.g. new range cant be between or overlap existing range like 7-10 is invalid.12-25 is invalid and 1-70 is invalid. How to write this query in insert procedure?

my table structure is :

CREATE TABLE HRM.tbAttendanceTimeSlots
(
SlotId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
FromMinutes INT NULL,
ToMinutes INT NULL
)

What I have tried:

i tried
IF EXISTS(SELECT 1 FROM HRM.tbAttendanceTimeSlots WHERE (@FromMinutes BETWEEN FromMinutes AND Tominutes) AND (@ToMinutes BETWEEN FromMinutes AND Tominutes))
BEGIN
RETURN -1 --clashing
END
ELSE
BEGIN
INSERT INTO HRM.tbAttendanceTimeSlots
(
FromMinutes, ToMinutes
)
VALUES
(
@FromMinutes,@ToMinutes
)
RETURN 1
END

Posted 12-Oct-17 19:32pm

xpertzgurtej

Updated 13-Oct-17 3:04am

v3

Add a Solution

2 solutions

Solution 2

The simplest way to check for an overlap would be:

SQL

IF EXISTS
(
    SELECT 1 
    FROM HRM.tbAttendanceTimeSlots 
    WHERE @FromMinutes <= ToMinutes 
    AND @ToMinutes >= FromMinutes
)

Posted 13-Oct-17 3:03am

Richard Deeming

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)

Member 7870345 · Accepted Answer · 2017-10-12T20:55:00

Solution 1

I think that you only have to change AND with OR in your stament:

IF EXISTS(SELECT 1 FROM HRM.tbAttendanceTimeSlots WHERE (@FromMinutes BETWEEN FromMinutes AND Tominutes) OR  (@ToMinutes BETWEEN FromMinutes AND Tominutes))
BEGIN
RETURN -1 --clashing
END
ELSE
BEGIN
INSERT INTO HRM.tbAttendanceTimeSlots
(
FromMinutes, ToMinutes
)
VALUES
(
@FromMinutes,@ToMinutes
)
RETURN 1
END

Posted 12-Oct-17 20:55pm

Member 7870345

Comments

xpertzgurtej 13-Oct-17 3:23am

i already tried with OR instead of And.. OR works fine to prevent data in between existing range but not for overlapping range like 1-70 as i asked in my question

Member 7870345 13-Oct-17 7:07am

Try that:

IF EXISTS(SELECT 1 FROM HRM.tbAttendanceTimeSlots WHERE (@FromMinutes BETWEEN FromMinutes AND Tominutes) OR (@ToMinutes BETWEEN FromMinutes AND Tominutes))
BEGIN
RETURN -1 --clashing
END
IF EXISTS(SELECT 1 FROM HRM.tbAttendanceTimeSlots WHERE (FromMinutes BETWEEN @FromMinutes AND @Tominutes) OR (ToMinutes BETWEEN @FromMinutes AND @Tominutes))
BEGIN
RETURN -1 --OVERLAP
END

INSERT INTO HRM.tbAttendanceTimeSlots
(
FromMinutes, ToMinutes
)
VALUES
(
@FromMinutes,@ToMinutes
)
RETURN 1

xpertzgurtej 13-Oct-17 7:13am

it worked..thanks