ajlisowski Posted April 9, 2012 Share Posted April 9, 2012 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? Quote Link to comment Share on other sites More sharing options...
ajlisowski Posted April 9, 2012 Author Share Posted April 9, 2012 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. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted April 12, 2012 Share Posted April 12, 2012 I think your would be best using a ROLLUP for this kind of thing Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.