Find conflicted date intervals using SQL

Question

Suppose I have following table in Sql Server 2008:

ItemId StartDate   EndDate
1      NULL        2011-01-15
2      2011-01-16  2011-01-25
3      2011-01-26  NULL

As you can see, this table has StartDate and EndDate columns. I want to validate data in these columns. Intervals cannot conflict with each other. So, the table above is valid, but the next table is invalid, becase first row has End Date greater than StartDate in the second row.

ItemId StartDate   EndDate
1      NULL        2011-01-17
2      2011-01-16  2011-01-25
3      2011-01-26  NULL

NULL means infinity here.

Could you help me to write a script for data validation?

[The second task]

Thanks for the answers. I have a complication. Let's assume, I have such table:

ItemId  IntervalId StartDate   EndDate
1       1          NULL        2011-01-15
2       1          2011-01-16  2011-01-25
3       1          2011-01-26  NULL
4       2          NULL        2011-01-17
5       2          2011-01-16  2011-01-25
6       2          2011-01-26  NULL

Here I want to validate intervals within a groups of IntervalId, but not within the whole table. So, Interval 1 will be valid, but Interval 2 will be invalid.

And also. Is it possible to add a constraint to the table in order to avoid such invalid records?

[Final Solution]

I created function to check if interval is conflicted:

CREATE FUNCTION [dbo].[fnIntervalConflict]
(
    @intervalId INT,
    @originalItemId INT,
    @startDate DATETIME,
    @endDate DATETIME
)
RETURNS BIT
AS
BEGIN

    SET @startDate = ISNULL(@startDate,'1/1/1753 12:00:00 AM')
    SET @endDate = ISNULL(@endDate,'12/31/9999 11:59:59 PM')

    DECLARE @conflict BIT = 0

    SELECT TOP 1 @conflict = 1
    FROM Items
    WHERE IntervalId = @intervalId
    AND ItemId <> @originalItemId
    AND (
    (ISNULL(StartDate,'1/1/1753 12:00:00 AM') >= @startDate 
     AND ISNULL(StartDate,'1/1/1753 12:00:00 AM') <= @endDate)
     OR (ISNULL(EndDate,'12/31/9999 11:59:59 PM') >= @startDate 
     AND ISNULL(EndDate,'12/31/9999 11:59:59 PM') <= @endDate)
    )

    RETURN @conflict
END

And then I added 2 constraints to my table:

ALTER TABLE dbo.Items ADD CONSTRAINT
    CK_Items_Dates CHECK (StartDate IS NULL OR EndDate IS NULL OR StartDate <= EndDate)

GO

and

ALTER TABLE dbo.Items ADD CONSTRAINT
    CK_Items_ValidInterval CHECK (([dbo].[fnIntervalConflict]([IntervalId], ItemId,[StartDate],[EndDate])=(0)))

GO

I know, the second constraint slows insert and update operations, but it is not very important for my application. And also, now I can call function fnIntervalConflict from my application code before inserts and updates of data in the table.

Answer 1

Something like this should give you all overlaping periods

SELECT
* 
FROM
mytable t1 
JOIN mytable t2 ON t1.EndDate>t2.StartDate AND t1.StartDate < t2.StartDate 

Edited for Adrians comment bellow

Answer 2

This will give you the rows that are incorrect.

Added ROW_NUMBER() as I didnt know if all entries where in order.

-- Testdata
declare @date datetime = '2011-01-17'

;with yourTable(itemID, startDate, endDate)
as
(
    SELECT  1,  NULL, @date
    UNION ALL
    SELECT  2,  dateadd(day, -1, @date),    DATEADD(day, 10, @date)
    UNION ALL
    SELECT  3,  DATEADD(day, 60, @date),    NULL
)

-- End testdata

,tmp
as
(
    select  *
            ,ROW_NUMBER() OVER(order by startDate) as rowno 
    from    yourTable
)

select  *
from    tmp t1
left join   tmp t2
    on t1.rowno = t2.rowno - 1
where   t1.endDate > t2.startDate

EDIT: As for the updated question:

Just add a PARTITION BY clause to the ROW_NUMBER() query and alter the join.

-- Testdata
declare @date datetime = '2011-01-17'

;with yourTable(itemID, startDate, endDate, intervalID)
as
(
    SELECT  1,  NULL, @date, 1
    UNION ALL
    SELECT  2,  dateadd(day, 1, @date), DATEADD(day, 10, @date),1
    UNION ALL
    SELECT  3,  DATEADD(day, 60, @date),    NULL,   1
    UNION ALL
    SELECT  4,  NULL, @date, 2
    UNION ALL
    SELECT  5,  dateadd(day, -1, @date),    DATEADD(day, 10, @date),2
    UNION ALL
    SELECT  6,  DATEADD(day, 60, @date),    NULL,   2
)

-- End testdata

,tmp
as
(
    select  *
            ,ROW_NUMBER() OVER(partition by intervalID order by startDate) as rowno 
    from    yourTable
)

select  *
from    tmp t1
left join   tmp t2
    on t1.rowno = t2.rowno - 1
    and t1.intervalID = t2.intervalID
where   t1.endDate > t2.startDate

Answer 3

declare @T table (ItemId int, IntervalID int, StartDate datetime,   EndDate datetime)

insert into @T
select 1, 1,  NULL,        '2011-01-15' union all
select 2, 1, '2011-01-16', '2011-01-25' union all
select 3, 1, '2011-01-26',  NULL        union all
select 4, 2,  NULL,        '2011-01-17' union all
select 5, 2, '2011-01-16', '2011-01-25' union all
select 6, 2, '2011-01-26',  NULL

select T1.*
from @T as T1
  inner join @T as T2
    on coalesce(T1.StartDate, '1753-01-01') < coalesce(T2.EndDate, '9999-12-31') and
       coalesce(T1.EndDate, '9999-12-31') > coalesce(T2.StartDate, '1753-01-01') and
       T1.IntervalID = T2.IntervalID and
       T1.ItemId <> T2.ItemId

Result:

ItemId      IntervalID  StartDate               EndDate
----------- ----------- ----------------------- -----------------------
5           2           2011-01-16 00:00:00.000 2011-01-25 00:00:00.000
4           2           NULL                    2011-01-17 00:00:00.000