基于SQL中两个范围之间的数量的点计算 [英] Point calculation based on amount between two range in SQL

问题描述

朋友们，我需要帮助来获得以下的解决方案

这是关于根据某些范围之间的金额计算点数，例如

i有一个像<的数据br $>

客户金额
 Customer_A 4500 
 Customer_B 3200 
 Customer_C 1500 

with a预定义设置，例如

类型Point Each_Amt From 
 Type-A 1 100 0 1000 
 Type-B 2 100 1001 2000 
 Type- C 3 100 2001 3000 
 Type-C 4 100 3001及以上

i需要得到的结果如

姓名Amt积分
 Customer_A 4500 120 
 Customer_B 3200 68 
 Customer_C 1500 20 

可能是我可以在C＃中执行此操作，但如果我得到一个SQL查询它会更好。

请帮助我并提前谢谢



我尝试过什么：



i不知道从哪里开始

解决方案

< blockquote>由于这些点的授予方式不同，因此你需要使用这样的情况。

  SELECT 客户
，金额
， CASE  
  WHEN 金额< =  1000  那么金额/  100  
  WHEN 金额 BETWEEN   1001   AND   2000  那么（（（金额 -   1000 ）/  100 ）*  2 ）+  10  
  WHEN 金额 BETWEEN   2001   AND   3000  那么（（（金额 -   2000 ）/  100 ）*  3 ）+  30  
  WHEN 金额>  3001  那么（（（金额 -   3000  ）/  100 ）*  4 ）+  60  
  END 积分
  FROM  MyTable 

为此工作量需要是一个正整数。根据需要调整代码

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

解决方案

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

这篇关于基于SQL中两个范围之间的数量的点计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！