0

I am new to RANK() and for the life of me I cannot get figure out what I am doing wrong. I want the sum of InvoiceAmt partitioned by year like I have already, but I also want to rank by Total_by_yr.

Below is my code the results.

SELECT c.CustNum, c.CustID, c.Name, c.Industry, YEAR(i.ApplyDate) AS YEAR,
    
    SUM(InvoiceAmt)OVER (PARTITION BY CustID ORDER BY YEAR(ApplyDate)) AS Total_by_yr,
    RANK() OVER (PARTITION BY YEAR(ApplyDate) ORDER BY SUM(InvoiceAmt)) AS rank
    
    
FROM dbo.Customer c
    join dbo.InvcHead i 
    on c.CustNum = i.CustNum

WHERE Industry in ('CN', 'RS') AND ApplyDate between '2019-07-01' AND '2022-06-30' 
GROUP BY InvoiceAmt,ApplyDate,CustID, c.CustNum, Name, Industry
ORDER BY Total_by_yr DESC

What I am Getting What I want

brad65340
  • 1
  • 1
  • Please provide your sample data and desired results as tabular text (not as images), in separate tables. Your query has two tables (`Customer` and `InvcHead`), so we should have two separate tables for input data as well. – GMB May 24 '23 at 13:12

0 Answers0