0

I want to find out the top 3 spending from each quarter. My current code is printing all the spending from each quarter but i want to show only the top 3 from each quarter.

SELECT SUM(f.Spend) AS Total_Spend, e.expenset_id,e.[Expense Type],
ROW_NUMBER() OVER(PARTITION BY e.expenset_id ORDER BY SUM(f.Spend) DESC) Rank,
CASE
    WHEN DATEPART(mm,f.Date) IN (4,5,6) THEN 'Q2'
    WHEN DATEPART(mm,f.Date) IN (7,8,9) THEN 'Q3'
    WHEN DATEPART(mm,f.Date) IN (10,11,12) THEN 'Q4'
    ELSE 'Q1'
    END AS Quarter
FROM fact_tbl f
JOIN expense_type e ON f.expenset_id=e.expenset_id
GROUP BY e.[Expense Type],e.expenset_id,DATEPART(mm,f.Date)
ORDER BY SUM(f.Spend) DESC
Thom A
  • 88,727
  • 11
  • 45
  • 75
Umar Usman
  • 51
  • 6
  • I have tried but no success. – Umar Usman Jan 03 '23 at 12:37
  • So, did you get an error, or something? Why didn't the above work, when (at least from your description) it seems to be exactly what you need. – Thom A Jan 03 '23 at 12:39
  • So i am getting all the results from every quarter. But i want to print only top 3 from every quarter. – Umar Usman Jan 03 '23 at 12:43
  • Sounds like you haven't implemented the solution I linked correctly then. Perhaps your `PARTITION BY` clause is wrong, or you didn't put the proper `WHERE` (the latter seems likely as you state you got "every" row). [Edit] your question to show the attempt with the Q&A solution I linked, and include sample data and expected results (in a **consumable** format *not* an image)./ – Thom A Jan 03 '23 at 12:47

0 Answers0