Return the ceiling of a set of numbers

Posted on 2011-10-19
Last Modified: 2012-05-12
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.
Question by:klpeyton
    LVL 73

    Expert Comment

    is this the same as your previous query that was deleted?

    Author Comment

    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?
    LVL 73

    Expert Comment

    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.
    LVL 73

    Accepted Solution


    try this...

    SELECT   cat, MAX(CEIL(final_points))
        FROM (SELECT x.*,
                         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
                FROM (SELECT t.*,
                             COUNT(*) OVER () cnt,
                             COUNT(*) OVER (ORDER BY emplid ROWS UNBOUNDED PRECEDING) rollcnt
                        FROM yourtable t) x)
    GROUP BY cat

    Author Comment

    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.
    LVL 73

    Expert Comment

    change the order by

     COUNT(*) OVER (ORDER BY final_points desc ROWS UNBOUNDED PRECEDING) rollcnt

    Author Comment

    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

    Author Comment

    Sorry, i posted the last thing before I read your suggestion. I did that and it worked.

    Author Closing Comment

    Thanks for the help..Sorry for the zones confusion.
    LVL 73

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video shows how to recover a database from a user managed backup

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now