Author 
Topic 

jwlooi
Starting Member
3 Posts 
Posted  09/10/2013 : 07:02:09

tableABC itemid char(8) conditionamt money amount money
itemid conditionamt amount 04400110 100.00 28.50 04400110 200.00 27.00 04400110 300.00 25.50
I need a query to get the amount based on the conditionamt of a total purchase amt on the itemid.
Example
John have a total purchase amt of 105.00 which is more than conditionamt 100.00 and below 200 and below 300 the result is 28.50 as rebate bonus
John have a total purchase amt of 250.00 which is more than conditionamt 200.00 and below 300 the result is 27.00 as rebate bonus
John have a total purchase amt of 400.00 which is more than conditionamt 300.00 the result is 28.50 as rebate bonus
Thanks in advance for your help.


VeeranjaneyuluAnnapureddy
Posting Yak Master
India
169 Posts 
Posted  09/10/2013 : 08:29:23

declare @Amount decimal(10,2) = 301.00
select distinct ( case when @Amount >= (select Conditionamt from tableABC where Conditionamt = 100.00) and @Amount <= 200 and @Amount <= 300 then '28.50' when @Amount >= (select Conditionamt from tableABC where Conditionamt = 200.00) and @Amount < 300 Then '27.00' when @Amount > (select Conditionamt from tableABC where Conditionamt = 300.00) then '28.50' else 'NULL' end) as 'Rebate Bonus' from tableABC
veeranjaneyulu 
Edited by  VeeranjaneyuluAnnapureddy on 09/10/2013 08:36:38 


jwlooi
Starting Member
3 Posts 
Posted  09/10/2013 : 21:40:28

Hi veeranjaneyulu,
Thanks for your reply.
I do not want to fix the Rebate Bonus as the result return. I need to return the value from the amount in the tableABC.
Maybe I did not provide you enough information.
The ConditionAmt and Amount in the tableABC can be some other values based on the itemid.
Here is the sample with different values.
itemid conditionamt amount 04400110 100.00 28.50 04400110 200.00 27.00 04400110 300.00 25.50 08800220 50.00 19.00 08800220 80.00 18.00 08800220 150.00 15.00 08800220 200.00 14.00
Thanks, looi
declare @Amount decimal(10,2) = 301.00
select distinct ( case when @Amount >= (select Conditionamt from tableABC where Conditionamt = 100.00) and @Amount <= 200 and @Amount <= 300 then '28.50' when @Amount >= (select Conditionamt from tableABC where Conditionamt = 200.00) and @Amount < 300 Then '27.00' when @Amount > (select Conditionamt from tableABC where Conditionamt = 300.00) then '28.50' else 'NULL' end) as 'Rebate Bonus' from tableABC
veeranjaneyulu



jwlooi
Starting Member
3 Posts 
Posted  09/11/2013 : 21:43:31

Thanks everyone of you for trying to help.
I have already got the solutions that solved my problems.




Topic 
