I currently have a query that organizes search results for volunteers that should be called for projects based on how close they live to a project the and there past attendance.
Currently doing "SELECT name, city, state, phone, prods_done, cancels, miles FROM volunteer_search WHERE project_id = 5653 ORDER BY miles ASC, cancels/(prods_done/cancels) ASC, prods_done DESC"; The results look something like this: Jim Barnes Chicago, IL 773-555-7777 2 1 11.5 Kelley Smith Cicero, IL 708-444-2121 3 0 21.6 Kim Ayers Plainfield, IL 630-888-9898 22 1 25.1 I am trying to find a way to give more weight to reliability (projects done and cancels). For example the fact that Kim Ayers has done 22 projects makes her more desirable than either Jim Barnes and Kelley Smith even though she has 1 cancel is farther from the project than both the other candidates. The desired order would be: Kim Ayers Plainfield, IL 630-888-9898 22 1 25.1 Kelley Smith Cicero, IL 708-444-2121 3 0 21.6 Jim Barnes Chicago, IL 773-555-7777 2 1 11.5 Can the query could be modified to take into account such considerations? I realize that I have not really quantified how much reliability and the number of projects done trumps distance from the project, but in the beginning I am willing to set that somewhat arbitrary and adjust it later. I realize that this may be beyond the scope of a MySQL query. If so I am grateful for any ideas on where to go for info/help. Thanks, Richard
