Updating rows for my shopping cart

Question

Updating rows for my shopping cart

Neil Barker

Valued Member

Points: 53
More actions
December 30, 2005 at 5:06 pm

#89096

I'm trying to write an asp based shopping cart which when you apply particular products to the cart, a discount is applied to them.
I have 2 tables, the cart, and the discounts.
If there are the correct amount items in the cart (using the discounts table), the discount is applied.
Here are my tables: This is the cart table.
<catalogID><SKU><Product><Price_Each><Weight><Option><Qty><Discount>
example data:
1893 000019-03 Whey10lb 59.9900 4820.0 Vanilla 4 .0000
This is the discount table:
<ParentSKUID><SKUID><Operator><Qty><Discount>
example data:
906 765 =>     2    9.4950
20      7 =>     1    1.0000
20    19 =>     1     0.9900
What the discount table tells me is that if I have => 1 item of SKU's 7 AND 19 in the cart, a discount of £1 is applied to SKU 7, and £0.99 is applied to SKU 19
(now here's the bit I'm having problems with) this is ONLY the case when BOTH items are in the cart.
The discounts are NOT applied is only 1 of the products are in the cart, they BOTH have to be in the cart for the discount to apply.
For example, if I had 4 x SKUID 7 in the cart, the discount would not apply.
However, in the example discount table, if I had => 2 of SKU 765 then the discount would apply.
Also, the left(sku,6) from the cart is the same number as SKUID in the discounts table.
I have a cursor which loops through all the records in the cart and checks for the discounts on a line by line basis, and then updates the discounts field in the cart table.
Can someone show me the query to make this work? I have been working on this for hours and just can't get it to work.
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

I will be posting a solution shortly but in the meantime take a look at Joe Celko's Article titled "Relational Division" at http://www.dbazine.com/ofinterest/oi-articles/celko1

SQL = Scarcely Qualifies as a Language

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

The logic is

For each Order and Discount combination, the count of ordered products qualifying for the discount is equal to the count of products for the same discount.

This assumes that a product may only appear once in each order. Also, it is possible that the same ordered product could qualify for more than one discount.

Using the Northwind database tables:

Create table ProductDiscounts

( DiscountIdinteger not null

, ProductIdinteger not null

, MinQtyinteger not null

, DiscountAmtnumeric(8,4) not null

, constraint ProductDiscounts_P primary key (DiscountId , ProductId)

, constraint Products_F_ProductDiscounts foreign key

(ProductId) references products

, constraint ProductDiscounts_C_MinQty CHECK (MinQty > 0 )

, constraint ProductDiscounts_C_DiscountAmt CHECK (DiscountAmt > 0.0 )

)

go

insert into ProductDiscounts

( DiscountId , ProductId , MinQty, DiscountAmt)

select 1, 1, 2, 3.00 union all

select 1, 2, 3, 3.50 union all

select 2, 2, 1, 8.00 union all

select 2, 3, 2, 8.00 union all

select 3, 1, 2, 4.00 union all

select 3, 2, 3, 4.50 union all

select 3, 3, 2, 8.50

go

select OrderDiscountableProducts.OrderId

,OrderDiscountableProducts.DiscountId

from (

SELECT ProductDiscounts.DiscountId

, OrderDetails.OrderId

,COUNT(*) as DiscountableProductCnt

FROM dbo.[Order Details] as OrderDetails

Join dbo.ProductDiscounts

ON ProductDiscounts.ProductId = OrderDetails.ProductId

AND ProductDiscounts.MinQty <= OrderDetails.Quantity

group by ProductDiscounts.DiscountId

, OrderDetails.OrderId

) as OrderDiscountableProducts

JOIN (SELECT ProductDiscounts.DiscountId

,COUNT(*) as DiscountProductCnt

FROM dbo.ProductDiscounts

Group by ProductDiscounts.DiscountId

) DiscountProducts

on DiscountProducts.DiscountId = OrderDiscountableProducts.DiscountId

and DiscountProducts.DiscountProductCnt = OrderDiscountableProducts.DiscountableProductCnt

go

SQL = Scarcely Qualifies as a Language

Neil Barker Valued Member Points: 53 More actions · Answer 3

Thanks for coming back to me so quickly, however, I think I've got lost in translation to my application - It's not giving me the desired results.

If you create this mock up below, What I'm trying to achieve is change the <discount> field in the #TmpCart table.

The results I'm actually getting from this query are not correct.

If you look at the productdiscounts table, In this example, it should be applying the discounts for parentSKUID 906 and 20.

In my example, it will apply the discount for parentskuid 803 which is not right.

If you remove skuid 000007-05 from the #tmpcart, it should only apply the discount for parentskuid 906.

Please have a look, any suggestions are greatly appreciated.

Create table ProductDiscounts

( ParentSKUID integer not null

, SKUID integer not null

, MinQty integer not null

, DiscountAmt numeric(8,4) not null

, constraint ProductDiscounts_C_MinQty CHECK (MinQty > 0 )

, constraint ProductDiscounts_C_DiscountAmt CHECK (DiscountAmt > 0.0 )

)

go

insert into ProductDiscounts

( ParentSKUID , SKUID , MinQty, DiscountAmt)

select 906, 765, 2, 9.495 union all

select 20, 7, 1, 1.00 union all

select 20, 19, 1, 0.99 union all

select 430, 72, 2, 2.99 union all

select 803, 765, 1, 5.00 union all

select 803, 162, 1, 3.99

go

CREATE TABLE #TmpCart

( catalogID int,

cCode varchar(9),

Product varchar(70),

Price money,

Weight numeric(8,4),

[Option] varchar(50),

Qty int,

Discount money

)

GO

insert into #TmpCart

(catalogID,cCode,Product,Price,Weight,[Option],Qty,Discount)

select 2302,'000765-03','IDS Multi Pro Whey Isolate (5lb Tub)',31.9900,2510.0,'Cappuccino',2,0.0000 union all

select 1893,'000019-03','Optimum Nutrition Whey (10lb Bag)',59.9900,4820.0,'Vanilla',1,0.0000 union all

select 1680,'000007-05','Optimum Nutrition Whey 5lb Tub',31.9900,2580.0,'Vanilla',1,0.0000

GO

SELECT OrderDiscountableProducts.SKUid,

OrderDiscountableProducts.ParentSKUId

FROM ((SELECT

ProductDiscounts.ParentSKUID,

left(#TmpCart.cCode,6) AS SKUID,

COUNT(*) as DiscountableProductCnt

FROM

dbo.#TmpCart

Join

ProductDiscounts ON ProductDiscounts.SKUID = left(#TmpCart.cCode,6)

AND

ProductDiscounts.MinQty <= #TmpCart.Qty

GROUP BY

ProductDiscounts.ParentSKUId,

LEFT(#TmpCart.cCode,6)

&nbsp AS OrderDiscountableProducts

JOIN (SELECT ProductDiscounts.ParentSKUId,

COUNT(*) as DiscountProductCnt

FROM dbo.ProductDiscounts

GROUP BY ProductDiscounts.ParentSKUId

&nbsp DiscountProducts ON DiscountProducts.ParentSKUId = OrderDiscountableProducts.ParentSKUId

AND

DiscountProducts.DiscountProductCnt = OrderDiscountableProducts.DiscountableProductCnt)

brendt hess Hall of Fame Points: 3256 More actions · Answer 4

There are a few details that I feel are missing. Without this information, I could not answer your question. Looking at the items in your cart:

2302, '000765-03', 'IDS Multi Pro Whey Isolate (5lb Tub)', 31.9900, 2510.0, 'Cappuccino', 2, 0.0000

1893, '000019-03', 'Optimum Nutrition Whey (10lb Bag)', 59.9900, 4820.0, 'Vanilla', 1, 0.0000

1680, '000007-05', 'Optimum Nutrition Whey 5lb Tub', 31.9900, 2580.0, 'Vanilla', 1, 0.0000

1) If the SKUID 19 line had a Quantity of 4, and the SKUID 7 line had a quantity of 3 in the cart, what discounts should apply?

2) If the second SKUID 765 line had a quantity of 1, what discounts would apply on this order?

3) If there was only one SKUID 765 line with a quantity of 3, would the discount be the same as in 2) above?

4) If there were five SKUID 765 lines with quantity of 1 each, what discount (if any) would apply?

My assumption would be that one discount is applied per combination of rows that match the criteria. Thus, I would say that 1) would have three discounts (of 1.00 and 0.99 each) applied. 2) would have one discount of 9.495 applied, 3) would be the same as 2), and 4) would have two discounts of 9.495 applied.

If my assumptions are correct, then I would only track discounts on a per-line basis if I modify the ProductDiscounts table so that the Discount amount is per-item, and the quantity is the number of units required to earn the discount. (This may be what you already have with the sample row with the 9.495 discount amount - that would be a 19.99 discount if you buy two.)

I might also modify my discounts table so that I can have a discount that is not on a per-item basis (such as taking $5.00 off of your total order if you buy three or more of product X), but that is a different question.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

As brendthess has indicated in detail, the business rules that you have posted are incomplete and the most critical missing rules are the primary keys and foreign keys constraints for each table.

As I indicated in the previous post, an assumption is that a product may only appear once in the cart or this SQL will not work.

Also, the column cCode of your cart table is not an atomic element but is a molecule e.g it has components consisting of the first 6 characters and then the remaining characters. This must be split into two columns or you will find that SQL will not work.

Please post the DDL for the product table and the cart table including primary keys and foreign key constraints.

Note that if you have a primary key that has the identity property, the business key must still be defined as a uniqueness contraint.

SQL = Scarcely Qualifies as a Language