SQL RANK() Calculation

Question

我有一个插入查询，我目前在一个存储过程中使用，它应该正常工作。内容如下:

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

这将为每个代理创建一个总数，并将它们与同伴进行排名。

---

我需要创建一个类似的语句，进行以下计算:

• 如果PolicyTypeID = 4，计算SUM(ColPrem*ModeValue) * 0.07
• Else，计算SUM((ColPrem*ModeValue) + (ExcessPrem * 0.07))
• 将每个代理的这两个语句相加，然后对总数进行排序。

我可以很容易地做到其中之一，如第一个查询所示。我的思维障碍来自于需要基于PolicyTypeID逐个处理。

Answer 1

我认为这个select语句做了你想要的计算:

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;

Answer 2

您只需要在sum中使用case语句。

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

Answer 3

您可以分而治之:在CTE中创建一个基本语句并在主查询中使用

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

我有点不明白公式的第一部分:

如果PolicyTypeID = 4，计算SUM(ColPrem*ModeValue) * 0.07

它实际上是SUM(ColPrem*ModeValue) * 0.07或SUM((ColPrem*ModeValue) * 0.07)，我实现了前者，要改变实现到第二种形式，只需将乘法移动到CASE内。