Percent Rank ()

Question

Hi Everyone

I have a SQL Server backend for my Access development. I am trying to get Percent Rank to work with a rolling window. Consider the below example

Day Value

1 10

2 11

3 17

4 12

5 27

6 11

7 19

8 10

9 15

10 12

On Day 1, to find the percent rank of 10 look at the last 4 periods. The relevant data set is {10,11,17,12}

On Day 2, to find the percent rank of 11 look at the last 4 periods. The relevant data set is {11,17,12,27}

I want to use native SQL Server commands in order to achieve maximum efficiency.

SQL Server has a Percent Rank () function that does what I want except I just can't figure out how to get the rolling window part to work. The first thing I did was get the rolling window logic to work for a simple function like SUM. The below works because it produces the correct result:

SELECT *,

SUM(PRICES) OVER (PARTITION BY SYMBOL ORDER BY QUOTE_DATE ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS CURRENTANDPREV3

FROM DBO.Stock

This tells me that the syntax to implement the rolling window feature works (ie ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) . The next step is to replace SUM with PERCENT RANK. It doesn't work.

SELECT *,

PERCENT_RANK() OVER (PARTITION BY SYMBOL ORDER BY QUOTE_DATE ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS CURRENTANDPREV3

FROM DBO.Stock

The error is "The function 'PERCENT_RANK' may not have a window frame"

Is there way a to implement a rolling window logic for PERCENT RANK? The database is about 4 million records (1.8 GB) so efficiency is super important.

Is anyone able to help me with this?

Thank you

Answer 1

You are asking "to use native SQL Server commands" in a MS Access group. Consider asking in a group specifically from SQL Server.

Answer 2

is anyone able to help me with this?