check data if it will overlapped

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 data if it will overlapped

Author

Topic

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-11-25 : 02:59:32

hi all,
i have a sample table here named rates


year min_rate max_rate
2008 10       20 
2008 55       79
2008 80       100 
2007 45       100 
2007 25       40
etc...

i want to have a validation where it will check first if data will overlapped the records.
for example, user accidentally insert this value


year - 2008,
min_rate - 15,
max_rate - 25

this should not be inserted to the tables and return a message because 15 will overlapped this one here. If 15 will be 21 it should be no problem.


year min_rate max_rate
2008 10       20
2008 55       79
2008 80       100 
2007 45       100 
2007 25       40
etc...

thnx for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 03:12:43

create an instead of insert trigger for this

CREATE TRIGGER TRG_Checkdata
ON rates
INSTEAD OF INSERT AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM inserted i
           INNER JOIN rates r
           ON r.year=i.year
           AND ((i.min_rate >= r.min_rate AND i.min_rate<=r.max_rate)
           OR  (i.max_rate >= r.min_rate AND i.max_rate<=r.max_rate)))
BEGIN
INSERT INTO rates
SELECT * FROM INSERTED

END
ELSE
BEGIN
RAISERROR 'Insertted record values overlaps with existing record',10,1
END
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 03:16:37

What if you insert two records that overlaps each other but not existing ones?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 03:19:02

[code]CREATE TRIGGER dbo.trgOverlap ON dbo.Rates
AFTER UPDATE,
INSERT
AS

IF EXISTS (SELECT * FROM Rates AS r1 INNER JOIN Rates AS r2 ON r2.min_rate <= r1.max_rate AND r2.max_rate >= r1.min_rate)
BEGIN
RAISERROR('Overlapping range values found.', 16, 1)
ROLLBACK TRAN
END[/code]

E 12°55'05.63"
N 56°04'39.26"

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2008-11-25 : 03:19:51

declare @t table (yar int, min_rate int, max_rate int)
insert @t
select 2008, 10,20 union all
select 2008, 55 , 79 union all
select 2008, 80, 100 union all
select 2007, 45, 100 union all
select 2007, 25 , 40

declare @yr int
declare @minrt int
declare @mxrt int
set @yr = 2008
set @minrt = 25
set @mxrt = 35

if exists(select 1
from @t
where yar = @yr
and ((@minrt between min_rate and max_rate)
or (@mxrt between min_rate and max_rate)
or (min_rate between @minrt and @mxrt)
or (max_rate between @minrt and @mxrt)))
select 'Duplicates'
else
select 'Can be inserted'

--------------------------------------------------
S.Ahamed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 03:30:54

[code]CREATE TABLE dbo.tSample
(
theYear SMALLINT,
minRate TINYINT,
maxRate TINYINT
)

INSERT dbo.tSample
(
theYear,
minRate,
maxRate
)
SELECT 2008, 10, 20 UNION ALL
SELECT 2008, 55, 79 UNION ALL
SELECT 2008, 80, 100 UNION ALL
SELECT 2007, 45, 100 UNION ALL
SELECT 2007, 25, 40

GO

CREATE TRIGGER dbo.trgOverlap ON dbo.tSample
AFTER UPDATE,
INSERT
AS

IF EXISTS ( SELECT *
FROM dbo.tSample AS s1
INNER JOIN dbo.tSample AS s2 ON s2.theYear = s1.theYear
WHERE s2.minRate <= s1.maxRate
AND s2.maxRate >= s1.minRate
AND s2.minRate <> s1.minRate
AND s2.maxRate <> s1.maxRate
)
BEGIN
RAISERROR('Overlapping range values found.', 16, 1)
ROLLBACK TRAN
END
GO

SELECT *
FROM dbo.tSample
ORDER BY theYear,
minRate

INSERT dbo.tSample (theYear, minRate, maxRate) VALUES (2008, 21, 25)

SELECT *
FROM dbo.tSample
ORDER BY theYear,
minRate

INSERT dbo.tSample (theYear, minRate, maxRate) VALUES (2008, 15, 25)

SELECT *
FROM dbo.tSample
ORDER BY theYear,
minRate

DROP TABLE dbo.tSample[/code]

E 12°55'05.63"
N 56°04'39.26"

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-11-25 : 03:32:09

thnk you for all your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 03:41:02

If you have a primary key in you real table, the code can look something similar to this

CREATE TABLE	dbo.tSample
		(
			rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			theYear SMALLINT,
			minRate TINYINT,
			maxRate TINYINT
		)

INSERT	dbo.tSample
	(
		theYear,
		minRate,
		maxRate
	)
SELECT	2008, 10,  20 UNION ALL
SELECT	2008, 55,  79 UNION ALL
SELECT	2008, 80, 100 UNION ALL
SELECT	2007, 45, 100 UNION ALL
SELECT	2007, 25,  40

GO

CREATE TRIGGER	dbo.trgOverlap ON dbo.tSample
AFTER	UPDATE,
	INSERT
AS

IF EXISTS	(	SELECT		*
			FROM		dbo.tSample AS s1
			INNER JOIN	dbo.tSample AS s2 ON s2.theYear = s1.theYear
			WHERE		s2.minRate <= s1.maxRate
					AND s2.maxRate >= s1.minRate
					AND s2.rowID <> s1.rowID
		)
	BEGIN
		RAISERROR('Overlapping range values found.', 16, 1)
		ROLLBACK TRAN
	END
GO

SELECT		*
FROM		dbo.tSample
ORDER BY	theYear,
		minRate

INSERT dbo.tSample (theYear, minRate, maxRate) VALUES (2008, 21, 25)

SELECT		*
FROM		dbo.tSample
ORDER BY	theYear,
		minRate

INSERT dbo.tSample (theYear, minRate, maxRate) VALUES (2008, 15, 25)

SELECT		*
FROM		dbo.tSample
ORDER BY	theYear,
		minRate

DROP TABLE	dbo.tSample

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources