×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Percentile based on n past periods

## Percentile based on n past periods

(OP)

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

### RE: Percentile based on n past periods

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

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%


---- Andy

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

### RE: Percentile based on n past periods

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!