SQL RANK() Calculation

Question

I have an insert query I'm currently using in a stored procedure that works as it should. It is as follows:

insert into tblAgentRank (AgtID, RankType, Rank, TimeFrame, RankValue)
select AgtID, 8, RANK() OVER (order by SUM(ColPrem*ModeValue) DESC) as Rank, 'Y', SUM(ColPrem*ModeValue)
from tblAppsInfo
where CompanyID in (select CompanyID from tblCompanyInfo
    where DeptID = 7)
group by AgtID
order by Rank

This creates a total for each agent, and ranks them against their peers.

I need to create a similar statement that does the following calculations:

If PolicyTypeID = 4, calculate SUM(ColPrem*ModeValue) * 0.07
Else, calculate SUM((ColPrem*ModeValue) + (ExcessPrem * 0.07))
Sum these two statements together for each agent, and then do the rank on the total.

I could easily do one of those, as demonstrated by the 1st query. My mental block is stemming from needing to do it on a case by case basis based on PolicyTypeID.

Which DBMS are you using?

user330315
– user330315

2014-06-27 18:36:09 +00:00
Commented Jun 27, 2014 at 18:36 — user330315
– user330315, Commented Jun 27, 2014 at 18:36
Sorry, forgot to mention. SQL 2005.

niclake
– niclake

2014-06-27 18:57:29 +00:00
Commented Jun 27, 2014 at 18:57 — niclake
– niclake, Commented Jun 27, 2014 at 18:57

Gordon Linoff · Accepted Answer · 2014-06-27 19:50:04Z

3

I think this select statement does the calculation that you want:

select AgtID, 8,
       RANK() OVER (order by SUM(case when PolicyTypeID = 4 then ColPrem*ModeValue * 0.07
                                      else  ColPrem*ModeValue + ExcessPrem * 0.07
                                 end) as RANK2,

       SUM(case when PolicyTypeID = 4 then ColPrem*ModeValue * 0.07
                else  ColPrem*ModeValue + ExcessPrem * 0.07
           end)
from tblAppsInfo
where CompanyID in (select CompanyID from tblCompanyInfo where DeptID = 7)
group by AgtID
order by Rank;

edited Jun 27, 2014 at 19:50

answered Jun 27, 2014 at 18:35

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vulcronos · Accepted Answer · 2014-06-27 18:35:47Z

1

You just need to use a case statement inside the sum.

CASE WHEN PolicyTypeID = 4 THEN SUM(ColPrem*ModeValue) * 0.07 
ELSE SUM((ColPrem*ModeValue) + (ExcessPrem * 0.07)) END

answered Jun 27, 2014 at 18:35

Vulcronos

3,4563 gold badges19 silver badges25 bronze badges

1 Comment

niclake Over a year ago

I chose another as the answer as it was more complete, but this is also correct. Thanks!

Serpiton · Accepted Answer · 2014-06-27 19:01:11Z

1

Instead of doing everything together you can divide and conquer: create a base statement in a CTE and use it in the main query

WITH PT AS (
  SELECT AgtID
       , RankType = 8
       , RankValue4 = SUM(CASE WHEN PolicyTypeID = 4 
                               THEN ColPrem*ModeValue 
                               ELSE 0 
                          END) * 0.07
       , RankValue = SUM(CASE WHEN PolicyTypeID = 4 
                              THEN 0 
                              ELSE (ColPrem*ModeValue) + (ExcessPrem * 0.07) 
                         END)
  FROM   tblAppsInfo
  WHERE  CompanyID in (SELECT CompanyID 
                       FROM   tblCompanyInfo
                       WHERE  DeptID = 7)
  GROUP BY AgtID
)
INSERT INTO tblAgentRank (AgtID, RankType, Rank, TimeFrame, RankValue)
SELECT AgtID, RankType
     , RANK() OVER (ORDER BY RankValue4 + RankValue DESC) as Rank
     , 'Y', RankValue4 + RankValue
FROM   PT

It's a little unclear to me if the first part of the formula:

If PolicyTypeID = 4, calculate SUM(ColPrem*ModeValue) * 0.07

it's really SUM(ColPrem*ModeValue) * 0.07 or it's SUM((ColPrem*ModeValue) * 0.07), I implemented the former, to change the implementation to the second form just move the multiplication inside the CASE.

answered Jun 27, 2014 at 19:01

Serpiton

3,6843 gold badges26 silver badges35 bronze badges

1 Comment

niclake Over a year ago

This also works. And the 7% could be calculated either way in this instance, I think. Thanks for the input!

Collectives™ on Stack Overflow

SQL RANK() Calculation

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related