The solution here ultimately depends on how this business functions and how these available appointment slots are structured. Not just in a ‘best case’ scenario, but also providing users with the ability to customize the output/availability of appointments.
So, to start you have an ‘appointment’ custom post-type, this you’ve already built. This appointment CPT entries however are created not by back-end users, but rather by users who are booking an appointment on the front end. What you need is a way for backend users/admins/editors to create ‘appointment slots’ that can be selected by front end users when they are booking an appointment. (And thus creating an entry for your appointment CPT.)
My approach would be to make a second custom post-type where you set up ‘available’ slots for a period of time. Again, this will vary depending on the type of business and how it operates.
For example, you mentioned there are multiple offices, so my first thought would be to build the ‘availability’ CPT to work on a weekly basis. Then, I would make ‘location’ an option (maybe as a taxonomy or as post meta) and then I would provide a ‘start date’ and ‘end date’ field.
So let’s say we’re making an availability custom post-type for Monday, July 24th to Friday, July 28th for OFFICE NORTH. My next step is to set the start and end hours. So OFFICE NORTH is open, that week, from 9:00am to 5:00pm. My next step is then to set ‘duration of appointments’. Is each appointment 15 minutes, 30 minutes, 45 minutes? Let’s say they’re 30 minute blocks for the sake of this exercise.
Once all of these fields exist and the data has been input, you ‘Save/Publish/Update’ the availability post. From there I would calculate the available appointment slots and record them into their own custom database table. I would index the date and location values. I would also then keep the times separate and in a serialized array.
The appt_times serialized array would be key/value pairs.
9:00am => 0 would indicate unbooked.
9:30am => 1 would indicate that the time slot is booked.
Once you have this basic outline set-up, then you can start adding in customization options. I would, personally, once the data set for available slots was built, then list all of the appointment times for that week in a list in the WP backend (in the edit screen for that specific availability entry) and I’d add a checkbox next to each one so that admins/editors can manually block off entries they don’t want booked. Let’s say on Thursday July 27th Head Office is sending a Regional Manager to OFFICE NORTH to have a meeting with the staff from 1:00pm to 3:00pm. You’d want to give the person setting up the availability slots the option of ‘blocking off’ these times:
1:00pm, 1:30pm, 2:00pm, 2:30pm
I reiterate, depending on how this business operates you may be better suited to building these availability records as a ‘day to day’ basis, or perhaps monthly. (Would NOT recommend making every appointment slot as an individual record as that will just bloat the database and make creating the time slots available too cumbersome and time consuming. We want to automate as much of this as possible.)
Once this data is in place, you would then use AJAX to populate the dropdown of available appointment slots. So a front end user/customer goes to book an appointment, they select a date and location and then AJAX retrieves the custom DB record for the date/location combo.
It parses through the data and locates every X:XXxm => 0 record and outputs an option in the dropdown for it.
From there, you do with the data what you need to create your appointment CPT entry.
Finally, as I mentioned in my original comment, I would then write a CRON task that will take today’s date and check through the ‘Availability’ post-types and delete/unpublish any whose ‘end date’ is older than today and then also purge the custom DB table of times using the same logic. Because we aren’t going to have people booking appointments in the past, so lets clear out that data and keep things leaner.
You STILL retain the past appointments though, because they’re a separate post-type, so that’s good for record keeping and reference, but what you’ve done is cleared out the ‘slots’ that are no longer usable.
Hope that helps.