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
