Dynamic Ranking - is it possible to create the SQL code?

• Tamrak

  SSC Eights!

  Points: 902

  More actions

  March 31, 2017 at 4:14 pm

  #323435

  I have the SQL code already written and generate the results like we see in the photo:

  1. We have two work types, admin and Project.
  2. We have a selectable week number. This is equivalent to the IN clause.  (Where weekNumber in 1, 2, 3...)
  3. The yellow highlighted column is the total hours per work type.
  4. The HoursCombined column is the result for the activity for week 1, 2, 3 combined.

  Challenges:

  1. I need to do the ranking based on the Total hours combined, in each WorkType.
  2. Even though I am looking now, I might have Employee Management as Ranking # 1 because it has the most hours on three weeks combined, 43.5 hours.
  3. However, if I select only week # 2, the Ranking will be changed. The Employee Time Off will become the first ranking with the most hours, 24 hours.'
  4. I tried to come up and experiment with SUM OVER() (PARTITION BY) and I had no lucks. Sometimes, I got an error like 'Windowed functions can only appear in the SELECT or ORDER BY clauses.'
  5. The ranking will be based on the 'Work Type'. Admin and Project.
  5. The WeekNumber is a required parameter that will allow the user to select the weeks number.  This should be done in the where clause.
  6. Just want to know whether it is possible to do some types of dynamic ranking in the MS SQL code.  I use both 2008 and 2012 versions.

  Thanks.,

  

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  March 31, 2017 at 6:43 pm

  #1936097

  look at RANK() and DENSE_RANK() windowing functions