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

Question about insert query

Shortly why this works

insert dbo.t_Shopping_cart (Product_code,Name,Price,Group,Quantity,Total)
select Product_code,Name,Price,Group,Quantity=1,Total=1
FROM dbo.t_Product
WHERE Group=6

But this doesn't

insert dbo.t_Shopping_cart (Product_code,Name,Price,Group,Quantity,Total)
select Product_code,Name,Price,Group,Quantity=1,Total=Quantity*Price --this is actually line 4
FROM dbo.t_Product
WHERE Group=6

It gives this error
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Quantity'. It does not help if Quantity*Price is in brackets. Probably a simple thing, but I don't know it. Is it even possible to do this kind of query?

Regards

Leif

Your problem stems from the fact that there is no Quantity field on the Product table.. from the declaration "Total=Quantity*Price" it does not infer the Quantity from the previous "Quantity=1" - it will look for it on the product table.

You already know your Quantity when building your sql, so you need not use the reference, you can simply put the number in.
e.g.
select Product_code,Name,Price,Group,Quantity=1,Total=1*Price
or
select Product_code,Name,Price,Group,Quantity=245,Total=245*Price

|||

Aha. You mean it should be something like

select Product_code,Name,Price,Group,Quantity=@.Quantity,Total=@.Quantity*Price --this is actually line 4

Leif