DECLARE @AssigmentsTable TABLE (
	Id int NOT NULL PRIMARY KEY CLUSTERED (Id ASC),
	Employee nvarchar(32) NULL,
	Job nvarchar(32) NULL,
	StartDate date NULL,
	EndDate date NULL)

INSERT INTO @AssigmentsTable (Id, Employee, JOB, StartDate, EndDate) VALUES (1, 'John', 'Junior Cleaner', '2015.01.01', '2015.02.01')
INSERT INTO @AssigmentsTable (Id, Employee, JOB, StartDate, EndDate) VALUES (2, 'John', 'Cleaner', '2015.04.01', '2015.07.01')
INSERT INTO @AssigmentsTable (Id, Employee, JOB, StartDate, EndDate) VALUES (3, 'John', 'Seniour Cleaner', '2015.08.01', '2015.12.01')
INSERT INTO @AssigmentsTable (Id, Employee, JOB, StartDate, EndDate) VALUES (4, 'Mark', 'Boss', '2015.01.01', '2015.12.01')
INSERT INTO @AssigmentsTable (Id, Employee, JOB, StartDate, EndDate) VALUES (5, 'Phill', 'Helper', '2015.05.01', '2015.06.01')

SELECT * FROM @AssigmentsTable

UPDATE @AssigmentsTable SET Employee = 'Mark', StartDate = '2015.05.01', EndDate = '2015.06.01' WHERE Id = 5
SELECT 0, 'Should not be possible - would be an overlap between 4 and 5 (one employee in several positions)', NULL, NULL, NULL UNION ALL SELECT * FROM @AssigmentsTable
UPDATE @AssigmentsTable SET Employee = 'Phill', StartDate = '2015.05.01', EndDate = '2015.06.01' WHERE Id = 5--undo

UPDATE @AssigmentsTable SET Job = 'Cleaner', StartDate = '2015.05.01', EndDate = '2015.06.01' WHERE Id = 5
SELECT 0, 'Should not be possible - would be an overlap between 3 and 5 (two employees in same position)', NULL, NULL, NULL UNION ALL SELECT * FROM @AssigmentsTable
UPDATE @AssigmentsTable SET Job = 'Helper', StartDate = '2015.05.01', EndDate = '2015.06.01' WHERE Id = 5 --undo

UPDATE @AssigmentsTable SET Job = 'Cleaner', StartDate = '2015.08.01', EndDate = '2015.12.01' WHERE Id = 5
SELECT 0, 'Such udate is possible - we changed job without creating overlaps', NULL, NULL, NULL UNION ALL SELECT * FROM @AssigmentsTable