Hi All,

Not sure if this is the right group so feel free to point me in a better
direction.

Most of our customers have a personal price for every product but Im having
a nightmare trying to produce a price matrix on the fly. I currently have a
solution in MS Access 2K which uses a series of append and update queries to
create a temp table, this takes a little over 2 minutes to generate and is
becoming problematic. The idea is to have the sales office be able to give a
customer a price over the phone - a 2-3 minute wait isnt going to work.

I have 3 sources of prices

global : This contains a default price for every stock item it is the base
of all prices
price set(s): these contain a group of stock items from global at a
discounted price
personal product prices : A customer specific price for a specific stock
item

All prices can and do change and frequently and there are are also quantity
discount breaks for the same stock Item.

We carry approximately 3500 products and have about 4000 customers.

Im currently in the process or moving things from MS Access 2K over to MS
SQL Server 2K

I realise that its difficult to visualise without the database but if
someone could offer some pointers,

Im put off buliding 1 big flat table due to the size and the frequent
updates - am I right to avoid doing this?
Is it correct to construct the temp table and then running update/inserts -
Im having problems with the lack of a unique ID as Stock code is duplicated
for different quantity.
I can quickly generate a temp table containing all the information from each
source adding a field value for the process number (1,2,3 ) what Id like is
the record with the highest process number, but if I use a group by and max
as soon as I add price in the view I get all the records again.

I cant help but think I have something fundamentally wrong as its causing
such a problem. Can anyone point me at some info or share some insight.

TIA.

[quoted text, click to view]

Hi Tery,

Your precedence is right, customer - price set then global, 1 to 3.

What im looking for,

Item Qty Price
Item1 1 2.25
Item1 10 1.5
Item2 10 1.9
Item3 100 4.99
Item4 1 2.2

The best I've come up with so far is to copy each stage (global, price set
then customers price) into a temp table with triggers to check if the
stock_Code and Qty match then update rather than append.

Any help suggestions welcome.

(SQL server is all really rather new to me still)

DDL hopefully below.

CREATE TABLE [dbo].[tbl_Customers] (
[ID] [numeric](18, 0) NOT NULL ,
[Company] [char] (10) NULL ,
[ID_Price_set_Global] [numeric](18, 0) NULL ,
[ID_Price_set_Main] [numeric](18, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_Matrix_Base] (
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Price_Set_ID] [numeric](18, 0) NULL ,
[Company_ID] [numeric](18, 0) NULL ,
[Stock_Code] [varchar] (10) NOT NULL ,
[Quantity] [numeric](18, 0) NOT NULL ,
[Price] [float] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_Price_Sets] (
[ID] [numeric](18, 0) NOT NULL ,
[Price_set_ID] [numeric](18, 0) NOT NULL ,
[Description] [varchar] (50) NULL
) ON [PRIMARY]
GO


insert into tbl_customers
([ID],Company,ID_price_set_global,ID_Price_set_main) values (1,'Test1',1,2)
Go
insert into tbl_price_sets ([ID],Price_set_ID,[Description]) values
(1,1,'Price set 1 Global')
insert into tbl_price_sets ([ID],Price_set_ID,[Description]) values
(2,2,'Price set 2 Band A')
Go
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quantity,Price) values
(1,null,'Item1',1,2.25)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quantity,Price) values
(1,null,'Item1',10,2)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quantity,Price) values
(1,null,'Item2',10,3)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quantity,Price) values
(1,null,'Item3',100,4.99)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quantity,Price) values
(1,null,'Item4',1,2.2)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quantity,Price) values
(1,null,'Item2',10,1.9)
insert into tbl_Matrix_base
(Price_set_Id,Company_ID,Stock_code,Quantity,Price) values
(null,1,'Item1',10,1.5)
Go

Annon (Ieatlotsofspam@yum.yum) writes:
[quoted text, click to view]

Is that table structure you posted carved in stone? Or can you change it?

I started to compose a query from your repro, but I found it darn
difficult as tbl_Matrix_base is so scattered. And there were also
some thing I did not understand. What does those two ID_price_set in
Customers mean? And your sample data had:

[quoted text, click to view]

How do I know which is which?

First of all, you need to keep track of which is which. To start with
you need a table to define sellable items:

CREATE TABLE sellable_items (stockcode varchar(10) NOT NULL,
qty smallint NOT NULL,
stdprice float NOT NULL,
CONSTRAINT pk_selleable_items PRIMARY KEY (stockcode, qty))

This table could just as well hold the standard price.

A price-set table would then look like this

CREATE TABLE priceset (pricesetid integer NOT NULL,
pricesetname varchar(30) NOT NULL,
CONSTRAINT pk_priceset PRIMARY KEY (pricesetid))

The actual prices in a price set would be in:

CREATE TABLE pricesetprices (pricesetid integer NOT NULL,
stockcode varchar(10) NOT NULL,
qty smallint NOT NULL,
price float NOT NULL,
CONSTRAINT pk_pricesetprices PRIMARY KEY
(pricesetid, stockcode, qty),
CONSTRAINT fk_prp_priceset FOREIGN KEY(pricesetid)
REFERENCES pricesets (pricesetid),
CONSTRAINT fk_prp_sellable FOREIGN KEY (stockcode, qty),
REFERENCES sellable_items (stockcode, qty)
)

Then then customer-specific prices:

CREATE TABLE customerprices (customerid integer NOT NULL,
stockcode varchar(10) NOT NULL,
qty smallint NOT NULL,
price float NOT NULL,
CONSTRAINT pk_customerprices PRIMARY KEY
(customerid, stockcode, qty),
CONSTRAINT fk_cup_customer FOREIGN KEY(customerid)
REFERENCES tbl_Customers (ID),
CONSTRAINT fk_cup_sellable FOREIGN KEY (stockcode, qty),
REFERENCES sellable_items (stockcode, qty)
)


Now to get all prices for a customer you can do:

SELECT s.stock_code, s.qty, price = coalesce(cup.price, prp.price,
s.price)
FROM sellable_items s
CROSS JOIN (SELECT * FROM tbl_Customers WHERE ID = @custid) c
LEFT JOIN pricesetprices prp ON c.pricesetid = prp.pricesetid
AND s.stock_code = prp.stock_code
AND s.qty = prp.qty
LEFT JOIN customerprices cup ON c.customerid = cup.customerid
AND s.stock_code = cup.stock_code
AND s.qty = cup.qty

A very important thing to note here is the use of foreign keys, and
multi-column primary keys. A common mistake is to add an ID column to
each table and think you have a primary key. For basic concepts like
customers, this can be a good thing, but for composed entities like the
price plan is not.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

[quoted text, click to view]

<SNIP elegant solution>

[quoted text, click to view]

Thanks for the solution Erland, unfortunatly as is always the way - Im stuck
with backward compatibility I cant rip out the tables and replace them with
the correct design. As you point out the problem is not know what is what
and from where it came. All the data is held in 1 table the tbl_Matrix_base,
customer, price set and global all lumped together. As time was pressing I
kept the temp table created a compound key field from Company & Stock_Code &
Qty , created a unqiue index on it but with ingnore_dup_key on. and ran the
3 insert queries. It adds new records but fails to add duplicates - as long
as I keep the insert order correct it gives me the results I need.

Its far from perfect - but its bought me more time, I'll take apart your
example and see if I can work it back into the live system.

Thanks again for the help.