TSQL - Use this as check constraint on a field to prevent having overlapping dates in a table

Post date: Sep 10, 2009 7:46:40 AM

GO

ALTER FUNCTION [dbo].[FNCountOverlappingDates] (@InputString AS VARCHAR(30)) RETURNS INTEGERAS-- Counts how many rows have overlapping dates

-- Parameters:-- @InputString: Semicolon seperated string in the format of DateFrom;DateTo;ForeignKey

--

-- @InputString Example:-- '20071201;20071231;1501' -- Output:-- Returns the number of rows found to have dates overlapping with supplied date period

BEGIN DECLARE @COUNT AS INT DECLARE @DateFrom DATETIME DECLARE @DateTo DATETIME DECLARE @ForeignKey INT -- Parse the parameters from the inputstring

SET @InputString = RTRIM(@InputString) SET @DateFrom = CAST(LEFT(@InputString, PATINDEX('%;%', @InputString) - 1) AS DATETIME) SET @InputString = RIGHT(@InputString, LEN(@InputString)- LEN(LEFT(@InputString, PATINDEX('%;%', @InputString) - 1) ) - 1 ) SET @DateTo = CAST(LEFT(@InputString, PATINDEX('%;%', @InputString) -1 ) AS DATETIME) SET @InputString = RIGHT(@InputString, LEN(@InputString) - LEN(LEFT(@InputString, PATINDEX('%;%', @InputString)) ) ) SET @ForeignKey = CAST(@InputString AS INT) SELECT @COUNT = COUNT(*)

FROM MyTable

WHERE (

( fldDateFrom BETWEEN @DateFrom AND @DateTo OR fldDateTo BETWEEN @DateFrom AND @DateTo ) OR ( @DateFrom BETWEEN fldDateFrom AND fldDateTo OR @DateTo BETWEEN fldDateFrom AND fldDateTo )

) AND MyTable.foreignkey = @ForeignKey

-- fldDateFrom and fldPortfolioId is primary key for the column, do not count the row that is about to be inserted. AND NOT( fldDateFrom = @DateFrom AND MyTable.foreignkey = @ForeignKey ) RETURN @COUNT

END

Use on column with calculated culumn, have it persisted

[fldOverlappingDates] AS ((((CONVERT([varchar](10),[fldDateFrom],(112))+';')+CONVERT([varchar](10),[fldDateTo],(112)))+';')+CONVERT([varchar](8),[foreignkey],0)) PERSISTED,

Then add the check constraint

ALTER TABLE [dbo].[MyTable] WITH CHECK ADD CONSTRAINT [DatesCannotBeOverlappingConstraint] CHECK (([dbo].[FNCountOverlappingDates]([fldOverlappingDates])=(0)))