SELECT top 5 SUMs (one per customer) for each month in range

Question

I have a query that pulls out month/year totals for customers, and add the ntile ranking. If I were to be able to pull out the max subtotal for ntile 1, 2, 3, 4, and 5, I would ALMOST get what I'm after, but I do not know how to proceed.

For example, the result I want would look something like:

Month   Year   CustomerCode   SubTotal   ntile
1       2012   CCC            131.45     1
1       2012   CCC            342.95     2
1       2012   ELITE          643.92     3
1       2012   CCC            1454.05    4
1       2012   CCC            12971.78   5
2       2012   CCC            135.99     1
2       2012   CCI            370.47     2
2       2012   NOC            766.84     3
2       2012   ELITE          1428.26    4
2       2012   VBC            5073.20    5
3       2012   CCC            119.02     1
3       2012   CCC            323.78     2
3       2012   HUCC           759.66     3
3       2012   ELITE          1402.95    4
3       2012   CCC            7964.20    5 

EXCEPT - I would expect ranking to be different customers like for month 2, but my base query isn't giving me that result - and I obviously don't know how to get it in T-SQL on SQL SERVER 2005 - in fact I'm not sure what I'm getting.

My next option is to pull a DataTable in C# and do some gymnastics to get there, but there has to be an easier way :)

My base query is

SELECT 
i.DateOrdered
,LTRIM(STR(DATEPART(MONTH,i.DateOrdered))) AS [Month]   
,LTRIM(STR(YEAR(i.Dateordered))) AS [Year]   
,c.CustomerCode 
,SUM(i.Jobprice) AS Subtotal  
,NTILE(5) OVER(ORDER BY SUM(i.JobPrice)) AS [ntile]
FROM Invoices i 
JOIN 
Customers c 
ON i.CustomerID = c.ID 
WHERE i.DateOrdered >= '1/1/2012'
AND i.DateOrdered <= '9/30/2012' 
GROUP BY YEAR(i.DateOrdered),  MONTH(i.DateOrdered), i.DateOrdered, c.CustomerCode
ORDER BY LTRIM(STR(DATEPART(MONTH,i.DateOrdered))),   
TRIM(STR(YEAR(i.Dateordered))),     
SUM(i.JobPrice), c.CustomerCode ASC

I'd really appreciate help getting this right.

Thanks in advance

Cliff

Answer 1

If I read you correctly, what you are after is

For each month in the range,
Show 5 customers who have the greatest SUMs in that month
And against each customer, show the corresponding SUM.

In that case, this SQL Fiddle creates a sample table and runs the query that gives you the output described above. If you wanted to see what's in the created tables, just do simple SELECTs on the right panel.

The query is:

;     WITH G as -- grouped by month and customer
(
    SELECT DATEADD(D,1-DAY(i.DateOrdered),i.DateOrdered) [Month],
           c.CustomerCode,
           SUM(i.Jobprice) Subtotal
      FROM Invoices i
      JOIN Customers c ON i.CustomerID = c.ID
     WHERE i.DateOrdered >= '1/1/2012' AND i.DateOrdered <= '9/30/2012'
  GROUP BY DATEADD(D,1-DAY(i.DateOrdered),i.DateOrdered), c.CustomerCode
)
    SELECT MONTH([Month]) [Month],
           YEAR([Month]) [Year],
           CustomerCode,
           SubTotal,
           Rnk [Rank]
      FROM
(
    SELECT *, RANK() OVER (partition by [Month] order by Subtotal desc) Rnk
      FROM G
) X
     WHERE Rnk <= 5
  ORDER BY Month, Rnk

To explain, the first part (WITH block) is just a fancy way of writing a subquery, that GROUPs the data by month and Customer. The expression DATEADD(D,1-DAY(i.DateOrdered),i.DateOrdered) turns every date into the FIRST day of that month, so that the data can be easily grouped by month. The next subquery written in traditional form adds a RANK column within each month by the subtotal, which is finally SELECTed to give the top 5*.

Note that RANK allows for equal rankings, which may end up showing 6 customers for a month, if 3 of them are ranked equally at position 4. If that is not what you want, then you can change the word RANK to ROW_NUMBER which will randomly tie-break between equal Subtotals.