Finding sum based on the condition in Sql Server 2008 -
i have these columns in 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 have calculate incentive person. in case of above 578. since has booked total 4 units out 3 brokers , 1 individual. broker part incentive 2500 inr per unit 3*2500 = 7500. comes discount part. see points below:
conditions:
if no discount has been given, 1% of bsp allocated incentive sales person.
if discount given booking between .1% 1%, .75% of bsp allocated incentive sales person.
if discount given booking between 1 .1% 2%, .50% of bsp allocated incentive sales person.
if discount given booking between 2% , above, .25% of bsp allocated incentive sales person.
in above tables clear 578 has booked 4 units, 2 discounts 2 without discounts.
so incentive calculated :
var incentive = total_no_of_units_booked_with_agent * 2500; // since there might possibility more 1 units can // booked sales person.no have find if there discount // applied there, if there, extract incentive each unit //and total using above condition. table shown have //since has 4 records incentive = incentive + (.75% of bsp)+ (.75%of bsp)+(1% of bsp)+(1%of bsp)
for conditional sum, use sum case statement inside enforce conditions.
select person, sum(case when discount = 0.00 0.0100 * bsp when discount <= 0.01 0.0075 * bsp when discount <= 0.02 0.0050 * bsp else 0.0025 * bsp end + case when agent <> 0 2500.0 else 0.0 end) incentive yourtable group person
Comments
Post a Comment