Alan.B (5/29/2015)

---

Sean Lange (5/29/2015)

---

sarath.tata (5/29/2015)

---

Sean Lange (5/29/2015)

---

sarath.tata (5/29/2015)

---

Sean Lange (5/29/2015)

---

Your question is not at all clear. What would be helpful is if you could post ddl and sample data in addition to the desired output. I think in this case you are going to need to explain the business rules too because they don't make sense to me. Please take a few minutes and read the first article in my signature for best practices when posting questions.

Thanks Sean for your comment. I have rephrased my original post now.

Cool we now have nice data to work with and what you expect for output. However, I can't make heads or tails of how you come up with the output. There is just a random list of dates and a random list of people with departments. If you can explain the logic I can help you with the query.

well, it is not random! We need to prepare a roster with the available persons in each department. For example, you and me working in the same department; You will be on the roster for the first week, me on the second week, you on the third week, me on the fourth week and so on. It rotates between two of us in our department.

May be you can sort by Dept and Name in the output, you would get a clear picture of rotation.

To you it isn't random. But from where I sit there is no rhyme or reason from the limited data. Why is person A-P1 assigned to work in the first time slot? And why are there also people from each department in that time slot? I think the problem is that you have simplified the logic so much to post it that I can't see the details of what you are trying to do.

Is this simply assign the first person from each department to each time slot and keep the rotation so that it cycles through the list? I guess for ordering we can just use Person?

Sean - I have not had a chance to look at the new data but take a look at what I posted. I think I understand what the OP wants (though they're not explaining it clearly). The method I used to get him his results is obviously not very scientific but it get's them the distribution they're looking for. E.g. 6 orders, 2 people in dept A, 1/2 the orders are going to person 1, the other 1/2 to person 2. 3 people in dept B - 1/3 of the orders going to each. 4 people in the group C, orders divided as evenly as possible with the the orders divided as evenly as possible (kinda NTILE-esk)... If you added a row-number over partition by each group it would replace how I'm using the RIGHT() function to get the number (I just used that because it worked).

Long story-short, dividing the 6 orders as evenly possible to each member of each group (dept). I could be totally wrong (would not be the first time), just guessing really - but it's kind of a fun little problem if I understand it correctly. I want to come back to this but I'm late to a meeting :hehe: