×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Percentile based on n past periods

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close