[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

Return the ceiling of a set of numbers

Posted on 2011-10-19
Medium Priority
309 Views
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.
0
Question by:klpeyton
• 5
• 5

LVL 74

Expert Comment

ID: 36994669
is this the same as your previous query that was deleted?
0

Author Comment

ID: 36994764
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

LVL 74

Expert Comment

ID: 36994805
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

LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 36994847

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

Author Comment

ID: 36994949
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

LVL 74

Expert Comment

ID: 36994971
change the order by

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

Author Comment

ID: 36994999
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

Author Comment

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

Author Closing Comment

ID: 36995022
Thanks for the help..Sorry for the zones confusion.
0

LVL 74

Expert Comment

ID: 36995134
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

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logicalā¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses
Course of the Month18 days, 21 hours left to enroll