x

Advanced NTile Ranking Function

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

more ▼

asked Oct 19 '09 at 06:29 PM in Default

David Travis gravatar image

David Travis
13 1 1 1

David - can you post up some sample data and examples of the desired output?
Oct 20 '09 at 08:49 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Oct 19 '09 at 07:13 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x55

asked: Oct 19 '09 at 06:29 PM

Seen: 2200 times

Last Updated: Oct 19 '09 at 06:29 PM