SQL determine schedule availability

Question

I have a tbl_availability that determines when a resource is available. The table structure is:

id - running id
startdate - when this availability starts
enddate - when this availability ends
dayofweek - weekday of availability
fromtime - start time
totime - end time

There can be multiple records for the same dayofweek, for example one record for Sundays 1000-1200 and another record for Sundays 1300-1400.

I am trying to figure out how to get two things:

1. Check when entering a new record that there is no conflict (overlap) with an existing record
2. Given a startdate and enddate, find all of the available periods that apply.

Answer 1

To determine if there's a conflict this query will return any overlapping time ranges:

SELECT * FROM tbl_availability 
WHERE (startDate > @CheckEnd AND startDate <= @CheckStart)
OR (endDate < @CheckStart AND endDate >= @CheckEnd)
OR (startDate >= @CheckStart AND endDate <= @CheckEnd)

The first part of the where clause checks for anything that overlaps the start time. The second part check for anything that overlaps the end time. The third part check for anything within the range.

To check for available time ranges for a specified duration use this:

SELECT * FROM
(SELECT endDate AS PeriodStart, (SELECT TOP 1 startDate 
  FROM tbl_availability as sub
  WHERE sub.startDate > tbl_availability.endDate
  ORDER by sub.startDate) AS PeriodEnd
FROM tbl_availability) AS OpenPeriods
WHERE DateDiff(MINUTE, PeriodStart, PeriodEnd) >= DateDiff(MINUTE, @RangeStart, @RangeEnd)

I haven't tested these queries, so there may have to be some tweaking going on here.