SQL query to find available slots with multiple providers and users

I'd re-imagine this data as transactional using CTEs, compute balances and then find the points where the balance is non-zero.

Conceptually, that means there's a +1 doctor transaction on each doctor's start time, and a -1 doctor transaction on each doctor's end time. Patients are just the reverse, there is a -1 doctor transaction at their start time and a +1 doctor transaction at their start time plus duration.

So something like:

WITH DrStarts AS (
    SELECT 
        1 [Drs], 
        [Dates].[Date] + [DrSched].StartTime [Timestamp]
    FROM        [DrSched]
    INNER JOIN  [Dates]
    ON          WEEKDAY([Dates]) = [DrSched].[Day]
), DrEnds AS (
    SELECT 
        -1 [Drs], 
        [Dates].[Date] + [DrSched].EndTime [Timestamp]
    FROM        [DrSched]
    INNER JOIN  [Dates]
    ON          WEEKDAY([Dates]) = [DrSched].[Day]
), ApptStarts AS (
    SELECT -1 [Drs], [Date] + [Time] FROM [Appts]
), ApptEnds AS (
    SELECT -1 [Drs], DATEADD(MM,[Duration],[Date] + [Time]) FROM [Appts]
), Txns AS (
                SELECT *, 1 Priority FROM DrStarts 
    UNION ALL   SELECT *, 1 Priority FROM DrEnds 
    UNION ALL   SELECT *, 0 Priority FROM ApptStarts 
    UNION ALL   SELECT *, 0 Priority FROM ApptEnds 
)

I added priorities at the end so we can make sure the patient leaves an instant before the doctor leaves. Then you can get the balance using a windowed function like so:

, AvailDrs AS (
    SELECT 
        *,
        SUM([Drs]) OVER( ORDER BY [Timestamp] DESC, [Priority] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) [AvailDrs]
    FROM Txns 
)

Then to get the available slots, you just do:

SELECT 
    [AvailDrs].[Timestamp] [From],
    LEAD([AvailDrs].[Timestamp]) OVER(ORDER BY [AvailDrs].[Timestamp]) [To],
    [AvailDrs].[AvailDrs]
FROM AvailDrs 
WHERE [AvailDrs] > 0

Though you may want to filter that to get rid of zero-length windows because those will occur.

This is not very performant, but if you have a high volume scenario, you probably want to reconsider your database design to make this function require less transformation.

You also need to make a date table. I presume you actually have a work calendar somewhere, but if not there are myriad ways to create a date table within a dynamic start/end date so I just assume it exists here. this approach also lets you easily slot in holidays, and perhaps incorporate a dr-specific leave calendar too.

In general, a wide range of difficult SQL probnlems become much easier if you reimagine the data as account/amount/timestamp transactions. Here you don't even subdivide into accounts but you often need that concept for other puzzles.

Also, I haven't tested this exact code, so you may end up with duplicates. If that's the case you may need to global key ORDER BY tie breaker to keep everything running smooth in the windowed functions. You can add this as an identity column to both tables, or just define a CTE with a DENSE_RANK() key column and use that instead of selecting from the tables directly.