Percentile based on n past periods

[MS213]

Hi

I am trying to get a SQL equivalent to Excel's percentrank.inc. Below is an example to explain. There is a rolling period of 4 days that is used to calculate the percentile. This window applies to a given agent.

AGENT QUOTE_DATE SOLD PERCENTILE
John 2021-02-19 47 100%
John 2021-02-18 16 33%
John 2021-02-17 23 33%
John 2021-02-16 33 100%
John 2021-02-15 14 0%
John 2021-02-12 23 100%
Jill 2021-02-11 11 33%
Jill 2021-02-10 9 0%
Jill 2021-02-09 43 100%
Jill 2021-02-08 23 100%

The percentile was calculated in Excel using percentrank.inc. The table was divided into two smaller tables. One for John and other for Jill. For John's Feb 19 record, use the formula PERCENTRANK.INC(L2:L5,L2) where L2:L5 is the 4 day window for past days and L2 is the value 47. For John Feb 17 use the records dated Feb 12, 15,16,17. Near the end of the table there will not be 4 days left so the formula only uses the data that is available.

How do I implement the percentile calculation in SQL?

Thank you

 

[Andrzejek]

MS213,
You may get a better response if you would state how the PERCENTILE column should be calculated.
Instead of relying on somebody here to reverse-engineering what Excel have done in percentrank.inc function

[pre]
AGENT QUOTE_DATE SOLD PERCENTILE
John 2021-02-19 47 100%
John 2021-02-18 16 33%[blue]
John 2021-02-17 23 33%
John 2021-02-16 33 100%
John 2021-02-15 14 0%
John 2021-02-12 23 100%[/blue]
Jill 2021-02-11 11 33%
Jill 2021-02-10 9 0%
Jill 2021-02-09 43 100%
Jill 2021-02-08 23 100%
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

There is a lot more useful description in MS Answers where MS213 asked the same question.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016