Years(Yr) AS (SELECT TOP 2000+N-1 FROM iTally) ITally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM 元 a, 元 b), WITH - Create CTE tally table with 1,000,000 rows IF OBJECT_ID('tempdb.#Sales') IS NOT NULL DROP TABLE #Sales ĬREATE TABLE #Sales - using global temp table so it's available after the proc executes IF OBJECT_ID('dbo.GenerateSalesData') IS NOT NULL DROP PROC dbo.GenerateSalesData ĬREATE PROC int, - something to int, - records per partition int - the lower this number, the more possible ties – the highest possible value for the SaleAmt column.ĭbo.GenerateSalesData creates * rows of sample data.– the number of years to generate data for beginning with 2000 and incrementing by 1.It takes four parameters and creates a global temp table named #sales with the columns: SaleID, SalesPersonID, SaleYear and SaleAmt. We have a stored procedure for sample data named dbo.GenerateSalesData. In this article we’ll examine PERCENT_RANK, how it’s calculated, then review a faster and more efficient alternative. In this case I didn’t just come up with a way to do what PERCENT_RANK does on a pre-2012 system, I developed something better than PERCENT_RANK. The great thing about helping people on forums is how you often find yourself learning more than the person(s) you are helping. With SQL Server 2012+ it’s easy because you have the PERCENT_RANK rank distribution function, but on pre-2012 systems a little more work is required. I was recently helping someone on a SQL forum calculate the percentile rank in SQL Server 2008.

Redshift rank function