Product prices and quantity breakdown

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 2000 Forums

SQL Server Development (2000)

Product prices and quantity breakdown

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-10-06 : 08:36:59

Hi
I am building a website and I have following scenario:

The price of our products depends on the quantity a customer buys. So we have following price structure:

product1:
1-9: $10
10-24: $8
25+ : $5

product2:
1-9: $9
10-24: $7
25+ : $4

The list goes on like the above. Currently, we have 25+, but in future we could have 25-49 and 50+....

So my question is: how can this sort of price table be implemented and how can I link it to my product table.

Or, is it as simple as:
TableID, MinQuantity, MaxQuantity, Price, ProductID

kind regards

Hunglech
Starting Member

16 Posts

Posted - 2005-10-07 : 05:34:51

table p (price)
prd_id, qtyfrom, price (not qtyto needed)

table d (data)
prd_id, qty

create function getpriceqty(@prd_id char(10), @n numeric(20))
returns numeric(20) AS
begin
declare @n1 numeric(20)
select @n1 = max(qtyfrom) from p where prd_id = @prd_id and qtyfrom <= @n
return @n1
end

select d.*, p.price from d join p on d.prd_id = p.prd_id AND dbo.getpriceqty(d.prd_id, d.qty) = p.qtyfrom

Kristen
Test

22859 Posts

Posted - 2005-10-07 : 05:49:25

We do it with multiple columns in the Price table - because we only allow a finite number of price breaks (6 I think)

Also, we get asked to do things like "Offer this client 'Column Up' pricing" or "End column pricing"

I recommend that you have a function that finds the price for a product (given the Qty) and is therefore capable of being extended to cover current promotions and the like - i.e. the code is centralised in the function)

The other thing that we have to accommodate is that some customers are on a special price list (which might be "10% off everything except where there is a specific price for a product"


SELECT	[Price] = 
	CASE WHEN @order_Qty >= price_QtyBreak6 THEN stk_price_Price6
		WHEN @order_Qty >= price_QtyBreak5 THEN price_Price5
...
		WHEN @order_Qty >= price_QtyBreak1 THEN price_Price1
		ELSE NULL
		END
FROM   MyPriceTable
WHERE Price_StockCode = 'xxx'

Kristen

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-10-07 : 05:53:00

Hi
hasanali00, your proposed solution looks far preferable and is the one I would go with. This allows you to simply join to your price table and should perform well. Just be sure to guard against overlapping quantity ranges. Hunglech, your solution looks a little convoluted. If you're going to use a function (which I wouldn't advise as it will process row-by-row), why not just return the price in the function rather than using the function to return the quantity and then performing a join?

Mark

Hunglech
Starting Member

16 Posts

Posted - 2005-10-07 : 06:01:19

Yes, You are right

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-10-17 : 11:53:30

Thanks Kristen,

I was given a table like:
ProductID, StandardPrice, Qtn1, Qtn2, Price1, Price2

With data like;
Product1, 100, 5, 10, 95, 90

And Kristen, your solution has worked perfectly for me. thanks.

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-10-18 : 08:00:42

Well, I was happy that Kristen has provided the solution till I run into this problem:

ProductTable(ProductID, StandardPrice, Qtn1, Qtn2, Price1, Price2)
Values = Product1, 100, 5, 0, 95, 0

In my table if the Qnt2 & Price2 is 0 (meaning there is NO second quantity breakdown for this product), I get Price=0..no matter what the @qu is

So any idea how I can get the price even if the Qnt2 & Price2 is 0

regards

Kristen
Test

22859 Posts

Posted - 2005-10-18 : 08:10:06

What's the code that is generating the "Price" ?

Kristen

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-10-18 : 09:13:20

Its your code, modified for my table fields:

SELECT [Price] =
CASE WHEN @order_Qty >= price_QtyBreak6 THEN stk_price_Price6
WHEN @order_Qty >= price_QtyBreak5 THEN price_Price5
...
WHEN @order_Qty >= price_QtyBreak1 THEN price_Price1
ELSE StandardPrice
END
FROM MyPriceTable
WHERE Price_StockCode = 'xxx'

The only difference is: I have 'Else StandardPrice' ...instead of 'Else NULL'

Kristen
Test

22859 Posts

Posted - 2005-10-18 : 10:37:42

Need to see you actual code please - can't be sure that I'm not seeing some mismatch if you just show me my code example as-if its identical to your code!

Kristen

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-10-18 : 10:44:31

If you mean the SQL code, then here it is:

CREATE PROCEDURE spv_Products_GetCustomerPrice

@ProductID nvarchar (50),
@Quantity int,
@PriceCode nvarchar (50)

AS

SELECT CD_Curr as Currency, [Price] = CASE
WHEN @Quantity >= cd_quan10 THEN cd_sell10
WHEN @Quantity >= cd_quan9 THEN cd_sell9
WHEN @Quantity >= cd_quan8 THEN cd_sell8
WHEN @Quantity >= cd_quan7 THEN cd_sell7
WHEN @Quantity >= cd_quan6 THEN cd_sell6
WHEN @Quantity >= cd_quan5 THEN cd_sell5
WHEN @Quantity >= cd_quan4 THEN cd_sell4
WHEN @Quantity >= cd_quan3 THEN cd_sell3
WHEN @Quantity >= cd_quan2 THEN cd_sell2
WHEN @Quantity >= cd_quan1 THEN cd_sell1
ELSE CD_Sell
END
FROM web1
WHERE cd_ref = @ProductID and cd_code=@PriceCode
GO

Kind regards

Kristen
Test

22859 Posts

Posted - 2005-10-18 : 10:53:57

Looks like you need something like:


SELECT CD_Curr as Currency, [Price] = CASE 
WHEN @Quantity >= cd_quan10 AND cd_quan10 <> 0 THEN cd_sell10
...

Kristen

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-10-18 : 11:22:29

Kristen...You are the man

Actually, some explanation would do for me...why adding cd_quan10 <> 0 works

thanks

Kristen
Test

22859 Posts

Posted - 2005-10-18 : 12:14:12

You are using 0 for Price/Qty breaks that are "unused".

The CASE statement uses the first WHEN that is true.

@Quantity >= cd_quan10 is nearly always going to be true (because cd_quan10 will be 0 on most products - not many will have 10 price breaks!)

So we need to have the WHEN test check that the @Quantity is greater than the Qtybreak:

@Quantity >= cd_quan10

AND that there is a REAL Qtybreak (and not an artificial ZERO one)

AND cd_quan10 <> 0

so if that is NOT true (@Quantity is LESS than the REAL QtyBreak, or there is a ZERO QtyBreak) then try the next WHEN clause for cd_quan9 ... and so on.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources