Skip to content
Advertisement

How do I modify my SQL Statement so each Project has (x) Judges and each Judge participates evenly among their population?

Question

How do I modify my SQL Statement so each Project has 4 unique judges and each Judge evenly judges the same number of projects for their assigned division and category?

Requirements

Population Project needs to be reviewed by Population Judge (x) times. x = 4

  • Each Project is grouped by Division and Category.
  • Each Judge is grouped by Division and Category.

  • Each Project must be reviewed by (x) unique Judges.

  • Each Judge should never review the same project twice.
  • Each Judge should be assigned an equal number of projects to review as the other judges in their division and category grouping.

Sample Source Judge Table

Sample Source Project Table

Oracle SQL Code

Data Output Example

Problem

The number of count(*)/projects should be distributed evenly among the Division and Category for a pool a judge. Instead, some judges are assigned to 20 projects while others are assigned to very few. I don’t know how to modify my query so each judge must be reference an equal number of times amongst the Project Division and Category grouping. I want to ensure equal participation.

Advertisement

Answer

I came up with a solution utilizing procedures in my Oracle database.

My approach was to loop through each project id. While inside my loop it would loop 1 to the number of assignments I required, in this case, 4.

  1. We needed to pull out a random judge id from a list of judges by a count of how many assignments they have already been given in ascending order. I wanted to use the lowest number of assignments first.

  2. Their assignment count if bumped +1

  3. We update the values in the final list table

  4. We loop until the number of assignments by project is given and then the next project is assessed.

    create or replace procedure random_judge_project_list as

    begin

    end;

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement