0

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

user1480192
  • 665
  • 8
  • 23
  • Possible duplicate of [Comparing date ranges](https://stackoverflow.com/questions/143552/comparing-date-ranges) – Alex Nov 12 '17 at 21:54
  • The question is not how to check for date overlaps. To clarify, the question is if there is an intelligent way to check all of the dates in one period against all of the dates in the other period without checking each of them separately. – user1480192 Nov 13 '17 at 06:31
  • I don't follow you, maybe you can provide an additional example. – Alex Nov 13 '17 at 06:38
  • It is simple to take a booking from the source, add a month to its to and from dates, and see if there is a conflict between these new dates and the target period, and then do this for each of the existing bookings in the source period. However with hundreds and thousands of bookings this takes a very long time. The question is whether there is a simpler and quicker way of doing this, for example linking the table onto itself. I tried but could not figure it out. – user1480192 Nov 14 '17 at 09:02
  • What you have described can be done in one query, is this what you are struggling with? The query is relatively simple: select records for **the** week, use `DATEADD` to add 1 week to dates - that makes your first query, then write another query where you select the next week, then you left join the two quires and only insert records where no match is found in the second query. – Alex Nov 14 '17 at 09:25
  • That is exactly what I am struggling with I can not figure out how to write and join these two queries – user1480192 Nov 14 '17 at 17:41

1 Answers1

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.

Alex
  • 4,885
  • 3
  • 19
  • 39