?
Solved

SQL Function for Price Bands

Posted on 2012-08-17
10
Medium Priority
?
898 Views
Last Modified: 2012-08-17
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
0
Comment
Question by:Graham_Forbes
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 11

Expert Comment

by:David Kroll
ID: 38304706
I don't understand a few things:

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?
0
 

Author Comment

by:Graham_Forbes
ID: 38304774
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
0
 

Author Comment

by:Graham_Forbes
ID: 38304841
see attachment for code to create and populate the table with the example above
CreateTableAndInsertData.txt
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 11

Accepted Solution

by:
David Kroll earned 2000 total points
ID: 38304918
Use this to create a function.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.GetTotalCost 
(
	@Qty int
)
RETURNS float
AS
BEGIN
  declare @tempQty int
  declare @totalprice float
  declare @curmax integer

  set @tempQty = @Qty

  set @totalprice = 0
  set @curmax = 0

  while @tempQty <= @Qty and @tempQty > 0
  begin
    select @curmax = min(maxqty) from priceband where maxqty > @curmax
    if @tempQty >= @curmax
      select @totalprice = @totalprice + (maxqty * price) from priceband where maxqty = @curmax
    else
      select @totalprice = @totalprice + (@tempQty * price) from priceband where maxqty = @curmax
    set @tempQty = @tempQty - @curmax
  end

  return @totalprice

END
GO

Open in new window


To call it:

select dbo.GetTotalCost (128)

Open in new window

0
 
LVL 1

Expert Comment

by:maggiec58
ID: 38304957
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.
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 38304972
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
0
 

Author Closing Comment

by:Graham_Forbes
ID: 38305048
perfect - thanks for this, all worked once I added the new column.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38306073
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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38306103
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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 38306119
This code is extremely useful for situations where gradual factors are applied, like for instance to calculate income taxes or in payroll.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question