0

I'm trying to write a query to return the overall rank and a per group rank. I'm able to get the "Overall Rank", but the Territory rank isn't correct.

SELECT        Territory, [Agent Number], [Agent Name], [Commission Level], [Profile End Date], [Prior Year Sales], [Current Year Sales YTD], [Total Sales], Rank() 
                             OVER (PARTITION BY Territory
    ORDER BY [Total Sales]) AS [Territory Rank], Rank() OVER (ORDER BY [Total Sales] DESC) AS [Overall Rank]
    FROM            (SELECT        TerritoryBase.Name AS Territory, ContactExtensionBase.ipl_AgentNumber AS [Agent Number], ContactBase.FullName AS [Agent Name], 
                                                        ContactExtensionBase.ath_activecommissionlevel AS [Commission Level], ContactExtensionBase.ath_profileenddate AS [Profile End Date], 
                                                        CASE WHEN ath_PriorYearSales IS NULL THEN '0' ELSE ath_PriorYearSales END AS [Prior Year Sales], CASE WHEN ath_SalesYTD IS NULL 
                                                        THEN '0' ELSE ath_SalesYTD END AS [Current Year Sales YTD], CASE WHEN ath_PriorYearSales IS NULL 
                                                        THEN '0' ELSE ath_PriorYearSales END + CASE WHEN ath_SalesYTD IS NULL THEN '0' ELSE ath_SalesYTD END AS [Total Sales]
                              FROM            ContactBase INNER JOIN
                                                        ContactExtensionBase ON ContactBase.ContactId = ContactExtensionBase.ContactId INNER JOIN
                                                        TerritoryBase ON ContactExtensionBase.ipl_TerritoryId = TerritoryBase.TerritoryId
                              WHERE        (NOT (ContactExtensionBase.ath_PriorYearSales IS NULL)) AND (ContactExtensionBase.ath_PriorYearSales <> 0) OR
                                                        (NOT (ContactExtensionBase.ath_PriorYearSales IS NULL)) AND (ContactExtensionBase.ath_SalesYTD <> 0) OR
                                                        (ContactExtensionBase.ath_PriorYearSales <> 0) AND (NOT (ContactExtensionBase.ath_SalesYTD IS NULL)) OR
                                                        (NOT (ContactExtensionBase.ath_SalesYTD IS NULL)) AND (ContactExtensionBase.ath_SalesYTD <> 0)) AS DerivedTable1
    ORDER BY [Overall Rank]

Here is a sample result from the above query: Sample Query Results

I know that I do not understand how to use Rank. Can someone help me figure this out?

EDIT: I tried to include what Mark suggested so my rank statements now look like this:

SELECT        Territory, [Agent Number], [Agent Name], [Commission Level], [Profile End Date], [Prior Year Sales], [Current Year Sales YTD], [Total Sales], Rank() 
                         OVER (PARTITION BY [Territory]
ORDER BY [Total Sales] Desc) AS [Territory Rank], Rank() OVER (ORDER BY [Total Sales] Desc) AS [Overall Rank]

Which produces this: enter image description here

This is close, but the territory rank still doesn't seem to work as expected. I'm not sure where number 8 and 9 or the 12 through 14 went inside the territory rank. Am I missing something with how Rank works?

mack
  • 2,715
  • 8
  • 40
  • 68

1 Answers1

1

Looks like your Territory Rank is ordering by Total Sales without the DESCENDING so it is probably backwards.

Look at Jack and Jerry. They are 'territory ranked' 352 and 353, instead of 1 and 2.

NB: depending on what you're doing, consider how you want to handle ties and note there is also DENSE_RANK, which won't skip numbers if there is a tie.

Mark Sowul
  • 10,244
  • 1
  • 45
  • 51
  • Thanks Mark, I tried to incorporate what I understood from your answer. I've updated my question with the results. – mack Aug 30 '13 at 18:04
  • Your new output doesn't show enough to diagnose this. Can you sort your excel sheet by Overall Rank (i.e. within Excel) and make sure no rows are filtered out? The only reason there should be gaps in the numbers is if there are ties (three people tied for territory rank '7' for instance). I'm concerned why there are gaps in the overall rank so I'm questioning whether the Excel sheet is showing us an accurate picture. – Mark Sowul Aug 30 '13 at 18:21
  • You're right. I sorted the spreadsheet by Overall Rank and I see some gaps. Between 1-40 I'm missing 10 numbers and I do not see any that are tied. Could it be that there are some that are tied that are not being shown at all? If there is a tie I'm expecting to see the record but the rank would be the same as the other tied records. – mack Aug 30 '13 at 19:38
  • I think you're on to something Mark. If I take just the results of the query I'm getting almost 2000 rows. The result of the report is less than 150 rows. Cross referencing the results I quickly found the missing ranks, I just can't figure out why they are missing. SSRS 2008 is being used to generate the report. I must have something messed up there. – mack Aug 30 '13 at 20:00
  • It was most definatly the report. I recreated it and went from 4 pages to 54, and I can't find a skip in rank. Thanks for your help Mark. – mack Aug 30 '13 at 20:12