>Your assumptions are all pretty much correct, Dragan. It's a little more complicated than I've described here so far, with data coming from an "unavailable" table (sick, vacation, lame, lazy, etc.) and two different scheduled tables. There are actually four integer bit-wise fields, two for "unavailable" and two for "scheduled," and, of course, it all links to other records showing "scheduled for what and with who." In theory, your SUM field function should work, because, as you correctly point out, there should be no overlap. But the data can get messy <g>.
>
>I've just about concluded that a SCAN loop with BITOR() isn't so bad after all. Performance testing shows the whole routine runs in less than one tenth of a second, so users will never see any perceptible consequence of my "ugly" code.
One nice thing with scheduling is that you can never have too much data. If it's more than 1000 people, it would go into separate schedules - nobody keeps such a schedule for such a large unit in one place. So you have a few hundred people at most, times no more than a hundred days ahead times one or two events a day per person - order of magnitude of tens of thousands of active records at worst. Fox eats that before breakfast and is still hungry.
My first take at this was 12 years ago (yes, DOS 6, brand new Novell 4.10 Pentium server, 386 workstations, FPD2.6) when I had maybe 40 doctors (and at least 20 would be logged in most of the time) with any number of patients, and freestyle slot allocation (i.e. anywhere between 1 and 60 slots per hour - up to them). It worked blazingly fast even then, reporting included.