0

I have these columns in my table 

Person      Agent     Unit        BSP            Discount

578           0      000023      32689525       0.1
578           1      000025      17589656       1
579           0      000021      32689525       0.1
579           0      000020      17589656       1
581           0      000022      32689525       0.1
583           0      000024      17589656       1
578           11     000023q     32689525       0
578           12     000025a     17589656       0

Actually I have to calculate the incentive for Person. Say in case of above 578. Since It has booked total 4 units out which 3 are with brokers and 1 is individual. So from broker part his incentive will 2500 INR per unit that is 3*2500 = 7500. Now comes the discount part. See the points below:

Conditions:

  1. If No discount has been given, than 1% of BSP will be allocated to Incentive to the Sales Person.

  2. If Discount given to booking is between .1% to 1%, than .75% of BSP will be allocated to Incentive to the Sales Person.

  3. If Discount given to booking is between 1 .1% to 2%, than .50% of BSP will be allocated to Incentive to the Sales Person.

  4. If Discount given to booking is between 2% and above, than .25% of BSP will be allocated to Incentive to the Sales Person.

In the above tables it us clear that 578 has booked 4 units, two with discounts an two without discounts.

So his incentive will be calculated as :

  var incentive = total_no_of_units_booked_with_agent * 2500;

// since there might be a possibility that more than one units can be
// booked by a sales person.No we have to find if there is any discount 
// applied there, if its there, then extract the incentive for each unit 
//and total it using the above condition. For table shown we have
//since it has 4 records

  incentive = incentive + (.75% of BSP)+ (.75%of BSP)+(1% of BSP)+(1%of BSP)
Improve this question
1
  • 1
    Can you show the desired results in addition to the word problem? Commented Aug 28, 2013 at 20:51

1 Answer 1

Reset to default
3

For a conditional sum, just use SUM with a CASE statement inside it to enforce your conditions.

SELECT
  person,
  SUM(CASE WHEN discount  = 0.00 THEN 0.0100 * bsp
           WHEN discount <= 0.01 THEN 0.0075 * bsp
           WHEN discount <= 0.02 THEN 0.0050 * bsp
                                 ELSE 0.0025 * bsp END
      +
      CASE WHEN agent <> 0       THEN 2500.0
                                 ELSE    0.0       END)  AS incentive
FROM
  yourTable
GROUP BY
  person
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.