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

  • naveed.tuf

    SSC Enthusiast

    Points: 128

    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

  • jonathan.crawford

    SSCertifiable

    Points: 6352

    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: 994261

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • jonathan.crawford

    SSCertifiable

    Points: 6352

    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: 994261

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Lowell

    SSC Guru

    Points: 323357

    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: 994261

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • jonathan.crawford

    SSCertifiable

    Points: 6352

    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

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

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