Query to find out overlapping decimal ranges

Question

0.00/5 (No votes)

See more:

I need to find out overlapping decimal ranges. Below is my table structure.

id FromValue  ToValue Condition
1   10           15    Between
2   16           NULL  Equals
3   6            9     Between
4   17           19    Between
5   16           NULL  Greater Than.

When I insert row 5. I should not be able to insert because there is already a range exists between 17 to 19. When I try to insert Greater than 16 It should not allow.

Same condition applies with Less Than condition. If I try to insert less 6. It should not allow me to insert because 6 to 9 range already exists.

Please help me out with SQL Query.

[edit]Code block added - OriginalGriff[/edit]

What I have tried:

SQL

declare @fromValue decimal(18,5)
declare @toValue decimal(18,5)
select  * from RangeTest where  (fromValue is not null and toValue is not null  )  
and  ( (fromValue >= @fromValue and toValue =  @fromValue ))

Posted 28-Mar-17 20:11pm

sacraj

Updated 29-Mar-17 7:38am

OriginalGriff

v2

Add a Solution

Comments

CHill60 29-Mar-17 9:16am

The logic for checking the ranges is not entirely clear. If you could explain what you are actually trying to achieve, or from where do you get the data to insert into the table?

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)

NightWizzard · Accepted Answer · 2017-03-29T07:38:00

In what you have tried you don't take into account which type of comparison to use depending on the condition column.

I would use an INSTEAD OF INSERT trigger to check value(s) and condition like that:

SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[Check_Insert] 
   ON  [dbo].[RangeTest] 
  INSTEAD OF INSERT
AS 
BEGIN
	SET NOCOUNT ON;

	DECLARE @OK bit;
	DECLARE @FromValue int;
	DECLARE @ToValue int;
	DECLARE @Condition nvarchar(20);
    DECLARE curTest CURSOR LOCAL FAST_FORWARD FOR
		SELECT [FromValue]
			 , [ToValue]
			 , [Condition]
		 FROM inserted;

	OPEN curTest;

	WHILE (1 = 1)
		BEGIN
			FETCH NEXT FROM curTest INTO @FromValue, @ToValue, @Condition;

			IF (@@FETCH_STATUS <> 0)
				BREAK;

			SET @OK = 1;

			IF (@FromValue IS NOT NULL)
				BEGIN
					IF EXISTS(SELECT 1 
								FROM [dbo].[RangeTest] 
							   WHERE ([Condition] = 'Equals')
								 AND (@FromValue = [FromValue])
							 )
						BEGIN
							SET @OK = 0;
							RAISERROR ('FromValue conflicts with existing Equals entry', 1, 1);
						END

					IF EXISTS(SELECT 1 
								FROM [dbo].[RangeTest] 
							   WHERE ([Condition] = 'Between')
								 AND (@FromValue >= [FromValue])
								 AND (@FromValue <= [ToValue])
							 )
						BEGIN
							SET @OK = 0;
							RAISERROR ('FromValue conflicts with existing Between entry', 1, 1);
						END

					IF EXISTS(SELECT 1 
								FROM [dbo].[RangeTest] 
							   WHERE ([Condition] = 'Greater Than')
								 AND (@FromValue > [FromValue])
							 )
						BEGIN
							SET @OK = 0;
							RAISERROR ('FromValue conflicts with existing Greater-Than entry', 1, 1);
						END
				END

			IF (@ToValue IS NOT NULL)
				BEGIN
					IF EXISTS(SELECT 1 
								FROM [dbo].[RangeTest] 
							   WHERE ([Condition] = 'Equals')
								 AND (@ToValue = [FromValue])
							 )
						BEGIN
							SET @OK = 0;
							RAISERROR ('ToValue conflicts with existing Equals entry', 1, 1);
						END

					IF EXISTS(SELECT 1 
								FROM [dbo].[RangeTest] 
							   WHERE ([Condition] = 'Between')
								 AND (@ToValue >= [FromValue])
								 AND (@ToValue <= [ToValue])
							 )
						BEGIN
							SET @OK = 0;
							RAISERROR ('ToValue conflicts with existing Between entry', 1, 1);
						END

					IF EXISTS(SELECT 1 
								FROM [dbo].[RangeTest] 
							   WHERE ([Condition] = 'Greater Than')
								 AND (@ToValue > [FromValue])
							 )
						BEGIN
							SET @OK = 0;
							RAISERROR ('ToValue conflicts with existing Greater-Than entry', 1, 1);
						END
				END

			IF (@OK = 1)
				INSERT INTO [dbo].[RangeTest]
						   ([FromValue]
						   ,[ToValue]
						   ,[Condition])
					 VALUES
						   (@FromValue
						   ,@ToValue
						   ,@Condition)
						END

	CLOSE curTest;
	DEALLOCATE curTest;
END
GO