Allocate Meetings to Employees (randomly generated) with daily maximum hours set at 5

Question

Allocate Meetings to Employees (randomly generated) with daily maximum hours set at 5

naveed.tuf

SSC Veteran

Points: 268
More actions
March 19, 2019 at 7:19 am

#377776

Hi all,
I am currently working on a project where I need to allocate meetings to employees (randomly generated, E0,E1,E2,E3,E4,E5, F0, F1, F2) but to keep the daily hours to a maximum of 5 hours
I have the following table:
"MeetingType" "Meetingdate" "MeetingTime (duration in minutes)" "rnd(0 to 6 generated randomly)" "Dept" "Employee (Concatenate rnd&Dept)"
Any help would be greatly appreciated
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 1

Might I suggest that nobody is looking for true "random assignment", they want it to be balanced assignment, meaning everyone gets a turn. So, round robin kind of thing. Random could still mean that one person gets a ton of meetings while someone else gets hardly any. Might I also suggest that if meetings are so unimportant you don't care who goes to them, maybe just stop going?

assign based on the MIN(SUM(meeting time)), order by employeeID so that if there's a tie you break it, grouped by day, having SUM(meeting time) < (300 - current meeting length)

still unfair when you start over on the next day, because employee1 gets a meeting every time. so, maybe you need to look at the sum(meeting time) for the last 30 days and assign based on the minimum value of that or something.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

jonathan.crawford - Tuesday, March 19, 2019 8:22 AM
Might I suggest that nobody is looking for true "random assignment", they want it to be balanced assignment, meaning everyone gets a turn. So, round robin kind of thing. Random could still mean that one person gets a ton of meetings while someone else gets hardly any. Might I also suggest that if meetings are so unimportant you don't care who goes to them, maybe just stop going?
assign based on the MIN(SUM(meeting time)), order by employeeID so that if there's a tie you break it, grouped by day, having SUM(meeting time) < (300 - current meeting length)
still unfair when you start over on the next day, because employee1 gets a meeting every time. so, maybe you need to look at the sum(meeting time) for the last 30 days and assign based on the minimum value of that or something.

Just use NTILE against an unsorted list of meetings for each day and you're done.The limits would be calculated for each day by the number of people available times the number of time slots.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 3

Jeff Moden - Tuesday, March 19, 2019 8:42 AM
Just use NTILE against an unsorted list of meetings for each day and you're done.The limits would be calculated for each day by the number of people available times the number of time slots.

What about the 5 hr limit? tell em to suck it up if it runs over, because there are too many meetings?

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

jonathan.crawford - Tuesday, March 19, 2019 8:46 AM
Jeff Moden - Tuesday, March 19, 2019 8:42 AM
Just use NTILE against an unsorted list of meetings for each day and you're done.The limits would be calculated for each day by the number of people available times the number of time slots.
What about the 5 hr limit? tell em to suck it up if it runs over, because there are too many meetings?

Heh... when we see some readily consumable test data, we may be able to come up with the answer to that. 😉

If it were me, I'd have a "extra employee" (always just one more than the number of employees available for the day) and use it as an "overflow" for meetings that are too much for the set of employees to handle. If the meetings aren't scheduled by whatever system makes the "random" assignments, then that's the best you can do is report that someone just tried to put 4 pounds of poop in a 3 pound bag. Hopefully, the system scheduling the meetings is a bit more intelligent than that.

There's also going to be a huge scheduling problem if the "MeetingTime (duration in minutes)" isn't some consistent value across all meetings.

This is one of those things where I'd tell people, "Use Outlook or something similar to schedule your meetings". 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 5

is there an assumption that there can only be one meeting for a given hour?
i would expect you need additional login where if there are two or more meetings at 1pm, then those meetings cannot be assigned to the same person, right?

sample data would give us an interesting problem

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

And... it would help tremendously if the sample data were "readily consumable". Please see the article at the first link in my signature line below for way way to provide "readily consumable sample data".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 7

Lowell - Thursday, March 21, 2019 7:24 AM
is there an assumption that there can only be one meeting for a given hour?
i would expect you need additional login where if there are two or more meetings at 1pm, then those meetings cannot be assigned to the same person, right?
sample data would give us an interesting problem

another reason for "round robin" instead of randomized assignment. you might also want to check for preference about back to back meetings instead of turning their schedule into swiss cheese. I'd much prefer having a block of meetings and the rest of the day available for work.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses