asked on

SQL Percentage Formula

Hello experts -

I am not sure what I want to do is possible without a whole lot of code, but I am hoping it is. I am trying to figure out the lead-time statistics for a group of trips along a particular route. The business rule is this -

The lead-time statistic for a particular route = the number of days that 75% of the trips finish in (sorry for the bad grammar). So for example, if I have 4 trips

Trip 1 - 5 days
Trip 2 - 5 days
Trip 3 - 5 days
Trip 4 - 3 days

The lead-time statistics will be 5 days because 75% of the trips finished in 5 days.

So that is a very simplistic example, but I hope it communicates what I want to do. I have attached a more complex example which involves groups. In the attached example, I would like the lead-time statistic for trips to Atlanta and then the lead-time statistic for trips to Raleigh.

Thank you for any and all help!

-- Lisa
Lead-Time-Statistic.xlsx

Dustin Saunders

I misread it initially, questions--

What if there aren't any trips to make 75%? What if trips are only 50% consistent? What if there are only 2 prior data points?

HainKurt

what is logic for that 75%

Destination	Trip No.	Duration Days
Atlanta	1	5
Atlanta	2	5
Atlanta	3	5
Atlanta	4	5
Atlanta	5	6
Atlanta	6	7
Atlanta	7	8
Atlanta	8	8
Atlanta	9	8
Atlanta	10	5
Raleigh	1	4
Raleigh	2	4
Raleigh	3	4
Raleigh	4	4
Raleigh	5	5
Raleigh	6	3
Raleigh	7	3
Raleigh	8	6
Raleigh	9	8
Raleigh	10	8

Open in new window

you put sample data but no calculation here... or does not show what it should be...

HainKurt

you should clearly explain how to find what you need...

or give lots of examples :) so we can derive a formula/logic...

Dustin Saunders

I think I realized what you want which is a statistical mode. That should give you the value that appears most frequently. Something like this:

SELECT destination,duration
FROM (SELECT destination, duration, count(*) AS cnt, row_number() over (partition by destination order by count(*) desc) as rn
	FROM trip
	GROUP BY destination,duration) t
	WHERE rn = 1

Open in new window

Will give you the most frequent occurrence.

ASKER CERTIFIED SOLUTION

Dustin Saunders

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lisa Callahan

ASKER

OK, I just talked to the powers that be and it looks like we just use a percentile as the calculation. I just need to order the results and then pick the one closest to 75%. It may not be a text-book definition of lead-time standard, but it is how we currently calculate it so I am going to go with that. And I already have a formula for percentile that I used for a previous report.

So thank you all for your quick responses but I think I got this one.

-- Lisa

Lisa Callahan

ASKER

Thanks, I will definitely use this!