DheerajJain February 2016

Compare one salary with another and add comments column

I am trying to compare one salary with another and add comments column to display whose salary is higher than who.

I used self join to compare but I do not know how to include a comment column to explain whose is higher than who.

Expect something like shown below:

empid   name     salary comments
1       raj      30000  "raj sal less then dev and sunil but greater then prashant,dilip,anil"
2       prashant 24935  "prashant sal less then raj,dev,anil,sunil but greather then dilip"
3       dev      38572  
4       anil     29845  
5       sunil    48201  
6       dilip    12093  

Answers


Matt February 2016

Use the STUFF function within a CASE statement.

SELECT t1.empid, t1.name, t1.salary,
CASE WHEN t1.salary = (SELECT MIN(t3.salary) FROM yourtable t3) 
     THEN t1.name + ' has the lowest salary.'
     WHEN t1.salary = (SELECT MAX(t3.salary) FROM yourtable t3)
     THEN t1.name + ' has the highest salary.'
     ELSE t1.name +'''s salary is less than ' + STUFF((SELECT ', ' + CAST(T2.name AS VARCHAR(MAX))
     FROM yourtable T2 WHERE  T1.salary < T2.salary
     FOR XML PATH('')),1,1,'') + ' but greater than ' + STUFF((SELECT ', ' + CAST(T2.name AS VARCHAR(MAX))
     FROM yourtable T2 WHERE  T1.salary > T2.salary
     FOR XML PATH('')),1,1,'')  + '.' 
END AS Comments 
FROM yourtable t1
GROUP BY t1.empid, t1.name, t1.salary

OUTPUT

empid  name     salary  Comments
1      raj      30000   raj's salary is less than dev, sunil but greater than prashant, anil, dilip.
2      prashant 24935   prashant's salary is less than raj, dev, anil, sunil but greater than dilip.
3      dev      38572   dev's salary is less than sunil but greater than raj, prashant, anil, dilip.
4      anil     29845   anil's salary is less than raj, dev, sunil but greater than prashant, dilip.
5      sunil    48201   sunil has the highest salary.
6      dilip    12093   dilip has the lowest salary.

SQL Fiddle: http://sqlfiddle.com/#!3/2ebca/63/0

Post Status

Asked in February 2016
Viewed 3,695 times
Voted 10
Answered 1 times

Search




Leave an answer