johnnyg123
asked on
sql function to round to specified increment
here is some sample data from a table I have named RoundInfo (ms sql server 2008)
Floor Ceiling percent DollarIncrement
100 200 5 1
201 4001 7 5
I am trying to write a sql function that will take in a parameter that will be
an integer amount
The function will do the following:
1. determine the range the parameter value falls into (the ranges are set up so the input will definitely fall into one of them)
2 for the range determined above, add the floor and ceiling together and divide that sum by 2
3. multiply the above result by the percent
4. take the result from the result above and round based on the increment
For example,
if the parameter value passed in was 120
since 120 falls in the first range, the function would do (100 + 200)/2 = 150
then 150*.05 = 147.07
then since the round is set to nearest 1 increment the function should return a value of 8
(if the value were 7.49 the value returned should be 7)
Likewise,
if the parameter value passed in was 400
since 400 falls in the second range, the function would do (201 + 4001)/2 = 2101
then 2101*.07 = 147.07
then since the round is set to nearest 5 increment function should return a value of 145 (since 147.07 is closer to 145 than 150)
(if the value were 158 then the value returned should be 160)
Hopefully this makes sense
Floor Ceiling percent DollarIncrement
100 200 5 1
201 4001 7 5
I am trying to write a sql function that will take in a parameter that will be
an integer amount
The function will do the following:
1. determine the range the parameter value falls into (the ranges are set up so the input will definitely fall into one of them)
2 for the range determined above, add the floor and ceiling together and divide that sum by 2
3. multiply the above result by the percent
4. take the result from the result above and round based on the increment
For example,
if the parameter value passed in was 120
since 120 falls in the first range, the function would do (100 + 200)/2 = 150
then 150*.05 = 147.07
then since the round is set to nearest 1 increment the function should return a value of 8
(if the value were 7.49 the value returned should be 7)
Likewise,
if the parameter value passed in was 400
since 400 falls in the second range, the function would do (201 + 4001)/2 = 2101
then 2101*.07 = 147.07
then since the round is set to nearest 5 increment function should return a value of 145 (since 147.07 is closer to 145 than 150)
(if the value were 158 then the value returned should be 160)
Hopefully this makes sense
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER