SQL query update the ranking based on the percentage allocation

Question

Total number of employee: 10

Ranking to be allocated: O , E, G

Percent to be allocated for each Rank: 20%, 40% , 40%

(Example:

1. total emp * 20/100 
 select 10 * 20./100 = 2
 First 2 employees rank to be 'O' 
 2. balance emp count * 40./100 
 select 8 * 40./100 = 3
 Next 3 employees Rank to be 'E'
3.  Balance 5 Employees Rank to be 'G'

We need to allocated the Rank from descending order of the employees Score

Create Table Ranking(Rank nvarchar,percentage int)
insert into Ranking values('O',20) 
insert into Ranking values('E',40)
insert into Ranking values('G',40)

Create Table Emp(Empcode nvarchar(3),Score numeric,Ranking nvarchar)  
insert into Emp values('E1',97,null) 
insert into Emp values('E2',95,null) 
insert into Emp values('E3',87,null) 
insert into Emp Values('E4',85,null) 
insert into Emp Values('E5',78,null) 
insert into Emp Values('E6',75,null) 
insert into Emp Values('E7',68,null) 
insert into Emp Values('E8',65,null) 
insert into Emp Values('E9',59,null) 
insert into Emp Values('E10',58,null) 

ranking should be allcoated based on the percentage available in the Ranking table

now E1 and E2 employees fall in the rank 'O' E3,E4,E5 employees fall in the rank 'E' E6,E7,E8,E9,E10 fall in the rank 'G'

Answer 1

How about running three update queries together to accomplish your goal? This solution take into account that you will have more employees in the future and that you will the percentage for each rank:

update #Emp set Ranking = 'O'
where Empcode in
(
    select top(((select count('x') from #Emp where Ranking is null) * (select (percentage) from #Ranking where Rank = 'O')) / 100) Empcode
    from #Emp
    where Ranking is null
    order by Score desc
);

update #Emp set Ranking = 'E'
where Empcode in
(
    select top(((select count('x') from #Emp where Ranking is null) * (select (percentage) from #Ranking where Rank = 'E')) / 100) Empcode
    from #Emp
    where Ranking is null
    order by Score desc
);

update #Emp set Ranking = 'G'
where Ranking is null;

It's hard to come up with a clean update query given your requirements. Most of the time I try to avoid using a cursor; but to accommodate the growing list of Rankings in the future I have to use a cursor. In order to make the cursor work, I have to add a column [id] to the table Ranking:

Create Table #Ranking(id int, Rank nvarchar,Percentage int)
insert into #Ranking values(1, 'O',20) 
insert into #Ranking values(2, 'E',40)
insert into #Ranking values(3, 'G',40)

Create Table #Emp(Empcode nvarchar(3),Score numeric,Ranking nvarchar)  
insert into #Emp values('E1',97,null) 
insert into #Emp values('E2',95,null) 
insert into #Emp values('E3',87,null) 
insert into #Emp Values('E4',85,null) 
insert into #Emp Values('E5',78,null) 
insert into #Emp Values('E6',75,null) 
insert into #Emp Values('E7',68,null) 
insert into #Emp Values('E8',65,null) 
insert into #Emp Values('E9',59,null) 
insert into #Emp Values('E10',58,null)

Here is the rewritten update query using a cursor:

declare Ranking_Cursor cursor for
    select Rank, percentage
    from #Ranking
    order by id asc

declare @Rank as nvarchar
declare @Percentage as int

open Ranking_Cursor
fetch next from Ranking_Cursor into @Rank, @Percentage
while @@fetch_status = 0
begin
    update #Emp set Ranking = @Rank
    where Empcode in
    (
        select top(((select count('x') from #Emp where Ranking is null) * @Percentage) / 100) Empcode
        from #Emp
        where Ranking is null
        order by Score desc
    )

    fetch next from Ranking_Cursor into @Rank, @Percentage
end
close Ranking_Cursor
deallocate Ranking_Cursor

--catch-all query for the bottom rank
update #Emp set Ranking = @Rank
where Ranking is null;

Not pretty but it should work if you plan to add more records into the table Ranking. I hope someone else can come up with the cleaner update query.

Answer 2

Using the NTILE function should give you exactly what you need. How's this?

SELECT Empcode
     , Score
     , Ranking = CASE WHEN Band BETWEEN 1 AND 2 THEN 'O'
                  WHEN Band BETWEEN 3 and 6 THEN 'E'
                  WHEN Band > 6 THEN 'G'
                  ELSE '' END
 FROM (SELECT *, Band = NTILE(10) over (order by score DESC)
      FROM EMP) qq