sql server 2005 - SQL RANK() Calculation -

i have insert query i'm using in stored procedure works should. follows:

insert tblagentrank (agtid, ranktype, rank, timeframe, rankvalue) select agtid, 8, rank() on (order sum(colprem*modevalue) desc) rank, 'y', sum(colprem*modevalue) tblappsinfo companyid in (select companyid tblcompanyinfo     deptid = 7) group agtid order rank 

this creates total each agent, , ranks them against peers.

---

i need create similar statement following calculations:

• if policytypeid = 4, calculate sum(colprem*modevalue) * 0.07
• else, calculate sum((colprem*modevalue) + (excessprem * 0.07))
• sum these 2 statements each agent, , rank on total.

i 1 of those, demonstrated 1st query. mental block stemming needing on case case basis based on policytypeid.

i think select statement calculation want:

select agtid, 8,        rank() on (order sum(case when policytypeid = 4 colprem*modevalue * 0.07                                       else  colprem*modevalue + excessprem * 0.07                                  end) rank2,         sum(case when policytypeid = 4 colprem*modevalue * 0.07                 else  colprem*modevalue + excessprem * 0.07            end) tblappsinfo companyid in (select companyid tblcompanyinfo deptid = 7) group agtid order rank;