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, 2009 at 06:29 PM in Default

avatar image

David Travis
13 1 1 3

David - can you post up some sample data and examples of the desired output?

Oct 20, 2009 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


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, 2009 at 07:13 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Oct 19, 2009 at 06:29 PM

Seen: 3108 times

Last Updated: Oct 19, 2009 at 06:29 PM

Copyright 2016 Redgate Software. Privacy Policy