Advanced NTile Ranking Function

Question

Is there a way to use the NTile function (SQL Server 2005) on a subset of the records? Here's my situation. I'm pulling call center data and need to show all of the call reps. But I need to assign them into quartiles on various measures only if they meet a certain minimum of calls handled. For example, I need to show everyone's average call handle time but bucket them into quartiles only if they handled at least 10 calls. If they didn't take ten calls, they aren't part of any quartile but are included in the output. Is this possible or do I need to create a table and fill in the quartiles in a second pass where the call count >= 10?

This gives me good quartiles for all reps without regard to number of calls taken...

NTile(4) Over (Partition By Location, Call_Type 
   Order By Avg(Call_Time_Sec)) as AHT_Quartile

This one assigns the same quartiles as above but hides the value if there are less than 10 calls resulting in uneven distribution between the groups...

Case When Count(*) >= 10 Then NTile(4) Over (Partition By Location, Call_Type
   Order By Avg(Call_Time_Sec)) Else Null End as AHT_Quartile

Is there another way?

Thanks, David

Answer 1

0

Yes - you're selecting from a table - so just select from a derived table with a WHERE clause.

Instead of

SELECT SomeData FROM Table

use

SELECT SomeData FROM (SELECT SomeDataSourceColumns FROM Table WHERE Count(*) OVER (PARTITION BY Location, Call_Type) > 10) filteredData

I'm sorry that I can't give you a more relevant example - but I can't really get the feel for what your whole query looks like.

answered Oct 19 '09 at 07:13 PM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

Thanks. I have it worked out doing exactly what you suggested. Now I just need to figure out how to prevent a call center rep from being in 2 quartiles for the same measure at the same time. My function is distributing the rows evenly, but I really need it to distribute the individual reps evenly. Here's the query I'm using. RO_AHT is Avg Handle Time and is constant for each person by location and call type.

Select CCR_Phone_Login, Location, Call_Type, RO_AHT, NTile(4) Over (Partition By Location, Call_Type Order By RO_AHT) as RO_AHT_Quartile From Metrics.dbo.temp_Coaching_Detail

Oct 21 '09 at 04:29 PM David Travis

(comments are locked)

Advanced NTile Ranking Function

Follow this question

Related Questions