not sure why this is not merely a "where does not exist ()" sql query?

ie self-join subquery. for example, in this table of sequences:

seq: 1,2,3,4,6,7,8, 10.

select seq +1 from seqtable s1 where not exists (select * from seqtable s2 where s2.seq = s1.seq + 1)

I still see this as straightforward, for example, starting with a minute-level table (8 hours * 60 min = 480 rows per day , one for each minute) and a booked flag, finding free ranges in minutes could be as below. (Beginning of day and end of day logic still req)

select ApptDate, Gap_start, Gap_end
    ,Gap_End - Gap_Start as Gap_Size
from (
    select ApptDate, MinuteID + 1 as Gap_start
        ,(select min(MinuteID) - 1
            from Appointments c3
            where Booked = 'Y'
                and c3.ApptDate = c1.ApptDate
                and c3.MinuteID > c1.MinuteID) as Gap_End
    from Appointments c1
        ,Schedules s
    where c1.ApptDate = s.AppDate
        and Booked = 'N'
        and not exists (select *
        from Appointments c2
        where Booked = 'Y'
            and c2.ApptDate = c1.c3.ApptDate
            and c2.MinuteID = c1.MinuteID + 1)
) dt
where Gap_End - Gap_Start >= @RequiredFreeMinutes

My first a semester project at university was a planning/scheduling application for some imaginary transport company, and my first desktop/sql application at all. Dont remember the details, but one weakness of SQL stuck out -that it's really hard to select data that isn't there.

I'm not sure why you couldn't do this in memory. An effecient data model would cope with a large number of dentists/patients/appointments. I'd doubt it's 1GB in memory done correctly.

Let's try an example. Say 1000 clinics with on average 10 dentists, so 10,000 dentists. You want to store in memory just the numeric data required to be able to book appointments.

Per dentists you'd have their availability encoded as a set of generic repeating calendar avail or unavail. Generating data would be a bad idea. 1K for each is generous but even if you were wasteful and it was 10K it would only be 10,000 * 10K = 100MB. So this is not a concern.

There will be a large number of appointments. Overestimate but let's say for 3 years we have 300 (days) * 20 (per day) * 3 years = 18,000 appointments per dentist. We only need time and duration of each appointment as we can lookup the patient and details based on (dentistid, start). The time and duration can be 5 min granularity so the time and duration can easily be stored in an int and int arrays.

So we have 10,000 dentist * 18,000 appointments * 4 bytes ~ 700MB

In memory you'd have some object and indexing overhead but it look like you could do this easily in 1GB - 2GB. It would be about 1000 times faster than a database solution.

Thinking about it there is probably a better optimised Availability data structure that represents the dentists available slots. Would be able to go out many years also.

I'd argue this is a complexity not really needed for 'dentist appointment' system.

The important part here is that the patient rarely wants 'any timeslot, any dentist, any branch'. The reason to go to dentist is either because you have tooth ache, go for an (semi)annual check up, or you continue your treatment.

In the first case you want something as soon as possible, so the query is 'give me a timeslot within next couple of days, for any doctor'. Rinse-repeat for multiple branches. Short timeframe makes it not very compute intensive regardless of the approach you take, be it query the existing appointments and compute free slots in the app, pre-generate all slots up front, or generate all slots in memory on database side at query time and subtract booked slots.

In the case of annual check up, the timeframe is bigger, but personally I wouldn't look for anything later than 2-3 weeks from now. Still manageable in terms of compute time.

In the last case, the next visit is booked for specific doctor, but the timeframe can be much longer. Still doable by querying next 2 weeks, if that doesn't work then next 2 weeks and so on.

In fact, for small to medium clinics, the dataset would be small so pretty much anything will work and you don't need to do such querying anyway - just present the user with regular calendar, they will 'eyeball' a free slot quickly enough to warrant not spending money on developing more complex system.

For a big network of dentists with thousands of branches and tens of thousands of patients (e.g. private medical insurance company that lets you book appts with their partners via their website), I would go with:

1. pregenerating an 'all 15 min slots' table with booked/free flag and next_free_slots int, cluster indexed by date, city, branch, doctor and query that with sth like 'limit top 5 ... where next_free_slots > required_slots'. You might want to query for stuff like find 2 times 4-slots long for each day within next week, or even more complex stuff but the important thing is you're no longer querying data that isn't there, which is pretty much the clue of your post.
2. another appointments table, classical approach, for the purposes of displaying a calendar - when you don't need 'next free slot for appointment', but rather 'how does my March look like and when I can take some holiday

Oren,

seems to me that we are agreeing that once you grow to use cases like 'any dental hygienist in any clinic within reasonable commute time and larger timeframe', computing on the fly is not a sustainable option. The first part of my post was meant for 'design a system for a small clinic, maybe with a handful of branches within single city', which in fact doesn't really require to scale that much.

To render the calendar, you just need 'working time' and a list of appointments. Any holidays, short breaks etc, can be modeled as appointments too. UI akin to 'scheduling assistant' in Outlook. If the choice of dental hygienists is relatively small (say up to 10), then this could work, otherwise see second part of my post.

Regarding point 2 in the second part of my post, it wasn't really meant for scheduling purposes, but so that the dentist can have visibility. Granted you can do that by quering the pre-generated availability table too, it's just that I think having two data sources for two different use-cases might be more efficient.