Round robin assignment with limits and ratios

• iancur

  SSC Enthusiast

  Points: 194

  More actions

  November 8, 2016 at 10:08 am

  #314770

  Ok, here is what I'm faced with. I have a number of patients that need to be assigned to doctors in a round robin fashion. The providers have a max # of members whey're willing to take and some providers should get three times as many members as some other providers. How would you do this without cursors? The basic code for round robin assignment is below. As members get assigned, some providers reach their limit and should be dropped off the list. Some members shouldn't be assigned to some providers if they're been discharged by that providers, so exclusions come into play as well in the round robin assignment.

  WITH с AS

  (

  SELECT *, ROW_NUMBER() OVER ORDER BY (memberID) AS rn

  FROM members

  ),

  s AS

  SELECT *,

  ROW_NUMBER() OVER ORDER BY (memberID) AS rn

  FROM providers

  )

  SELECT c.*, s.*

  FROM с

  JOIN s

  ON s.rn =

  (с.rn - 1) %

  (

  SELECT COUNT(*)

  FROM providers

  ) + 1

• Bill Talada

  SSChampion

  Points: 11954

  More actions

  November 8, 2016 at 10:48 am

  #1911248

  Allow me to simplify:

  I'm faced with a homework problem and don't have the patience to do it. I'd like to assign it in a round robin fashion to any gullible forum help providers. Some providers may reach their tolerance limit with me and will drop off the replies list. I shouldn't assign homework to help providers that have already discharged me. How can I exclude them?

• iancur

  SSC Enthusiast

  Points: 194

  More actions

  November 8, 2016 at 11:04 am

  #1911253

  You are correct. I need to implement a weighted round robin from pseudocode and do my homework.