Click here to Skip to main content
14,641,302 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi, Friends, I need help to get a solution for the below
this is about calculating points based on amount between some range like
i have a data like
Customer Amount
Customer_A  4500
Customer_B  3200
Customer_C  1500

with a predefined setting like
Type   Point  Each_Amt  From   to
Type-A   1      100       0    1000
Type-B   2      100    1001    2000
Type-C   3      100    2001    3000
Type-C   4      100    3001    and Above

i need to get the result like
Name         Amt   Points
Customer_A  4500   120
Customer_B  3200   68
Customer_C  1500   20

may be i can do this in C#, but if i get an sql query it will be better.
pls help me and thanks in advance

What I have tried:

i have no idea from where to start
Updated 24-Apr-19 20:21pm
Richard MacCutchan 13-Apr-19 9:27am
You can check the SQL documentation to see what math operations it supports.
Gerry Schmitz 13-Apr-19 9:41am
I don't think SQL does "declining balance" calculations without some procedural code or functions that starts looking like (bad) C#.
Maciej Los 14-Apr-19 14:32pm
How 4500 amount returns 120 points?
Maciej Los 15-Apr-19 8:57am
How the amount of 4500 is getting converted into 120 points?
Christian Graus 17-Apr-19 0:04am
Where do these point totals come from? Why does customer A have 120 points? How does the setting relate to either total?
Lisanas 17-Apr-19 8:46am
@Maciej Los and @Christian Graus

its like for 1st (1000/100) * 1 point= 10
then second (1000/100) *2 =20
then third (1000/100) *3 =30
balance (1500/100)* 4==60
total 120 points
im so sorry if my question is not clear.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 3

Since the points are awarded differently depending on the range you'll need to use a case like this.
SELECT  Customer
            WHEN Amount <= 1000 THEN Amount / 100
            WHEN Amount BETWEEN 1001 AND 2000 THEN (((Amount - 1000) / 100 ) * 2) + 10
            WHEN Amount BETWEEN 2001 AND 3000 THEN (((Amount - 2000) / 100 ) * 3) + 30
            WHEN Amount > 3001 THEN (((Amount - 3000) / 100 ) * 4) + 60
        END Points
FROM    MyTable
For this to work Amount needs to be a positive integer. Adjust the code as needed

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100