Copying a schedule without overlap in SQL Server

Question

I have a simple table in SQL Server that is used to schedule room bookings. It has only three columns: roomid, fromdate, todate.

I need to enable copying existing bookings of one week (Sun-Sat) to another week, but only so long as there is no overlap with existing appointments.

For example, if in the source week I have

1/1/2017 10:00-11:00
1/2/2017 11:00-12:00
1/3/2017 12:00-13:00

and in the destination week I have

1/2/2017 09:00-11:30

The only the bookings for 1/1 and 1/3 would be copied. 1/2/2017 11:00 would not be copied because there would be an overlap with an existing booking in the source week.

PLEASE NOTE: I know how to check for if one booking overlaps another booking. However, There are multiple bookings in the source date range and multiple bookings in the target date range and the question is whether there is a smart way to check the source range against the target range WITHOUT checking every booking in the source range against every booking in the target range (for example by linking the table onto itself).

Answer 1

This is a prototype query that should get you want you want.

Note: it works for comparing one week of source data against one week of target data

SELECT *
FROM
        -- Current week appointments with +1 week dates
        ( SELECT roomid, DATEADD( week, 1, fromdate ) AS FutureFrom, DATEADD( week, 1, todate ) AS FutureTo
        FROM data
        WHERE [current week filter] ) AS CurrentWeek
    LEFT JOIN
            -- Next week appointments
            ( SELECT roomid, fromdate, todate
            FROM data
            WHERE [next week filter] ) AS NextWeek
        ON (FutureFrom < todate)  and  (FutureTo > fromdate) AND CurrentWeek.roomid = NextWeek.roomid
-- Only return CurrentWeek appointments where appointment date range does not overlap with next week's one
WHERE NextWeek.roomid IS NULL

Note that date range overlap logic is obtained from: Determine Whether Two Date Ranges Overlap

This query should help your get the start of week filters working:

-- Start of the current week
SELECT DATEADD(DD, 1 - DATEPART(DW, CONVERT( DATE, GETDATE())), CONVERT( DATE, GETDATE())) AS StartOfTheWeek

Reference: Get first day of week in SQL Server

Note: If you want a working sample you need to provide a full DDL script with more sample data.