I have been trying to build a query to calculate the total revenue of a group of accounts. Unfortunately the grouping was done by text instead of IDs, so that was a bit of a workaround as well. I was getting results differently than expected and have been stuck figuring out why.
I have 3 tables I’m working with:
InvoiceDetails Keys: InvoiceNo, HeaderSeqNo
InvoiceHeader Keys: InvoiceNo, HeaderSeqNo
Account Keys: CustomerNo
with top10 as ( SELECT rank() over (Order by sum(ExtensionAmt) desc ) as (Rank) ,Top_Parent_Account_Name__c ,sum(ExtensionAmt) as Revenue FROM (AR_InvoiceHistoryDetail) ARD INNER JOIN AR_InvoiceHistoryHeader ARH on ARD.InvoiceNo = ARH.InvoiceNo and ARD.HeaderSeqNo = ARH.HeaderSeqNo and year(invoicedate) = '2018' INNER JOIN (Account) A2 on ARH.CustomerNo = A2.MAS_Customer_No__c group by A2.Top_Parent_Account_Name__c ) Select Top_Parent_Account_Name__c from top10
Here’s the part I don’t understand…If I run the query as is, each time I run it, my results are different. If I run the code within the cte, it is consistent & accurate every time. What is is about selecting 1 column from the CTE producing inconsistencies? Is it because I’m doing the top 10 within the CTE? Should I be getting my top 10 account families differently?