Find Top x per month

Question

Thanks for taking the time to read my question.

I have a table tblData with UserName, InvoiceDate and TotalData

There are several Invoice dates per month, so I need to convert InvoiceDate to YearMonth to equalize the data

I am trying to figure out how to return the top 10 highest data users per month, but all I can seem to get is the top 10 users overall...

I have looked on line a lot but am having trouble understanding what the posts say vs. what I'm working with. I think I have to do a sub query but not really sure how to apply that to my query so that I get to 10 for all my YearMonth's and not just the top 10 overall.

any help would be great.

Thanks,
Brad

Answer 1

SELECT TOP 10 FROM tblDATA Group By Month(InvoiceDate);

See if that does the trick.

Answer 2

Hi Curt,

thanks for your quick reply. That returns me the top 10 users with the most recent InvoiceDate. I'd like the top 10 users with the highest data value per Month.

Brad

Answer 3

SELECT Format(InvoiceDate,"yyyy-mm") AS Expr1, tblData.UserName, tblData.TotalData
FROM tblDATA
WHERE ((((select count(asterist here) from tblData as f
WHERE Format(f.InvoiceDate,yyyy-mm) = Format(tblData.InvoiceDate,yyyy-mm) and f.TotalData <= tblData.TotalData
))>=10)) ORDER BY Format(InvoiceDate,yyyy-mm);

It won't let me put an asterisk, so I've used a placeholder.

Answer 4

Try this:

SELECT UserName,
  YEAR(InvoiceDate) AS InvoiceYear,
  MONTH(InvoiceDate) AS InvoiceMonth,
  SUM(TotalData) AS DataUsed
FROM tblData AS T1
WHERE UserName IN
    (SELECT TOP 10 UserName
     FROM tblData AS T2
     WHERE YEAR(T2.InvoiceDate) = YEAR(T1.InvoiceDate)
     AND MONTH(T2.InvoiceDate) =MONTH(T1.InvoiceDate)
     GROUP BY UserName
     ORDER BY SUM(TotalData) DESC)
GROUP BY UserName, YEAR(InvoiceDate), MONTH(InvoiceDate)
ORDER BY YEAR(InvoiceDate), MONTH(InvoiceDate),SUM(TotalData) DESC;

---

Ken Sheridan, Stafford, England

Answer 5

Hi Ken,

Thank you so VERY much for that query. That did EXACTLY what I needed!!! I was so close but you used the fields in a different way than I did, which was the difference.

have a great day.

Brad