SQL Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query Help

Author

Topic

jwlooi
Starting Member

3 Posts

Posted - 2013-09-10 : 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

169 Posts

Posted - 2013-09-10 : 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

jwlooi
Starting Member

3 Posts

Posted - 2013-09-10 : 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 - 2013-09-11 : 21:43:31

Thanks everyone of you for trying to help.

I have already got the solutions that solved my problems.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources