How to handle date ranges which cannot overlap?

Question

I need advice on how to handle the following database/table rule:

We have employees who must be in a given location from a begin date to an end date.  So, example:

Name     Location    BeginDate   EndDate

John Smith   Ohio   1/1/2005  5/31/2005

John Smith  Iowa   6/1/2005 12/31/2005

 

So, from the data above you can see 2 things:

(1)    John Smith’s dates that he is in a location never overlaps.  i.e. He can’t be in 2 places at the same time.  So, if we added a record that John Smith was in NY from 2/1/2005-2/28/2005 that would obviously be incorrect because (from the first record) he is already in Ohio.  How can this be handled in the database?  i.e. We want to restrict users from entering overlapping dates per employee.  Assuming I would need some trigger? 

(2)    John Smith’s dates that he is in a location does not have gaps.  i.e. The first record says John was in Ohio until 5/31/05 – then on the very next day he went to Iowa.  If we said John was in Iowa starting 3 months later on 9/1 instead of 6/1 then we would not know where John was for 3 months.  How can this be handled in the database?  i.e. We want to restrict users from entering date gaps per employee.  Again, assuming I would need some trigger?

Thanks in advance for your help with this.

 

Dan

Answer 1

Others may have more t-sql input, but this kind of logic is best implemented in the app layer, not sql server.

If users are using SQL Server directly, then I would look at building stored procedures to do the inserts/updates/deletes.  This proc can take a passed in employee ID (or whatever) and look up its dates and work accordingly with the new information.  Depending on your scenarios, you can build different alerts into the sproc.  I don't think a trigger is a good idea for "preventing" users from inserting the wrong data.  Again though, this is best done in the app layer if there is one.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 2

Try:

 

CREATE TABLE dbo.T (
[Name] varchar(35),
Location varchar(35),
BeginDate datetime NOT NULL,
EndDate datetime NULL,
CONSTRAINT CK_Dates CHECK (BeginDate <= EndDate)
)
go
CREATE TRIGGER tr_T_ins_upd ON dbo.T
FOR INSERT, UPDATE
AS
IF @@ROWCOUNT = 0 RETURN;

-- overlaping dates
IF EXISTS (
	SELECT *
	FROM inserted AS I INNER JOIN dbo.T ON I.[Name] = T.[Name] AND I.Location <> T.Location
	WHERE I.BeginDate <= T.EndDate AND I.EndDate >= T.BeginDate
)
BEGIN
ROLLBACK TRANSACTION;
RAISERROR('No overlaping allowed', 16, 1);
RETURN;
END

-- gaps
IF EXISTS (
	SELECT *
	FROM
		(
		SELECT
			T.[Name],
			T.Location,
			T.BeginDate,
			T.EndDate,
			ROW_NUMBER() OVER(PARTITION BY T.[Name] ORDER BY T.BeginDate) AS rn
		FROM
			dbo.T
		WHERE
			EXISTS (
			SELECT *
			FROM INSERTED AS I
			WHERE I.[Name] = T.[Name]
			)
		) AS A
		INNER JOIN
		(
		SELECT
			T.[Name],
			T.Location,
			T.BeginDate,
			T.EndDate,
			ROW_NUMBER() OVER(PARTITION BY T.[Name] ORDER BY T.BeginDate) AS rn
		FROM
			dbo.T
		WHERE
			EXISTS (
			SELECT *
			FROM INSERTED AS I
			WHERE I.[Name] = T.[Name]
			)			
		) AS B
		ON A.[Name] = B.[Name] AND A.rn = B.rn - 1
	WHERE
		DATEDIFF([day], A.EndDate, B.BeginDate) > 1	
	)
BEGIN
ROLLBACK TRANSACTION;
RAISERROR('No gaps allowed', 16, 1);
RETURN;
END
go
INSERT INTO dbo.T VALUES('John Smith', 'Ohio', '1/1/2005', '5/31/2005');
INSERT INTO dbo.T VALUES('John Smith', 'Iowa', '6/1/2005', '12/31/2005');
GO
DROP TABLE dbo.T;
GO

You can also take a look at this outstanding blog post by Alexander Kuznetsov.

Storing intervals of time with no overlaps
http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/storing-intervals-of-time-with-no-overlaps.aspx


AMB

Answer 3

Thanks Hunchback!

Used your trigger and it worked perfectly!  I did modify the 'no gaps' logic.  Tested and seems to work ok but if you see any gaps in my logic (no pun inteneded) please let me know.

IF

 

NOT EXISTS (

 

SELECT * FROM dbo.PortfolioHistory PH INNER JOIN inserted I

 

ON I.[portfolioHistory_portfolioID] = PH.[portfolioHistory_portfolioID]

 

WHERE

 

 

(DATEDIFF([day],PH.portfolioHistory_EndDate ,I.portfolioHistory_BeginDate) = 1)

 

OR

 

 

(DATEDIFF([day],I.portfolioHistory_EndDate,PH.portfolioHistory_BeginDate ) = 1)

)

BEGIN

ROLLBACK

 

TRANSACTION;

RAISERROR

 

('!!!!No gaps in dates in Portfolio History are allowed!!!!', 16, 1);

RETURN

 

;

END

Answer 4

I think that you are comparing a row to itself:

>> FROM dbo.PortfolioHistory PH INNER JOIN inserted I  ON I.[portfolioHistory_portfolioID] = PH.[portfolioHistory_portfolioID]

when the comparison should be between previous row to any one of the rows that were changed and the row that was changed.


AMB