Just Code‎ > ‎

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

posted Sep 10, 2009, 12:46 AM by Peter Henell   [ updated Sep 15, 2009, 11:39 AM ]
ALTER FUNCTION [dbo].[FNCountOverlappingDates] (@InputString AS VARCHAR(30)) RETURNS INTEGER AS -- 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
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)))