14,641,302 members
Rate this:
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
Posted
Updated 24-Apr-19 20:21pm
v2
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.

Rate this:

Solution 3

Since the points are awarded differently depending on the range you'll need to use a case like this.
```SELECT  Customer
,Amount
,CASE
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