Complex Price List

I need help with an odd request. I have been asked to create a view
price list that includes the price each customer will pay for any
product. ProductID, customerID, and price is all I really need. Our
rules for figuring out who pays what, is somewhat complex, and I would
prefer to avoid cursoring throught what will be millions of records if
possible. In order of presidence, if there is a specific customer
price we allways use that number. Here is what the tables look like:
Customers
CustomerID CustomerName PriceListID ...
27 Acme, Inc. 1
35 Bonzi, LLC 1
41 Chuck Corp. 2
Products
ProductID ProductName ...
10 Widget
11 Thingy
12 Watchamacallit
CustPrice
CustomerID ProductID Price
27 10 2.99
41 11 10.00
35 12 30.00
If that was the end of the story my view would match the CustPrice
table, but there is a pricelist that we use if there is no special
pricing for the customer. If the price list does include the productid
in question, we use the Retail price, if the Retail price also does not
include the product then set the price to $1000 because it is a special
negotable item. This is how the product pricing tables look:
PriceLists
PriceListID PriceListName
1 Retail
2 Preferred Customer
PriceListItems
PriceListID ProductID Price
1 10 7.99
1 11 15.00
2 10 6.99
I have added a discription to show where the price came from.What my
veiw should look like in the end is this:
CustomerID ProductID Price
27 10 2.99 --Customer price
27 11 15.00 --retail
pricelist
27 12 1,000.00 --Default price
35 10 7.99 --retail
pricelist
35 11 15.00 --retail
pricelist
35 12 30.00 --Customer price
41 10 7.99 --retail
pricelist
(used because there no
preferred pricelist for the item)
41 11 10.00 --Preferred
pricelist
41 12 1,000.00 --Default price
The big question is how do I correlate customers and products with
three different pricing systems'
Please Help!roger@.wwstar.com wrote:
> I need help with an odd request. I have been asked to create a view
> price list that includes the price each customer will pay for any
> product. ProductID, customerID, and price is all I really need. Our
> rules for figuring out who pays what, is somewhat complex, and I would
> prefer to avoid cursoring throught what will be millions of records if
> possible. In order of presidence, if there is a specific customer
> price we allways use that number. Here is what the tables look like:
> Customers
> CustomerID CustomerName PriceListID ...
> 27 Acme, Inc. 1
> 35 Bonzi, LLC 1
> 41 Chuck Corp. 2
> Products
> ProductID ProductName ...
> 10 Widget
> 11 Thingy
> 12 Watchamacallit
> CustPrice
> CustomerID ProductID Price
> 27 10 2.99
> 41 11 10.00
> 35 12 30.00
> If that was the end of the story my view would match the CustPrice
> table, but there is a pricelist that we use if there is no special
> pricing for the customer. If the price list does include the productid
> in question, we use the Retail price, if the Retail price also does not
> include the product then set the price to $1000 because it is a special
> negotable item. This is how the product pricing tables look:
> PriceLists
> PriceListID PriceListName
> 1 Retail
> 2 Preferred Customer
> PriceListItems
> PriceListID ProductID Price
> 1 10 7.99
> 1 11 15.00
> 2 10 6.99
> I have added a discription to show where the price came from.What my
> veiw should look like in the end is this:
> CustomerID ProductID Price
> 27 10 2.99 --Customer price
> 27 11 15.00 --retail
> pricelist
> 27 12 1,000.00 --Default price
> 35 10 7.99 --retail
> pricelist
> 35 11 15.00 --retail
> pricelist
> 35 12 30.00 --Customer price
> 41 10 7.99 --retail
> pricelist
> (used because there no
> preferred pricelist for the item)
> 41 11 10.00 --Preferred
> pricelist
> 41 12 1,000.00 --Default price
> The big question is how do I correlate customers and products with
> three different pricing systems'
> Please Help!
>
This can be done with a couple of UNIONs, but you're going to have to
produce valid CREATE TABLE and INSERT statements first.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> This can be done with a couple of UNIONs, but you're going to have to
> produce valid CREATE TABLE and INSERT statements first.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
I see how to get the custprices into the query.
I can do a join between Customers and PriceListProducts to get the
field I need, but how do I tell it to omit customer/Proct combinations
that I already have?
Then I could look up the list prices for customer/products not in the
second set, how do I do that?
Then I need to fill in all the blanks, which is beyond the scope of my
knowledge.|||If you have millions of rows maybe a function is better:
create function dbo.GetPrice (
@.CustomerID int,
@.ProductID int
) returns double
begin
declare @.Price as double
select @.Price = cp.Price
from CustPrice cp with(nolock)
where cp.CustomerID = @.CustomerID
and cp.ProductID = @.ProductID
if @.@.RowCount > 0
return @.Price
select @.Price = pli.Price
from Customers c with(nolock)
inner join PriceListItems pli
on c.PriceListID = pli.PriceListID
where c.CustomerID = @.CustomerID
and pli.ProductID = @.ProductID
if @.@.RowCount > 0
return @.Price
return 1000
end
go
This might be a little off because I'm not sure what you mean when you
said:
"If the price list does include the productid in question, we use the
Retail price "
If you still need a view then it would be like this:
select cp.CustomerID, cp.ProductID, cp.Price, 'cp' as Source
from CustPrice cp with(nolock)
union all
select c.CustomerID, pli.ProductID, pli.Price, 'pli'
from Customers c with(nolock)
inner join PriceListItems pli
on c.PriceListID = pli.PriceListID
where not exists
(select 1
from CustPrice cp with(nolock)
where cp.CustomerID = c.CustomerID
and cp.ProductID = pli.ProductID)
union all
select c.CustomerID, p.ProductID, 1000, 'default'
from Customers c with(nolock),
Products p with(nolock)
where not exists
(select 1
from CustPrice cp with(nolock)
where cp.CustomerID = c.CustomerID
and cp.ProductID = p.ProductID)
and not exists
(select 1
from PriceListItems pli with (nolock)
where pli.PriceListID = c.PriceListID
and pli.ProductID = p.ProductID)
Indexes would be important with this code.
JJ|||Lets think this through. Since it appears you want every customer
against every product, I would start with a cross join between
Customers and Products
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
We need customer prices for all the rows returned by that. We will
use a LEFT OUTER JOIN so that we don't loose any of the above rows.
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
That has all the rows we need prices for, with the highest priority
pricess (if any). But we also sometimes need prices from the default
PriceListItems. That calls for another LEFT OUTER JOIN. Note that it
is joining to Crossed.
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
LEFT OUTER
JOIN PriceListItems as PI
ON Crossed.ProductID = PI.ProductID
AND Crossed.PriceListID = CI.PriceListID
With that we should have what we need to build the SELECT list:
SELECT Crossed.CustomerID, Crossed.ProductID,
COALESCE(CP.Price, CI.Price, 1000.00)
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
LEFT OUTER
JOIN PriceListItems as PI
ON Crossed.ProductID = PI.ProductID
AND Crossed.PriceListID = CI.PriceListID
ORDER BY Crossed.CustomerID, Crossed.ProductID
At least I think so. Without tables and test data this is untested,
but I believe the approach is correct.
Roy Harvey
Beacon Falls, CT
On 9 Aug 2006 13:21:53 -0700, roger@.wwstar.com wrote:
>I need help with an odd request. I have been asked to create a view
>price list that includes the price each customer will pay for any
>product. ProductID, customerID, and price is all I really need. Our
>rules for figuring out who pays what, is somewhat complex, and I would
>prefer to avoid cursoring throught what will be millions of records if
>possible. In order of presidence, if there is a specific customer
>price we allways use that number. Here is what the tables look like:
>Customers
>CustomerID CustomerName PriceListID ...
>27 Acme, Inc. 1
>35 Bonzi, LLC 1
>41 Chuck Corp. 2
>Products
>ProductID ProductName ...
>10 Widget
>11 Thingy
>12 Watchamacallit
>CustPrice
>CustomerID ProductID Price
>27 10 2.99
>41 11 10.00
>35 12 30.00
>If that was the end of the story my view would match the CustPrice
>table, but there is a pricelist that we use if there is no special
>pricing for the customer. If the price list does include the productid
>in question, we use the Retail price, if the Retail price also does not
>include the product then set the price to $1000 because it is a special
>negotable item. This is how the product pricing tables look:
>PriceLists
>PriceListID PriceListName
>1 Retail
>2 Preferred Customer
>PriceListItems
>PriceListID ProductID Price
>1 10 7.99
>1 11 15.00
>2 10 6.99
>I have added a discription to show where the price came from.What my
>veiw should look like in the end is this:
>CustomerID ProductID Price
>27 10 2.99 --Customer price
>27 11 15.00 --retail
>pricelist
>27 12 1,000.00 --Default price
>35 10 7.99 --retail
>pricelist
>35 11 15.00 --retail
>pricelist
>35 12 30.00 --Customer price
>41 10 7.99 --retail
>pricelist
> (used because there no
>preferred pricelist for the item)
>41 11 10.00 --Preferred
>pricelist
>41 12 1,000.00 --Default price
>The big question is how do I correlate customers and products with
>three different pricing systems'
>Please Help!|||JJ,
That did great! Thanks!
There is the one part that I didn't explain properly that I still need
to figure out.
I wrote:
> "If the price list does include the productid in question, we use the
> Retail price "
I should have wrote:
"If the price list does NOT include the productid in question, we use
the
Retail price "
Meaning, if Bob is to use the preferred customer price list, but he
buys a "thingy" which is not on the preferred customer pricelist
(PriceListID=2), the price Bob will be charged is the retail price
(PriceListID=1).
Thanks for your help,
Roger|||if the rule is that the customer allways gets the lower price of any
that are available to him, then the code should be:
--Replace the middle "union all" block with:
union all
select c.CustomerID, pli.ProductID, min(pli.Price) as Price, 'pli'
from Customers c with(nolock)
inner join PriceListItems pli with(nolock)
on c.PriceListID = pli.PriceListID
where not exists
(select 1
from CustPrice cp with(nolock)
where cp.CustomerID = c.CustomerID
and cp.ProductID = pli.ProductID)
group by c.CustomerID, pli.ProductID
If the customer would get the "preferred price" even if it was higher
then
--need to replace the middle "union all" block with this block:
union all
select c.CustomerID, pli.ProductID, pli.Price, 'pli'
from Customers c with(nolock)
inner join PriceListItems pli with(nolock)
on c.PriceListID = pli.PriceListID
inner join (select m.ProductID, max(m.PriceListID) maxPriceListID
from PriceListItems m with(nolock)
group by m.ProductID) as m
on c.PriceListID = m.MaxPriceListID
where not exists
(select 1
from CustPrice cp with(nolock)
where cp.CustomerID = c.CustomerID
and cp.ProductID = pli.ProductID)
the first one will perform better if that is the rule. It happens we
can get away with "max(m.PriceListID)" because this is a simple
example, but usually you would add a column in the PriceList table like
"Priority" and then join to the PriceList table and take a max of
that.
I still like the function idea better especially if you are only
usually pulling back a list once you have the CustomerID already
select. It is easier to maintain.
You can still use it in a select
select CustomerID, ProductID, dbo.GetPrice(CustomerID, ProductID) as
price
from ...
where CustomerID = @.CustomerID
Put me on the preferred customer list.
JJ|||On 9 Aug 2006 15:49:01 -0700, roger@.wwstar.com wrote:
>There is the one part that I didn't explain properly that I still need
>to figure out.
>I wrote:
>> "If the price list does include the productid in question, we use the
>> Retail price "
>I should have wrote:
>"If the price list does NOT include the productid in question, we use
>the
>Retail price "
>Meaning, if Bob is to use the preferred customer price list, but he
>buys a "thingy" which is not on the preferred customer pricelist
>(PriceListID=2), the price Bob will be charged is the retail price
>(PriceListID=1).
OK so we have to make some more changes. We will do yet another outer
join to pick up PriceListID=1. And the COALESCE will have the result
from that query inserted before the ultimate default.
SELECT Crossed.CustomerID, Crossed.ProductID,
COALESCE(CP.Price, CI.Price, PL1.Price, 1000.00)
FROM (SELECT * FROM Customers CROSS JOIN Products) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
LEFT OUTER
JOIN PriceListItems as PI
ON Crossed.ProductID = PI.ProductID
AND Crossed.PriceListID = CI.PriceListID
LEFT OUTER
JOIN PriceListItems as PL1
ON Crossed.ProductID = PL1.ProductID
AND PL1.PriceListID = 1
ORDER BY Crossed.CustomerID, Crossed.ProductID
There is another approach that might be worth considering. Start with
the idea that price list 1 and price list 2 are universal, so we can
join to them directly from the Products table BEFORE the cross join.
We would then have to reference them conditionally in the results.
Since the query is getting kind of hairy, I am going to use a view for
readability sake.
CREATE VIEW ProductsStdPrices
AS
SELECT P.*,
PL1.Price as PL1_Price,
PL2.Price as PL2_Price
FROM Products
LEFT OUTER
JOIN PriceListItems as PL1
ON P.ProductID = PL1.ProductID
AND PL1.PriceListID = 1
LEFT OUTER
JOIN PriceListItems as PL2
ON P.ProductID = PL2.ProductID
AND PL2.PriceListID = 2
GO
Now we substitute the view for the Products table in the cross join,
chop off the last two outer joins, and change the COALESCE. The
change to the COALESCE would have required at least one CASE
expression put inside it, so I opted to replace the entire thing with
a CASE.
SELECT Crossed.CustomerID, Crossed.ProductID,
CASE WHEN CP.Price IS NOT NULL
THEN CP.Price
WHEN Crossed.PriceListID = 2
AND Crossed.PL2_Price IS NOT NULL
THEN Crossed.PL2_Price
WHEN Crossed.PL1_Price IS NOT NULL
THEN Crossed.PL1_Price
ELSE 1000.00
END
FROM (SELECT * FROM Customers
CROSS JOIN ProductsStdPrices) as Crossed
LEFT OUTER
JOIN CustPrice as CP
ON Crossed.ProductID = CP.ProductID
AND Crossed.CustomerID = CP.CustomerID
ORDER BY Crossed.CustomerID, Crossed.ProductID
Which again, I think will work, but is untested.
Roy Harvey
Beacon Falls, CT