CODEWITHSUNDEEP
sqlsql-server-2005
niclake
niclake

Reputation: 134

SQL RANK() Calculation

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:

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.

Upvotes: 0

Views: 80

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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;

Upvotes: 3

Serpiton
Serpiton

Reputation: 3684

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.

Upvotes: 1

Vulcronos
Vulcronos

Reputation: 3456

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

Upvotes: 1

Related Questions