Question about pulling Price from multiple tables.

Pricing is a little confusing at my work. My works database from the point of sale has multiple places for price to be. Right now the tables in question are in a SQL database. I am trying to create a user defined function so I can get a list of prices for specific items and for the customer logged into the web page. The tables look like this:

Inventory: ItemID, ItemCompany, Description, ListPrice, PublicPrice
ContractID: ContractNum, ItemID, ItemCompany, MinQty, ContractPrice
Customer: CustomerNumber, CustomerDepartment, Contract1 – Contract4

ContractNum '99' is a global contract to all customers. I was able to get the price for this generic contract price, but not for contracts customer may have.

Here is what I have so far (with an example item already being pulled up). I am at a loss on how to get the customers contract price with the same function.

SELECT Inventory.ItemNumber, Inventory.Company, Inventory.Description,
 Contracts.QuantityAS MinContractQty,COALESCE (Contracts.ContractPrice, Inventory.WhlCatalogPrice)
AS Price, Inventory.WhlCatalogPriceAS ListPrice
FROM InventoryLEFTOUTER JOIN ContractsON Inventory.ItemNumber = Contracts.ItemNumberAND Inventory.Company = Contracts.CompanyAND'99' = Contracts.ContractNumber
WHERE (Inventory.ItemNumber = N'444')AND (Inventory.Company = N'035')

Assuming my grasp of your schema is correct, is there a reason why this wouldn't work?

SELECT ContractID.ContractPrice, Inventory.DescriptionFROM Customer, ContractID, InventorWHERE (Customer.Contract1 = ContractID.ContractNumORCustomer.Contract2 = ContractID.ContractNumORCustomer.Contract3 = ContractID.ContractNumORCustomer.Contract4 = ContractID.ContractNum)AND ContractID.ItemID = Inventory.ItemID

This should get all the prices and descriptions for the items that have a contract number. Add another AND clause to the WHERE and narrow it down to the CustomerID you want. You can add the global contract by addind 99 = ContractID.ContractNum in the WHERE statement (put it in the parentheses).|||The only problem I am running into is if there is no contract price under '99' or under one for the customer, it would not bring up the item. The '99' group is select items, the contract price just applies to everyone. Thanks for the reply.|||Can you post some sample data too?|||

Sure. I think I have all of the different situations that might come up.

Contract Table:
"ItemNumber","Company","ContractNumber","ContractPrice","Qty"
"01541","NAT","BB",$4.99,1
"01541","NAT","DD",$4.50,1
"26300","CRD","DD",$6.00,1
"26300","CRD","BB",$6.29,1

Customers:
"CustomerNumber","Department","CustomerName","Contract1","Contract2","Contract3","Contract4"
" 15576","","Company1","DD","","",""
" 15577","","Company2","BB","","",""
" 15601","","Company3","","",,""
" 15602",,"Company4","C1","DD","",""

Product Table:
"ItemNumber","Company","Description","WhlCatalogPrice"
"06342","MEA","BOOK,NOTE,SCIENTIFIC,11X8.5",$11.69
"24133","SAN","PEN,LIQUID EXPRS 8PK,AST",$18.50
"26300","CCS","CASE,DRIVE,NEO-SPORT,BK",$30.73
"33350","KMW","DOCK,ENTERTAINMENT,IPOD 500",$162.50
"01541","NAT","CLIPBOARD,LTR,RECY,PLAST,RD",$7.35
"01593","COX","WIPE,DISINFECTING,CLOROX",$52.80
"26300","CRD","Item 26300",$9.95
"20021","IVR","INKCART,MFC3100, 950YD,BK",$16.79
"45005","CCS","TOWER,CD,ONETOUCH,40 CAP",$64.95
"5500","TOP","FORM,P/OUT,1PT,8.5X11,35C",$128.47
"5500","LAK","HEATER,RADIATOR,GY",$199.95
"5500","OSP","CHAIR,MANAGERIAL,MESH,BK",$355.00|||

Tealc:

The only problem I am running into is if there is no contract price under '99' or under one for the customer, it would not bring up the item. The '99' group is select items, the contract price just applies to everyone. Thanks for the reply.

I'm not quite getting what you're trying to do. Can you explain it again for me?

Thanks.

|||

I ment to add these to the contract table:

"06342","MEA","99",$9.69,1
"24133","SAN","99",$16.99,1

I am looking for the function to return both "List Price" (which is WhlCatalogPrice), and a price specific to the '99' group or customer contract if they have any.

Thanks for everyones reply so far.

|||

SELECT i.Itemnumber, i.Company, i.Description,COALESCE(Contract1.MinQty,Contract2.MinQty,Contract3.MinQty,Contract4.MinQty,GlobalContract.MinQty) AS MinContractQty,COALESCE(Contract1.ContractPrice,Contract2.ContractPrice,Contract3.ContractPrice,Contract4.ContractPrice,GlobalContract.ContractPrice,Inventory.WhlCatalogPrice) AS Price,I.WhlCatalogPrice AS ListPrice

FROM Inventory i

CROSS JOIN Customer cust

LEFT JOIN Contracts Contract1 ON Contract1.ContractNum=cust.Contract1 AND Contract1.ItemID=i.ItemID

LEFT JOIN Contracts Contract2 ON Contract2.ContractNum=cust.Contract2 AND Contract2.ItemID=i.ItemID

LEFT JOIN Contracts Contract3 ON Contract3.ContractNum=cust.Contract3 AND Contract3.ItemID=i.ItemID

LEFT JOIN Contracts Contract4 ON Contract4.ContractNum=cust.Contract4 AND Contract4.ItemID=i.ItemID

LEFT JOIN Contracts GlobalContract ON GlobalContract.ContractNum=99 AND GlobalContract.ItemID=i.ItemID

WHEREcust.CustomerNumber=@.CustID

|||That query seems to be working, but I keep getting duplication. I am going to look at each of the tables to make sure there is no duplication in them. I am still trying to learn what is what in the database from this new to me point of sale system (DDMS).sql