Rank values based on dates (grouping)

• JeremyU

  SSChasing Mays

  Points: 640

  More actions

  October 21, 2021 at 12:35 am

  #3942657

  Hello friends,

  I need some help. I need to rank all titles for each week. Later on, I need to pull top20 titles for each week in PowerBI, however for some reason it doesn't let me to do it there (it pulls top20% only). I am trying to find a way around and group it in SQL. Does someone know how it is possible to get a column with the ranking for each week?

  SELECT

  [DMDUNIT], [STARTDATE], SUM([TOTFCST]) AS 'Total Forecast'

  FROM [BYIntegration].[SCPOMGR].[FCSTPERFSTATIC]

  GROUP BY [DMDUNIT], [STARTDATE], [TOTFCST]

   

  

• pietlinden

  SSC Guru

  Points: 63591

  More actions

  October 21, 2021 at 5:53 am

  #3942707

  Calculate week numbers for each sale record, then you can do something like

  SELECT WeekNo = t.N, ProductID, ca.Total_Sales, ca.rnk 
  FROM Tally t
  CROSS APPLY (SELECT TOP 10 ProductID, Total_Sales
                  , rnk = RANK() OVER (t.N ORDER BY Total_Sales DESC)
               FROM Sales
               WHERE WeekNo = t.N) ca
  

  Tally is just a numbers table from 1 to some number (in this case at least 10).