Return the ceiling of a set of numbers

I need to create a sql stored procedure that will return a number threshold based on a certain percentage.

Data set

Emplid      FINAL_POINTS
1      100.00
2      100.00
3      82.50
4      80.00
5      50.00
6      50.00
7      50.00
8      46.70
9      45.00
10      45.00
11      40.00
12      40.00
13      40.00

Percentage :

They would like for the amount of emplids that 15% fall into Level 5, 35% into Level 4 and so on
Level 5 - 15%
Level 4 - 35%
Level 3 - 26%
Level 2 - 24%

Based on the total number of emplids (13) and the percentage's they would like it would breakdown to  this

Level 5 - 2 emplids
Level 4 - 5 emplids
Level 3 - 3 emplids
Level 2 - 3 emplids


Emplids 1 and 2 will be in Level 5, Emplids 3-7 will be in Level 4, Emplids 8-10 will be Level 3, and Emplids 11-13 will be level 2.

So from that I have figured out the thresholds will be
Level 5 - 100
Level 4 - 83
Level 3 - 47
Level 2 - 40

In other words all emplids with the final points between 83.01-100 will be level 5, 47.01-83 will be level 4, 40.01-47 will be level 3 and 0-40 will be level 2.

So the stored procedure will have the parameters will be L_5 - L_2, a person will enter for L_5 15%, for L_4 - 35% and so on, what I want my output to be is 100, 83, 47, and 40 based on just the data set of emplid and final points.

I know the syntax for a stored procedure, I just don't know how to even start the sql to make it return the output.
klpeytonAsked:
Who is Participating?
 
sdstuberCommented:

try this...


SELECT   cat, MAX(CEIL(final_points))
    FROM (SELECT x.*,
                 CASE
                     WHEN rollcnt <= CEIL(:l_5 / 100 * cnt) THEN 5
                     WHEN rollcnt <= CEIL((:l_5 + :l_4) / 100 * cnt) THEN 4
                     WHEN rollcnt <= CEIL((:l_5 + :l_4 + :l_3) / 100 * cnt) THEN 3
                     WHEN rollcnt <= CEIL((:l_5 + :l_4 + :l_3 + :l_2) / 100 * cnt) THEN 2
                 END
                     cat
            FROM (SELECT t.*,
                         COUNT(*) OVER () cnt,
                         COUNT(*) OVER (ORDER BY emplid ROWS UNBOUNDED PRECEDING) rollcnt
                    FROM yourtable t) x)
GROUP BY cat
ORDER BY cat DESC
0
 
sdstuberCommented:
is this the same as your previous query that was deleted?
0
 
klpeytonAuthor Commented:
yes, I though I had it posted in the wrong zones so i delete it and posted just to the Oracle one. The FAQ said to do that, if you post in the wrong zone. This is what it said..


If you just posted the question and there are no comments, you can delete it and try again. If there are comments, then click the Request Assistance button and ask the Moderators to move it for you.

Was I wrong to do that?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
no what you did was fine.  you original zones were fine too.

based on the "helllooooo" in your last question, I thought you deleted and recreated in order to attract attention.
given that the question was less than 2 hours old, that wouldn't be good as there are contributors all over the world so it may take a while for the question to roll around into a timezone where some people are awake.

if a question has no response after several hours you can ask a moderator to try to ping designated experts.
If the question remains idle long enough, that will happen automatically anyway.

if you delete a question too soon, it's possible somebody is already working on it and when you delete they may throw away their work as wasted.  When the new question comes in, they may not feel like redoing all of that effort.
0
 
klpeytonAuthor Commented:
Thanks sdstuber..the only issue is rollcnt..I have to order the final_points in order for them to be descending so it throws the rollcnt off.
0
 
sdstuberCommented:
change the order by


 COUNT(*) OVER (ORDER BY final_points desc ROWS UNBOUNDED PRECEDING) rollcnt
0
 
klpeytonAuthor Commented:
I understand why you did the order by emplid, but in the database the emplids are like 1739, 1734, 6683.  Not in any order, I just put 1,2,3 in order to keep the emplids anonymous
0
 
klpeytonAuthor Commented:
Sorry, i posted the last thing before I read your suggestion. I did that and it worked.
0
 
klpeytonAuthor Commented:
Thanks for the help..Sorry for the zones confusion.
0
 
sdstuberCommented:
no problem,  I see you're new to EE,  welcome aboard!

for future questions like this,  multi-zoning   in Oracle and SQL Syntax would be a good idea.

There was nothing wrong with your original selection of Oracle 10.x and PL/SQL.
But,  by going with Oracle and SQL your question will be sent to people following Oracle zones
as well as people following SQL zones.  

So, you might pickup some sql server, access, db2 or mysql people.  
It's possible they might be able to help even if they aren't particularly strong in Oracle but maybe very stong in SQL in general.

posting to oracle and pl/sql is good to give the participants information about exactly what you're looking for,  but from an "advertising" stand point,  those two zones largely hit the same people.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.