How Do I Use ROW_NUMBER to Generate ItemNo for Multiple Groups using GROUPING SETS

Question

Please I need your assistance.

I have a table that is grouped using the PolicyRefCode Fields attached to a Unique DebitNoteNo that encapsulates various PoliciyRefCodes for each transaction.
With the assistance of LiHong I have been able to generate an Item numbering for a single PolicyRefCode group.
However, running the Script with Multiple groups did not give me the expected result.

My Query

;WITH CTE AS  
 (  
  SELECT ROW_NUMBER()OVER(ORDER BY SUM(a.PercentageCover) DESC) AS ItemNo  
        ,a.PolicyRefCode  
        ,d.ShortDescription AS PolicyName  
        ,a.DebitNoteNo AS DebitNoteNo  
        ,b.Name As InsurerName  
        ,a.GrossAmount AS GrossAmount  
        ,SUM(a.PercentageCover) AS PercentageCover  
        ,a.InsuerLeadCode AS InsuerLeadCode  
        ,SUM(a.PremiumAmount) AS PremiumAmount  
        ,SUM(a.NAICOMLevyAmount) AS NAICOMLevyAmount  
        ,SUM(a.OverridenCommissionAmount) AS OverridenCommissionAmount  
        ,SUM(a.TotalCommission) AS TotalCommission  
        ,SUM(a.NetPremium) AS NetPremium    
  FROM Underwriting_InsurerPolicyDistribution As a   
       INNER JOIN Underwriting_Insurer_Branch As b ON a.InsBranchCode = b.InsBranchCode  
       INNER JOIN Underwriting_Policy_Code As d ON a.PolicyCode = d.PolicyCode    
  WHERE a.DebitNoteNo='DN0000003033'  
  GROUP BY a.PolicyRefCode,a.PercentageCover,a.DebitNoteNo,a.InsuerLeadCode,b.Name,a.GrossAmount,d.ShortDescription  
 )  
 SELECT COALESCE(CAST(ItemNo AS VARCHAR(20)),'')AS ItemNo  
       ,COALESCE(PolicyRefCode,'')AS PolicyRefCode  
       ,COALESCE(PolicyName,'')AS PolicyName  
       ,COALESCE(DebitNoteNo,'')AS DebitNoteNo  
       ,COALESCE(InsurerName,'TOTAL')AS InsurerName  
       ,COALESCE(GrossAmount,'0')AS GrossAmount  
       ,SUM(PercentageCover)AS PercentageCover  
       ,COALESCE(CAST(InsuerLeadCode AS VARCHAR(20)),'')AS InsuerLeadCode  
       ,SUM(PremiumAmount)AS PremiumAmount  
       ,SUM(NAICOMLevyAmount)AS NAICOMLevyAmount  
       ,SUM(OverridenCommissionAmount)AS OverridenCommissionAmount  
       ,SUM(TotalCommission)AS TotalCommission  
       ,SUM(NetPremium)AS NetPremium  
 FROM CTE  
 GROUP BY GROUPING SETS((ItemNo,PolicyRefCode,PolicyName,DebitNoteNo,InsurerName,GrossAmount,InsuerLeadCode),(PolicyRefCode))  
 ORDER BY PolicyRefCode DESC  

Result

Challenges

1. How do I generate the ItemNo(in red) per each group ( 0000000761, 0000000765) in Ascending order of (1-7(now 3-12), 1-5(1-7)) respectively?
   for as many PolicyRefCode as may be under each DebitNoteNo (DN0000003033) as shown in the picture above.
2. How do I ensure that the PercentageCover is in DESCENDING order with the highest at the top of each group?
3. How do I ensure that the PercentageCover with the InsuerLeadCode [1] takes the first row, followed by the [0] per each group even when the PercentageCover is the same (for example 25, 25 underlined in red) in the picture above?

Please note that Challenges 1-3 have been resolved as shown in the output result below.

Replacing the line of the script above with the one below will give the result below.

SELECT ROW_NUMBER()OVER(PARTITION BY PolicyRefCode ORDER BY SUM(a.PercentageCover) DESC, InsuerLeadCode DESC) AS ItemNo

please does anyone know how I can handle challenges 4 below?

4) How do I ensure that the PolicyRefCode did not show on the last Row of each group (Total as shown in the picture above)

Thank you very much in anticipation of your kind assistance.

@LiHongMSFT-4306

Answer 1

Hi @abiodunajai

4) How do I ensure that the PolicyRefCode did not show on the last Row of each group (Total as shown in the picture above)

Try changing line 23 from this ,COALESCE(PolicyRefCode,'')AS PolicyRefCode to this ,CASE WHEN ItemNo IS NULL THEN '' ELSE PolicyRefCode END AS PolicyRefCode

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.