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

abiodunajai 371 Reputation points
2022-11-25T16:14:43.53+00:00

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
264248-specialriskmultipleapportionment.png

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

264421-specialriskmultipleapportionment2.png

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

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,293 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,867 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} vote

Accepted answer
  1. LiHongMSFT-4306 25,646 Reputation points
    2022-11-28T02:05:40.34+00:00

    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful