Hi @abiodunajai
1) How do I sort the ItemNo in ASCENDING order of 1-10 ?
You should not use ORDER BY NEWID()
in the ROW_NUMBER function, cause NEWID() returns random numbers.
You need to change it to ROW_NUMBER() OVER (ORDER BY SUM(a.PercentageCover) DESC) AS ItemNo
2) Since ItemNo 10(now 4) shall be the Total, I don't need a Row Number and the PolicyRefCode, how do I stop the ROW numbering and the PolicyRefCode on the 9th row so that numbers 10(now 4) and 0000002311 will not be added to the Last Row.
Try this solution:
;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='DN0000003042'
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),())
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.