I am trying to create a query for use in a report. The query pulls the results of a survey over several hundred scores. My goal is to display the names of the categories that hold the lowest scores (and in the case of ties display those too, meaning in some cases there could be 11 entries, but always at least two). This will make sense with the example below. Here is the query I have created that I need to change.SELECT 'Center' = cen.Centername, 'Subsidiary' = sub.Subsidiaryname, 'Manager' = su.managername, 'Supervisor' = su.supervisorname, 'Language Code' = [tok].[language], 'Planning and Execution' = CAST(res.skills_planningexecution AS FLOAT), 'Time Management' = CAST(res.skills_timemanagement AS FLOAT), 'Continuous Improvement' = CAST(res.skills_continuousimprovemen AS FLOAT), 'Interpersonal Skills' = CAST(res.skills_interpersonal AS FLOAT), 'Communicaiton Skills' = CAST(res.skills_communication AS FLOAT), 'Leadership Skills' = CAST(res.skills_leadership AS FLOAT), 'Integrity' = CAST(res.culture_integrity AS FLOAT), 'Respect' = CAST(res.culture_respect AS FLOAT), 'Professionalism' = CAST(res.culture_professionalism AS FLOAT), 'Innovation' = CAST(res.culture_innovation AS FLOAT), 'Commitment' = CAST(res.culture_commitment AS FLOAT)FROM pdpadmin_dev..supervisor su INNER JOIN pdp..vw_results_67732 res on su.supervisorid = res.supervisorid and res.questionid=49 INNER JOIN TPReports..info_center cen on su.center = cen.center INNER JOIN TPReports..info_subsidiary sub on cen.subsidiaryid = sub.subsidiaryid INNER JOIN PDP..tokens_67732 tok on res.token = tok.tokenORDER BY sub.Subsidiaryname, cen.Centername, manager, supervisor
That gets a result set that looks something like this (abbreviated for space):Name Planning and Execution Time Management Continuous Improvement---------------------------------------------------------------------Bob 5 3 2Sam 4 4 4
So my goal is to add a column to display the two loweset categories for Bob ("Continuous Improvement" and "Time Management"). In the case of Sam it would need to display all categories since they are tied.So my ultimate goal would be something like this:Name Plan & Exec Time Mngt Cont Improve Lowest Category---------------------------------------------------------------------Bob 5 3 2 Cont Improv, Time MngtSam 4 4 4 Plan & Exec, Time Mngt, Cont Improve
I hope that makes sense. I can't figure out how to display it all. Thanks for your help!Craig Greenwood
I think I need a derived table but I'm not sure
Posted - 2010-05-26 : 12:39:19