I had need of calculating metrics across an industry and then returning the quartile in which each company scored. So, i wrote up a function that took in the MinValue, MaxValue, and CompanyMetricValue as inputs and returned the quartile for the Company. Great. Then, when asking  someone else about this using this new function, the response was, “Why not just use NTILE?” Ug.

Here is the description from SQL 2008 Books Online:

Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row,  NTILE returns the number of the group to which the row belongs.

NTILE (integer_expression)    OVER ( [ <partition_by_clause> ] < order_by_clause > )

So, using NTILE, I was able to do the following:

SELECT NTILE(4) OVER(ORDER BY CompanyMetricValue ASC) AS Quartile

In the example above, i want quarters, so I use 4 as the (integer expression).

Lesson Learned: Before inventing a new wheel, check Books Online for wheels that already exist. 🙂