Hello,

Hopefully someone can help me with this query.

Here are the tables:

BasketItem:
idBasket - PK for each order/basket
idBasketItem - PK for this table/each item in a specific basket
idProduct - PK for each product
intPrice - Price charged
intQuantity - quantity ordered
(more unimportant fields)

Products:
idProduct - PK for each Product
intPrice - Regular price for the product
intSalesAllowed - A 1 stored here means "Yes Sales are allowed"
idProductType - PK for the product type (i.e. posters, banners, etc.)
(more unimportant fields)

TypeDiscount:
idProductType - PK for the product type (posters, banners, etc.)
intMinQuantity - Minimum Qty ordered to qualify
intSalesAmount - Amount discounted
intSalesPercent - Percent discounted
chrPromoCode - does it require a promotion code? (no in this case)
(more unimportant fields)

Ok, I need to take an order (idBasket) and find out if they qualify for a Type Discount (Order 25 posters and save 25%...). So I need to create a stored procedure that will take idBasket and find all the idBasketItems and add up the quantities of each product type in the basket and check that number against the Minimum Qty qaulifier (intMinQuantity) and return the sales amount and percent (intSalesAmount/intSalesPercent) if it qualifies.

Here is what I have so far but this is my first real big SQL Query:

Code:
create procedure sp_QtyDiscountCheck

@idBasket int

AS

Select TypeDiscount.intSalesAmount, TypeDiscount.intSalesPercent, TypeDiscount.idProductType
From BasketItem, Products, TypeDiscount
where BasketItem.idBasket = @idBasket and
BasketItem.idProduct = Products.idProduct and
Products.idProductType = TypeDiscount.idProductType and
Products.intSalesAllowed = 1 and
Sum(BasketItem.intQuantity) >= TypeDiscount.intMinQuantity
Does anyone have the answer? or some direction?

Should I bring all the BasketItems into an ASP page and then write code that can check the quantities?

Thanks,

Matt