none
Find Top x per month RRS feed

  • 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

    Monday, February 6, 2017 9:18 PM

Answers

  • 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

    • Marked as answer by mbrad Tuesday, February 7, 2017 1:36 PM
    Tuesday, February 7, 2017 1:06 PM

All replies

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

    See if that does the trick.

    Monday, February 6, 2017 9:21 PM
  • 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

    Monday, February 6, 2017 9:22 PM
  • 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.







    Monday, February 6, 2017 9:50 PM
  • 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

    • Marked as answer by mbrad Tuesday, February 7, 2017 1:36 PM
    Tuesday, February 7, 2017 1:06 PM
  • 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

    • Marked as answer by mbrad Tuesday, February 7, 2017 1:36 PM
    • Unmarked as answer by mbrad Tuesday, February 7, 2017 1:36 PM
    Tuesday, February 7, 2017 1:35 PM