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)))