Click here to Skip to main content
15,352,563 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
I am having some issue with some query

Here is example of what I want

Quote:

@QuantityEntered = 1 returns 1.50 because 1 falls in between 1 and 5 and 1.50 is added from price field for a total of 1.50
@QuantityEntered = 2 returns 3.00 because 2 falls in between 1 and 5 and 1.50 is added from price field for total of 3.00
@QuantityEntered = 3 returns 4.50 because 3 falls in between 1 and 5 and 1.50 is from price field added for total of 4.50
@QuantityEntered = 4 returns 6.00 because 4 falls in between 1 and 5 and 1.50 is from price field added for total of 6.00
@QuantityEntered = 5 returns 7.50 because 5 falls in between 1 and 5 and 1.50 is from price field added for total of 7.50
@QuantityEntered = 6 returns 8.75 because 6 falls in between 6 and 10 and 1.25 is from price field added for total of 8.75
@QuantityEntered = 7 returns 10.00 because 7 falls in between 6 and 10 and 1.25 is from price field added for total of 10.00
@QuantityEntered = 8 returns 11.25 because 8 falls in between 6 and 10 and 1.25 is from price field added for total of 11.25
@QuantityEntered = 9 returns 12.50 because 9 falls in between 6 and 10 and 1.25 is from price field added for total of 12.50
@QuantityEntered = 10 returns 13.75 because 10 falls in between 6 and 10 and 1.25 is from price field added for total of 13.75
@QuantityEntered = 11 returns 14.75 because 11 falls in between 11 and 999999999 and 1.00 is from price field added for total of 14.75
@QuantityEntered = 12 returns 15.75 because 12 falls in between 12 and 999999999 and 1.00 is from price field added for total of 15.75


but i am getting this

Quote:
@QuantityEntered = 1 returns 1.50
@QuantityEntered = 2 returns 3.00
@QuantityEntered = 3 returns 4.50
@QuantityEntered = 4 returns 6.00
@QuantityEntered = 5 returns 7.50
@QuantityEntered = 6 returns 8.75
@QuantityEntered = 7 returns 10.00
@QuantityEntered = 8 returns 11.25
@QuantityEntered = 9 returns 12.50
@QuantityEntered = 10 returns 20


What I have tried:

SQL
DECLARE @QuantityEntered int
DECLARE @ClassEntered int

SET @QuantityEntered = 1;
SET @ClassEntered = 1

--SELECT 
--  Sum(IIf(@QuantityEntered>[MaxValue],[MaxValue]*[Price],(@QuantityEntered-([MinValue]-1))*[Price])) AS RangePrice
--FROM 
--  TierPricing
--WHERE 
--  (((TierPricing.MinValue)<=@QuantityEntered) AND ((TierPricing.Class)=@ClassEntered));

--Select * from TierPricing
--WHERE 
--  (((TierPricing.MinValue)<=@QuantityEntered) AND ((TierPricing.Class)=@ClassEntered));


SELECT 
  Sum(IIf(@QuantityEntered>=TierPricing.MaxValue,TierPricing.MaxValue*TierPricing.Price,(@QuantityEntered-(TierPricing.MinValue-1))*TierPricing.Price)) AS RangePrice
FROM 
  TierPricing
WHERE 
  (((TierPricing.MinValue)<=@QuantityEntered) AND ((TierPricing.Class)=@ClassEntered));
Posted
Updated 19-Aug-16 1:09am
v2
Comments
Andy Lanng 18-Aug-16 9:11am
   
Double check your banding limits. Is it possible you have an overlap somewhere?

Also, please post a table and data script so I can recreate the problem myself

Thanks ^_^
Aless Alessio 18-Aug-16 10:18am
   
If you don't post table data, would be difficult to see where s the mistake my friend ..

1 solution

You can reformulate the problem another way.
SQL
RangePrice = @QuantityEntered + IIF(@QuantityEntered <= 10 , @QuantityEntered, 10) * 0.25 + IIF(@QuantityEntered <= 5, @QuantityEntered, 5)* 0.25
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900