mercredi 22 juillet 2015

Working out a percentage group

I'm essentially trying to work out which percentage value (e.g. 1 to 100) a range of numbers fall into.

For example, if I have a simple table of 500 records:

declare @temp table (id int, percentage int)

insert @temp (id)
select Number
from master..spt_values
where type = 'p' 
and number between 1 and 500

select * from @temp

Then I'd like to work out that the first 1% of the records (i.e. those with id 1 to 5) will be be percentage number 1, the second 1% of records (i.e. those with id 6 to 10) will be number 2 etc.

I'm seen that some of the more recent versions on sql server have some percentage based functions like PERCENTILE and PERCENT_RANK which I think might have been a possibility, however I'm using SQL Server 2005 so not sure if my options are limited?

Is there an easy way to do this that I'm missing?

Thanks!

Aucun commentaire:

Enregistrer un commentaire