Something like Oracle's ntile function?

[pabloj]

Hi all, I have to compute ntile in sqlserver, my desired result should be something like:
CUST_NO SALES_1999 DECILE_1999 SALES_2000 DECILE_2000
---------- ---------- ----------- ---------- -----------
1 6 1 12 1
2 20 1 22 1
3 31 1 32 2
4 33 2 34 2
5 36 2 37 2
6 40 2 41 3
8 43 3 47 3
7 45 3 46 3
9 49 3 50 4
10 51 4 52 4
11 54 4 55 4
12 56 4 57 5
13 58 5 59 5
14 60 5 61 6
16 65 6 82 8
18 71 6 72 7
19 73 7 74 7
20 75 7 83 8
21 84 8 85 9
22 86 8 87 9
23 88 9 100 10
24 93 9
15 96 10 64 6
17 102 10 103 10

Is there a script or function available, like Oracle's ntile (I know that Yukon will feature it but ...).
Thanks in advance for your help!

Stick to your guns

 

[Connatic]

What does Oracle's ntile function do ?

if you post this, you may get more help, as i for one have no idea what it does, and therefore can't tell you what it's SQL Server equivalent is !

 

[TheJon]

IANAS (I am not a statistician), but try something like this:

declare @sales_1999_rank table(sales_id int identity(1,1), cust_no int, sales int)
declare @sales_2000_rank table(sales_id int identity(1,1), cust_no int, sales int)

insert into
  @sales_1999_rank(cust_no, sales)
select
  cust_no,
  sales_1999
from
  myTable
where
  sales_1999 is not null
order by
  sales_1999

insert into
  @sales_2000_rank(cust_no, sales)
select
  cust_no,
  sales_2000
from
  myTable
where
  sales_2000 is not null
order by
  sales_2000

declare @count_1999 decimal
declare @count_2000 decimal
select @count_1999 = count(*) from @sales_1999_rank
select @count_2000 = count(*) from @sales_2000_rank

select
  a.cust_no,
  a.sales,
  ceiling(convert(decimal,a.sales_id)*10/@count_1999),
  b.sales,
  ceiling(convert(decimal,b.sales_id)*10/@count_2000)
from
  @sales_1999_rank a
    full outer join
  @sales_2000_rank b
      on a.cust_no = b.cust_no
order by
  a.cust_no

change the *10 above to change from deciles to quartiles (*4) etc.

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]