I have this following query to get the primacy for an employee :
with employeeScopeFunctions as ( select e.employeeId, es.FunctionId, ef.Label, c.CompanyName ,es.SortOrder from employee e LEFT JOIN employee_scope es on es.employeeId = e.employeeId LEFT JOIN employee_function ef on es.FunctionId = ef.FunctionId LEFT JOIN Company c ON es.CompanyId = c.ID WHERE e.EmployeeId=54 ) SELECT DISTINCT esf.EmployeeId, esf.FunctionId ,STUFF((SELECT ',' + CompanyName FROM employeeScopeFunctions es WHERE esf.EmployeeId=es.EmployeeId AND esf.FunctionId=es.FunctionId FOR XML PATH('')),1,1,'') AS Companies ,SUM(esf.SortOrder) as sumOrder FROM employeeScopeFunctions esf GROUP BY esf.EmployeeId,esf.FunctionId
My output is like below :
EmployeeId FunctionId Label CompanyName sumOrder 54 39 Director C1,C2,X5 224 54 273 Group Chief Executive Officer X6,F6 66 54 897 Group Regional Chief Executive Officer VY,G7 130
What I want is to get primacy (primary,secondary, tertiary) to each function like below :
EmployeeId FunctionId Label CompanyName sumOrder primacy 54 39 Director C1,C2,X5 224 tertiary 54 273 Group Chief Executive Officer X6,F6 66 primary 54 897 Group Regional Chief Executive Officer VY,G7 130 secondary
The function having the minimal sumOrder will be the primary function and so on.
Advertisement
Answer
You can use a ROW_NUMBER()
ordered by the SUM()
result, then a CASE
to display your custom string value.
;with employeeScopeFunctions as ( SELECT e.employeeId, es.FunctionId, ef.Label, c.CompanyName, es.SortOrder FROM employee e LEFT JOIN employee_scope es on es.employeeId = e.employeeId LEFT JOIN employee_function ef on es.FunctionId = ef.FunctionId LEFT JOIN Company c ON es.CompanyId = c.ID WHERE e.EmployeeId = 54 ), RankingBySum AS ( SELECT esf.EmployeeId, esf.FunctionId, STUFF ( ( SELECT ',' + CompanyName FROM employeeScopeFunctions es WHERE esf.EmployeeId=es.EmployeeId AND esf.FunctionId=es.FunctionId FOR XML PATH('') ) , 1,1,'') AS Companies, SUM(esf.SortOrder) as sumOrder, -- Add this ranking here SumRanking = ROW_NUMBER() OVER (PARTITION BY esf.EmployeeId ORDER BY SUM(esf.SortOrder) ASC) FROM employeeScopeFunctions esf GROUP BY esf.EmployeeId, esf.FunctionId ) SELECT R.EmployeeId, R.FunctionId, R.Companies, R.sumOrder, -- Display your primacy condition here primacy = CASE R.SumRanking WHEN 1 THEN 'primary' WHEN 2 THEN 'seconday' WHEN 3 THEN 'tertiary' END FROM RankingBySum AS R