Jump to content

Availibility...


ajlisowski

Recommended Posts

Hi, I have written a system that displays the users availabilityfor the day. Basically there is a user table, an assignment table and an availabilitytable.

 

The system I have works fine for a smaller data set, but I have run into problems when loading more users at once. Basically I am having trouble grabbing their schedule for the day in one query.

 

Users table, for the purposes of this question is user UserID, Name

 

assignment is AssignmentID, UserID, AppointmentID

 

appointment is AppointmentID, AppointmentDate, AppointmentTime

 

availability is AvailabilityID, UserID, Date, Time, Repeating, Cancel

 

Repeating is a bool that repeats on all days that match the same day of the week for that time.

 

Cancel is a bool that says to not execute the repeating availability for that day/time.

 

So if someone has a repeating event scheduled for every monday at 1pm, but they also have a cancel event scheduled for today at 1pm, they will be free today at 1pm.

 

 

Anyway, I want a query that returns the user information, including a string showing their schedule for every hour of the day.

 

so

 

UserID, Name,8,9,10,11,12,13,14,15,16,17,18,19

 

where each field 8-19 is a string containing either the user being unavailable for that hour, or a list of the assignments they have for that hour.

 

The only way I can think to do this off the top of my head is to make a join on the availability table and assignment table for every hour. But that seems inefficient and I would assume there is a much better way.

 

Right now I load the user availability and assignments for each user in a seperate queries. But obviously if there's 100+ users Im showing at once, thats 200+ queries for each user. Id much prefer to load in all that info in one query. Any ideas?

Link to post
Share on other sites

So, I am able to get the availability via PIVOT on the Time field twice. Once to get any repeating or scheduled unavailability and once to get any cancels. Having both Cancels and whether or not something is scheduled, I know whether or not a user is available for that time.

 

Assignments is another story. I could get the total number of assignments for that time using the same pivot plan. However, I need more info than that. I need to get actual appointment information scheduled for that hour, not just how many. Since Pivot requires an aggregate function to return values I can not return a concatination of data back.

 

I'm guessing ill have to talk to my DBA and get a function created to do a group_concat.

Link to post
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.