SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jwlooi
Starting Member

3 Posts

Posted - 09/10/2013 :  07:02:09  Show Profile  Reply with Quote
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
164 Posts

Posted - 09/10/2013 :  08:29:23  Show Profile  Reply with Quote
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
Go to Top of Page

jwlooi
Starting Member

3 Posts

Posted - 09/10/2013 :  21:40:28  Show Profile  Reply with Quote
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
Go to Top of Page

jwlooi
Starting Member

3 Posts

Posted - 09/11/2013 :  21:43:31  Show Profile  Reply with Quote
Thanks everyone of you for trying to help.

I have already got the solutions that solved my problems.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000