Graham_Forbes
asked on
SQL Function for Price Bands
hello
I have a requirement to return a total price based on a table of different prices which vary by quantity.
see table
1 2 4
Start Finish price qty price*qyt
1 10 0.00 10.00 0.00
11 30 300.00 20.00 6,000.00
31 60 420.00 30.00 12,600.00
61 100 480.00 40.00 19,200.00
101 150 600.00 28.00 16,800.00
151 500 600.00 0.00 0.00
Total 54,600.00
the above exampe shows the total if I buy 128 apples.
so the first 10 are free, then 11 to 30 are priced at 300 each, next 31 to 60 at 420 etc
the price bands and price will be stored in a table and the quantity I want to buy will be between 1 and 500.
I think I need a loop to work out each band, accumulate the total price, deduct the quantity at that price and loop until the quantity is zero.
thanks for any help,
graham
I have a requirement to return a total price based on a table of different prices which vary by quantity.
see table
1 2 4
Start Finish price qty price*qyt
1 10 0.00 10.00 0.00
11 30 300.00 20.00 6,000.00
31 60 420.00 30.00 12,600.00
61 100 480.00 40.00 19,200.00
101 150 600.00 28.00 16,800.00
151 500 600.00 0.00 0.00
Total 54,600.00
the above exampe shows the total if I buy 128 apples.
so the first 10 are free, then 11 to 30 are priced at 300 each, next 31 to 60 at 420 etc
the price bands and price will be stored in a table and the quantity I want to buy will be between 1 and 500.
I think I need a loop to work out each band, accumulate the total price, deduct the quantity at that price and loop until the quantity is zero.
thanks for any help,
graham
ASKER
you always get the first 10 free, then you get the next 20 at 300 (the 20 is calculated by take the number costed so far (10) and decucting the finish band quantity (30). then the next band is priced at 420 and the quantity is quantity so far (30) and deducct the finish band quantity (60), so on until you have priced all the required quantity
thanks
thanks
ASKER
see attachment for code to create and populate the table with the example above
CreateTableAndInsertData.txt
CreateTableAndInsertData.txt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I created a table bands. It has 3 columns, ID, quantity and price. the data looks like this.
id quantity price
1 10 0.00
2 30 300.00
3 60 420.00
4 100 480.00
5 150 600.00
6 500 600.00
I then created a scalar funtion
create FUNCTION [dbo].[getTotal]
(
@quantity int
)
RETURNS money
AS
BEGIN
DECLARE @total as money
declare @top as int
declare @bottom as int
declare @curPrice as money
set @total = 0
while @quantity > 10
begin
set @top = (select min(quantity) from bands where quantity >= @quantity)
set @bottom = (select MAX(quantity) from bands where quantity < @quantity)
set @curPrice = (select Price from bands where quantity = @top)
set @total = @total + @curPrice * (@quantity - @bottom)
set @quantity = @quantity - (@quantity - @bottom)
end
RETURN @total
END
Ending the loop requires a little thought. I solved it by looping until quantity was less than 10. If you don't like that, you will have to put in a check for @bottom = null and or add a 0 record or something to prevent a null value on one of the band lines.
id quantity price
1 10 0.00
2 30 300.00
3 60 420.00
4 100 480.00
5 150 600.00
6 500 600.00
I then created a scalar funtion
create FUNCTION [dbo].[getTotal]
(
@quantity int
)
RETURNS money
AS
BEGIN
DECLARE @total as money
declare @top as int
declare @bottom as int
declare @curPrice as money
set @total = 0
while @quantity > 10
begin
set @top = (select min(quantity) from bands where quantity >= @quantity)
set @bottom = (select MAX(quantity) from bands where quantity < @quantity)
set @curPrice = (select Price from bands where quantity = @top)
set @total = @total + @curPrice * (@quantity - @bottom)
set @quantity = @quantity - (@quantity - @bottom)
end
RETURN @total
END
Ending the loop requires a little thought. I solved it by looping until quantity was less than 10. If you don't like that, you will have to put in a check for @bottom = null and or add a 0 record or something to prevent a null value on one of the band lines.
Oh, I forgot to mention that I added a field to the table called maxqty. With each band, there is a maximum quantity that can be used:
1-10 = 10
11-30 = 20
31-60 = 30
61-100 = 40
101-150 = 50
151-500 = 350
1-10 = 10
11-30 = 20
31-60 = 30
61-100 = 40
101-150 = 50
151-500 = 350
ASKER
perfect - thanks for this, all worked once I added the new column.
I know the question is answered but I still want to post a solution that doesn't use a loop. I knew this was possible with a simple select query but I didn't have time for it until now. I made some corrections to the table because it had some errors in terms of bands. The fact that I added an ID column is irrelevant, it would work without it as well. Here it is:
CREATE TABLE [dbo].[tblPriceBandLookup](
[id] int identity,
[StartBand] [int] NULL,
[FinishBand] [int] NULL,
[Price] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO tblPriceBandLookup (StartBand, FinishBand, Price) VALUES (1, 10, 0)
INSERT INTO tblPriceBandLookup (StartBand, FinishBand, Price) VALUES (11, 30, 300)
INSERT INTO tblPriceBandLookup (StartBand, FinishBand, Price) VALUES (31, 60, 420)
INSERT INTO tblPriceBandLookup (StartBand, FinishBand, Price) VALUES (61, 100, 480)
INSERT INTO tblPriceBandLookup (StartBand, FinishBand, Price) VALUES (101, 150, 600)
INSERT INTO tblPriceBandLookup (StartBand, FinishBand, Price) VALUES (151, 500, 700)
GO
declare @quant int
select @quant=47
select
sum(case when @quant>FinishBand then FinishBand-StartBand+1 else @quant-StartBand+1 end*Price) cost
from
[dbo].[tblPriceBandLookup]
where
StartBand<=@quant
Given the simplicity of the select to retrieve the cost it is not necessary a function, the query could be used like given or in a join, depending on the needs. Anyway here it is the function if preferred:
CREATE FUNCTION dbo.GetTotalCost
(
@quant int
)
RETURNS float
AS
BEGIN
return
(
select
sum(case when @quant>FinishBand then FinishBand-StartBand+1 else @quant-StartBand+1 end*Price) cost
from
[dbo].[tblPriceBandLookup]
where
StartBand<=@quant
)
END
GO
This code is extremely useful for situations where gradual factors are applied, like for instance to calculate income taxes or in payroll.
First, it seems odd that the more you buy, the more they cost. That's not the real issue here though. The thing I'm questioning is that from this example, you have 10 out of a total 10 in the first group, but then only 20 out of 30 in the second group, 30 out of 60 in the next, 40 out of 100 in the next, etc. Wouldn't you use 10/10 in the first, 30/30 in the second, 60/60 in the third and 28/100 in the fourth?