How to find out if time is in sequence in SQL Server 2008

I have a table containing some Timespans (as two TIME columns)

Eg:

TimeBegin        TimeEnd
---------------- ----------------
00:00:00.0000000 01:00:00.0000000
01:00:00.0000000 02:00:00.0000000
01:30:00.0000000 03:00:00.0000000

I need to figure out whether the timespans form a sequence (in the above case from 00.00 - 03.00). Some of the timespans might overlap, as is the case above.

Edit I extended my table to:

WeekDay     TimeBegin        TimeEnd
----------- ---------------- ----------------
3           00:00:00.0000000 01:00:00.0000000
3           01:00:00.0000000 02:00:00.0000000
3           02:30:00.0000000 04:00:00.0000000

and used the query:

;with sequenced as (
    select *,rn=ROW_NUMBER() over (order by timebegin)
    from tbl
    where weekday(timebegin) = 2)
select *
from sequenced a开发者_JS百科
join sequenced b on a.rn=b.rn-1
where a.TimeEnd < b.TimeBegin

but it says ''weekday' is not a recognized built-in function name.'

so I changed it to:

;with sequenced as (
    select *,rn=ROW_NUMBER() over (order by timebegin)
    from tbl
    where weekday = 2)
select *
from sequenced a
join sequenced b on a.rn=b.rn-1
where a.TimeEnd < b.TimeBegin

but then it returns the row containing

WeekDay TimeBegin   TimeEnd
3        01:00:00.0000000   02:00:00.0000000

which is actually in the sequence?

thanks Thomas

since you only need true or false for the entire sequence, you can follow this algorithm:

1. sort the sequence in order of TimeBegin
2. put them in a temp table, include row_number
3. for every row starting with the second one:
    check if TimeBegin is smaller than or equal to the TimeEnd for the previous row
        if no, return false
4. return true

edit: just realized this fails in some weird cases like this:

TimeBegin        TimeEnd
---------------- ----------------
00:00:00.0000000 05:00:00.0000000
01:00:00.0000000 02:00:00.0000000
02:30:00.0000000 03:00:00.0000000

this should still count as a sequence (I think) and return true, but my algorithm doesn't account for this scenario. To fix this, when inserting into the temp table, remove the records that are completely "contained" in the previous record (i.e. if TimeBegin[i] > TimeBegin[i-1] and TimeEnd[i] < TimeEnd[i-1])

This checks EVERY single record in the table in time order, and shows ALL the gaps. If there are no records shown, then you have no gaps == the data forms an unbroken sequence

;with sequenced as (
    select *,rn=ROW_NUMBER() over (order by timebegin) from tbl)
select *
from sequenced a
join sequenced b on a.rn=b.rn-1
where a.TimeEnd < b.TimeBegin

Assuming this is a timetable (only a week's data) and you only want to check within a specific day, you can use

;with sequenced as (
    select *,rn=ROW_NUMBER() over (order by timebegin)
    from tbl
    where weekday(timebegin) = 2)
select *
from sequenced a
join sequenced b on a.rn=b.rn-1
where a.TimeEnd < b.TimeBegin

But it covers only the records that begin on weekday 2 (Monday) and any time spans that cross midnight are not considered.

If this covers MULTIPLE weeks and you need to check EACH Monday, then it gets more complicated.

;with sequenced as (
    select *,
           Date = DateDiff(d,0,timebegin),
           rn   = ROW_NUMBER() over (order by timebegin)
    from tbl
    where weekday(timebegin) = 2)
select *
from sequenced a
join sequenced b on a.rn = b.rn-1 and a.Date = b.Date
where a.TimeEnd < b.TimeBegin