CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Shivam Sharma
Shivam Sharma

Reputation: 107

Reward points by looking at the already created fields

I have a table here : enter image description here

I want to reward a gold and a silver(i.e a value of 1 wherever applicable,else 0 )to top 2 persons by looking at pointsRewarded field.

I already have the first table created.I want a new table with the two new fields i.e the gold and silver fields.

i want the output to be something like this: enter image description here

Please help me with the query or give me some suggestions on how to proceed.

Thanks a lot.

Upvotes: 0

Views: 86

Answers (2)

paulbarbin
paulbarbin

Reputation: 382

Make sure to order by pointsrewarded descending so first place is the highest points and not the lowest. My code is longer, but I find easier to read (personal preference).

--create table employee (employeeid int, employeename varchar(50), weeknumber int, pointsRewarded int, Hours int)
--insert into employee values (111, 'person1', 1, 400, 2)
--insert into employee values (112, 'person2', 1, 100, 10)
--insert into employee values (113, 'person3', 1, 200, 10)
--insert into employee values (111, 'person1', 2, 100, 2)
--insert into employee values (112, 'person2', 2, 50, 10)
--insert into employee values (113, 'person3', 2, 200, 10)
--insert into employee values (111, 'person1', 3, 20, 4)
--insert into employee values (112, 'person2', 3, 25, 5)
--insert into employee values (113, 'person3', 3, 100, 6)

;WITH Medals AS
(
    SELECT
        employeeid
        ,employeename
        ,weeknumber
        ,pointsRewarded
        ,hours
        ,ROW_NUMBER() OVER (PARTITION BY weeknumber ORDER BY pointsrewarded DESC) medal
    FROM
        employee
)
SELECT  
        employeeid
        ,employeename
        ,weeknumber
        ,pointsRewarded
        ,hours
        ,CASE WHEN medal = 1 THEN 1 ELSE 0 END AS gold
        ,CASE WHEN medal = 2 THEN 1 ELSE 0 END AS silver
FROM    
    Medals

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I think you want to use dense_rank() for this:

select t.*,
       (case when rnk = 1 then 1 else 0 end) as gold,
       (case when rnk = 2 then 1 else 0 end) as silver
from (select t.*,
             dense_rank() over (partition by week order by pointsrewarded) as rnk
      from t
     ) t;

dense_rank() will handle the case when there are ties. In that case, multiple "gold" and "silver" values will be assigned.

I should also note that the subquery is not necessary. You can repeat the dense_rank() in the outer query. I just think it is easier to follow the logic this way.

Upvotes: 1

Related Questions