CODEWITHSUNDEEP
sql-server-2008
Patrick
Patrick

Reputation: 2577

Why does this sql RANK OVER() command give me all Rank 1

In the following query, I'm attempting to rank rows by the highest computedWorstDerogLevel and then lowest RecentDerogMonths, but it's ranking them all as 1. 

SELECT 
            strTransID,
            strRateCode as PreviousHighRateCode,
            strRateStatusCode as TradelineRateCode,
            dateReported,
            datePreviousHigh,
            CASE
                WHEN 
                    strRateStatusCode IN ('2', '3', '4', '5', '7', '8', '9', 'F', 'G', 'H', 'M', 'Z', '#', '$')
                    AND
                    (
                        strRateStatusCode IN ('F', 'G', 'H', 'M', 'Z', '#', '$')
                        OR strRateStatusCode >= strRateCode 
                    )
                THEN 
                    strRateStatusCode 
                ELSE 
                    strRateCode 
            END AS WorstDerogLevel
            ,
                DateDiff(month, 
                    CASE 
                        WHEN 
                            ( 
                                dateReported >= datePreviousHigh 
                                OR datePreviousHigh is null
                            )
                            AND strRateStatusCode IN ('2', '3', '4', '5', '7', '8', '9', 'F', 'G', 'H', 'M', 'Z', '#', '$')
                            AND
                            (
                                strRateStatusCode IN ('F', 'G', 'H', 'M', 'Z', '#', '$')
                                OR strRateStatusCode >= strRateCode 
                            )
                        THEN 
                            dateReported 
                        ELSE 
                            datePreviousHigh 
                    END, 
                    dateTransDate) as RecentDerogMonths

            ,

        RANK() OVER   
        (
            PARTITION BY 
                DateDiff(month, 
                    CASE 
                        WHEN 
                            ( 
                                dateReported >= datePreviousHigh 
                                OR datePreviousHigh is null
                            )
                            AND strRateStatusCode IN ('2', '3', '4', '5', '7', '8', '9', 'F', 'G', 'H', 'M', 'Z', '#', '$')
                            AND
                            (
                                strRateStatusCode IN ('F', 'G', 'H', 'M', 'Z', '#', '$')
                                OR strRateStatusCode >= CstrRateCode 
                            )
                        THEN 
                            dateReported 
                        ELSE 
                            datePreviousHigh 
                    END, 
                    dateTransDate) 

            ORDER BY

            CASE
                WHEN 
                    strRateStatusCode IN ('2', '3', '4', '5', '7', '8', '9', 'F', 'G', 'H', 'M', 'Z', '#', '$')
                    AND
                    (
                        strRateStatusCode IN ('F', 'G', 'H', 'M', 'Z', '#', '$')
                        OR strRateStatusCode >= strRateCode 
                    )
                THEN 
                    strRateStatusCode 
                ELSE 
                    strRateCode 
            END desc,

                DateDiff(month, 
                    CASE 
                        WHEN 
                            ( 
                                dateReported >= datePreviousHigh 
                                OR datePreviousHigh is null
                            )
                            AND strRateStatusCode IN ('2', '3', '4', '5', '7', '8', '9', 'F', 'G', 'H', 'M', 'Z', '#', '$')
                            AND
                            (
                                strRateStatusCode IN ('F', 'G', 'H', 'M', 'Z', '#', '$')
                                OR strRateStatusCode >= strRateCode 
                            )
                        THEN 
                            dateReported 
                        ELSE 
                            datePreviousHigh 
                    END, 
                    dateTransDate) asc

        ) AS Rank  

     FROM 
        [Customers]

    WHERE strTransID = '279579407'

Here is my result set enter image description here

Upvotes: 0

Views: 636

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Just debug your query by adding two column PartitionField and OrderField with the expression you have on the RANK() OVER

There two cases where every row have RANK = 1

  1. each rows belong to a different partition, so you have size 1 partitions.
  2. all the rows in the partition have the same value, all are rank 1.

Upvotes: 1

Related Questions