Wednesday, July 27, 2016

CASE n RANK Function in T-SQL

CASE n RANK Function in T-SQL


--CASE
SELECT
     SUM(CASE WHEN Rate>0 AND Rate<10 THEN 1 ELSE NULL END) sal1,
SUM(CASE WHEN Rate>10 AND Rate<20 THEN 1 ELSE NULL END) sal2,
SUM(CASE WHEN Rate>20 AND Rate<100 THEN 1 ELSE NULL END) sal3,      
SUM(CASE WHEN Rate>100 AND Rate<10000 THEN 1 ELSE NULL END) sal4 
  FROM HumanResources.EmployeePayHistory


--RANK
SELECT  Rate,   
       RANK() OVER (ORDER BY Rate DESC) AS RankBySalary  
FROM HumanResources.EmployeePayHistory AS cmp ; 

No comments:

Post a Comment