Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Special Offer Logic

Author  Topic 

Kristen
Test

22859 Posts
Posted - 2004-11-01 : 17:42:03
I've got to develop some SQL routines to provide "Special Offers"

Basically we have a web application with a shopping cart which contains Product Codes, Quantities, Price and Discount.

The client wants to have offers like "Buy one of Product-X and one of Product-Y for a combined price of $1.50 (normally $1 each)"

The way it needs to work is:

There is a minimum number of qualifying products - if there are 3 products in a promotion and 2 "qualifying products" you must buy at least 2, of the 3, products.

There is a minimum Qualifying Quantity - you must buy at least the Qualifying Quantity - so if the qualifying quantity is 4 you must buy four items. This must include at least the number of Qualifying Products. (You cannot buy all 4 of only one product if the Qualifying Products = 2)

Discount is only available in multiples of the Promotion Quantity.

So .. if there Qualifying Products is 4, the Qualifying Quantity is 4 and the Promotion Quantity = 4 you must buy 4 different products, one of each, to get the discount. And only multiples of 4 qualify for the disount (any additional quantity, above the highest multiple, attracts the full price).

I DON'T WANT TO USE A CURSOR !!

I'm assuming two tables:

PromotionHeader which stores the Qualifying Products, Qualifying Quantity values and Promotion Quantity, and a PromotionItems table which stores the associated ProductCodes and the DiscountPrice for each product (assuming the Qualifying data in the PromotionHeader is satisfied.

What's the best way to make the calculation? (By all means change the PromotionHeader and PromotionItems tables to whatever makes the job easier)

Thanks

Kristen

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2004-11-01 : 18:07:11
ok this must look silly but could you post DDL and all that goes with it (where's tara when you need her ) because i can't really picture it quite right... sample would surly help.
those 2 table don't quite sink in to me...

Go with the flow & have fun! Else fight the flow
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts
Posted - 2004-11-01 : 19:13:20
Kristen,

The "Qualifying Products" rule can be determined by relational division. The "small divide" I think.

Once the Qualifying Products are checked, the sum of the volumes will determine the first part of the "Qualifying Quantity". Then it should be a simple ratio of "Qualifying Quantity" / "Qualifying Products" : Actual Quantities to weed out the second part.

After that some MOD work should do the Promotional Quantity..

Or.... Create a view that gives all possible combinations of the Header and Items table.. Then join that to the shopping basket with a division check. Or something like that...

Hope that helps

Hope that helps..


DavidM

"Always pre-heat the oven"
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-11-01 : 23:49:06
I thought I would just post a generic problem, 'coz I can change the tables, but I'd actually got as far as Qualifying Products and Qualifying Quantity (by division I imagine!)

However the Promotional Quantity (buy in, say, multiples of 4) is stumping me because I'm trying to store discount on each Order Item - so if I had 3 or ProductA and 2 of ProductB I need to calculate the discount on only 4, of the total of 5, products (doesn't matter which ones, the discount is spread evenly).

I'll knock up some DDL for as far as I've got and post it in a moment.

Kristen
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-11-02 : 00:31:17
[code]
SET NOCOUNT ON

DECLARE @Match_OrderID int
-- Set the Order ID being tested (does not work for 1002, 2004, 2005)
SELECT @Match_OrderID = 2004

-- Update products in basket (for any discounts etc)
CREATE TABLE PromotionHeader
(
ph_Code varchar(10) NOT NULL,
ph_Description varchar(50) NOT NULL,
ph_QualProducts int NOT NULL, -- Number of Qualifying products
ph_QualQty int NOT NULL, -- Qty of Products to Qualify
-- Maybe we need a Total Deal price (rather than price-per-each-item)
-- pp_TotalPrice money NULL, -- Discounted price
PRIMARY KEY
(
ph_Code
)
)

CREATE TABLE PromotionProduct
(
pp_ph_Code varchar(10) NOT NULL, -- Promotion code
pp_prod_Code varchar(5) NOT NULL, -- Product Code
pp_Qty int NOT NULL, -- Quantity
pp_DiscountedPrice money NOT NULL, -- Discounted price
PRIMARY KEY
(
pp_ph_Code,
pp_prod_Code
)
)

-- PROM01 - must buy all three products to qualify, buy 3, 6, 9, ...
-- PROM02 - buy 4 of any product to qualify, buy 4, 8, 12, ...

INSERT INTO PromotionHeader
SELECT ph_Code = 'PROM01',
ph_Description = 'Multi Buy Sweets Promotion',
ph_QualProducts = 1, -- Number of Qualifying products
ph_QualQty = 3 -- Qty of Products to Qualify
-- pp_TotalPrice = 2.50 -- Discounted price
UNION ALL SELECT 'PROMO2', 'Multi buy glasses 4', 1, 4 --, NULL

INSERT INTO PromotionProduct
SELECT pp_ph_Code = 'PROM01',
pp_prod_Code = 'P1A', -- Product Code
pp_Qty = 1, -- Quantity
pp_DiscountedPrice = 0.83 -- Discounted price
UNION ALL SELECT 'PROM01', 'P1B', 1, 0.83
UNION ALL SELECT 'PROM01', 'P1C', 1, 0.84
UNION ALL SELECT 'PROMO2', 'P2A', 4, 4.00
UNION ALL SELECT 'PROMO2', 'P2B', 4, 4.00

CREATE TABLE OrderItem
(
oi_ID int NOT NULL , -- Order ID
oi_Item smallint NOT NULL , -- Item Number
oi_prod_Code varchar (5) NOT NULL , -- Product
oi_PriceEach money NOT NULL ,
oi_Discount money NULL ,
oi_Qty int NOT NULL
PRIMARY KEY
(
[oi_ID],
[oi_Item]
)
)


INSERT INTO OrderItem
SELECT oi_ID = 1001, -- Order ID
oi_Item = 1, -- Item Number
oi_prod_Code = 'P1A', -- Product
oi_PriceEach = 1.00,
oi_Discount = NULL ,
oi_Qty = 1
UNION ALL SELECT 1001, 2, 'P1B', 1.00, NULL, 1
UNION ALL SELECT 1001, 3, 'P1C', 1.00, NULL, 1

UNION ALL SELECT 1002, 1, 'P1A', 1.00, NULL, 2 -- Should only discount 1
UNION ALL SELECT 1002, 2, 'P1B', 1.00, NULL, 1
UNION ALL SELECT 1002, 3, 'P1C', 1.00, NULL, 1
-- PROMO2
UNION ALL SELECT 2001, 1, 'P2A', 5.00, NULL, 2
UNION ALL SELECT 2001, 2, 'P2B', 5.00, NULL, 2

UNION ALL SELECT 2002, 1, 'P2A', 5.00, NULL, 3
UNION ALL SELECT 2002, 2, 'P2B', 5.00, NULL, 1

UNION ALL SELECT 2003, 1, 'P2A', 5.00, NULL, 4

UNION ALL SELECT 2004, 1, 'P2A', 5.00, NULL, 3 -- Should only discount 2
UNION ALL SELECT 2004, 2, 'P2B', 5.00, NULL, 2

UNION ALL SELECT 2005, 1, 'P2A', 5.00, NULL, 5 -- Should only discount 4


-- Clear existing Discount Prices
UPDATE U
SET oi_Discount = NULL
FROM OrderItem U
WHERE oi_ID = @Match_OrderID
AND oi_Discount IS NOT NULL


UPDATE U
SET oi_Discount = (oi_PriceEach - pp_DiscountedPrice) * oi_Qty
FROM OrderItem U
JOIN
(
SELECT [T_pp_ph_Code] = pp_ph_Code,
[T_oi_ID] = oi_ID
FROM OrderItem
JOIN PromotionProduct
ON pp_prod_Code = oi_prod_Code
JOIN PromotionHeader
ON ph_Code = pp_ph_Code
WHERE oi_ID = @Match_OrderID
GROUP BY pp_ph_Code, oi_ID, ph_QualQty, ph_QualProducts
HAVING
-- Check sufficient separate product lines ...
COUNT(DISTINCT oi_prod_Code) >= ph_QualProducts
-- ... and sufficient overal quantity
AND SUM(oi_Qty) >= ph_QualQty
) T
ON oi_ID = T_oi_ID -- i.e. @Match_OrderID
JOIN PromotionProduct
ON pp_ph_Code = T_pp_ph_Code
AND pp_prod_Code = oi_prod_Code
WHERE oi_ID = @Match_OrderID

SELECT *, [Line Total] = (oi_PriceEach * oi_Qty) - oi_Discount
FROM OrderItem
WHERE oi_ID = @Match_OrderID
ORDER BY oi_ID, oi_Item
GO



DROP TABLE PromotionHeader
GO
DROP TABLE PromotionProduct
GO
DROP TABLE OrderItem
GO

SET NOCOUNT OFF
GO
[/code]
Kristen
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2004-11-02 : 08:13:54
Kristen I think you have the promotion header confused a bit...

for prom01 pp_Qty on each of the three items equals '1'
This translates to 'You must buy at least one of each of the 3 items. Discount applies for each set of the 3.'

A B C
1 1 2 --qualifies for 1 set
2 3 2 --qualifies for 2 sets

Then prom02 pp_Qty on each of the two items equals '4'
Under the same kind of definition you get: 'You must buy at least 4 of each of the 2 items. Discount applies for each set of the promo group.'

-A -B
02 02 -- qualifies as 0 sets
04 05 -- qualifies as 1 set
16 20 -- qualifies as 4 sets


Now from what I gather you want prom02 to be 'buy any combination of the two items totalling, and the discount applies to every 4 items purchased.'

I would suggest you have these criteria in different columns as they behave quite differently. I am working on a mock up...

Corey
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2004-11-02 : 08:39:27
I know I am changing your sample a little bit so let me know if I'm on the right track:


SET NOCOUNT ON

DECLARE	@Match_OrderID	int
-- Set the Order ID being tested (does not work for 1002, 2004, 2005)
SELECT	@Match_OrderID = 2004

-- Update products in basket (for any discounts etc)
CREATE TABLE PromotionHeader
(
	ph_Code			varchar(10) NOT NULL,
	ph_Description		varchar(50) NOT NULL,
	ph_QualProducts		int NOT NULL,	-- Number of Qualifying products
	ph_QualQty		int NOT NULL,	-- Qty of Products to Qualify
-- Maybe we need a Total Deal price (rather than price-per-each-item)
--	pp_TotalPrice	money NULL,		-- Discounted price
	PRIMARY KEY
	(
		ph_Code
	)
)

CREATE TABLE PromotionProduct
(
	pp_ph_Code		varchar(10) NOT NULL,	-- Promotion code
	pp_prod_Code		varchar(5) NOT NULL,	-- Product Code
	pp_Qty			int NOT NULL,		-- Quantity
	pp_DiscountedPrice	money NOT NULL,		-- Discounted price
	PRIMARY KEY
	(
		pp_ph_Code,
		pp_prod_Code
	)
)

-- PROM01 - must buy all three products to qualify, buy 3, 6, 9, ...
-- PROM02 - buy 4 of any product to qualify, buy 4, 8, 12, ...

INSERT INTO PromotionHeader
SELECT	ph_Code		= 'PROM01',
	ph_Description	= 'Multi Buy Sweets Promotion',
	ph_QualProducts	= 1,	-- Number of Qualifying products
	ph_QualQty	= 3	-- Qty of Products to Qualify
--	pp_TotalPrice	= 2.50	-- Discounted price
UNION ALL SELECT 'PROMO2', 'Multi buy glasses 4',	1, 4	--, NULL

INSERT INTO PromotionProduct
SELECT	pp_ph_Code	= 'PROM01',
	pp_prod_Code	= 'P1A',	-- Product Code
	pp_Qty		= 3,		-- Quantity
	pp_DiscountedPrice = 3*0.833333333	-- Discounted price
UNION ALL SELECT 'PROM01', 'P1B', 3, 3*0.833333333
UNION ALL SELECT 'PROM01', 'P1C', 3, 3*0.833333333
UNION ALL SELECT 'PROMO2', 'P2A', 4, 4.00
UNION ALL SELECT 'PROMO2', 'P2B', 4, 4.00

CREATE TABLE OrderItem
(
	oi_ID		int NOT NULL ,		-- Order ID
	oi_Item		smallint NOT NULL ,	-- Item Number
	oi_prod_Code	varchar (5) NOT NULL ,	-- Product
	oi_PriceEach	money NOT NULL ,
	oi_Discount	money NULL ,
	oi_Qty		int NOT NULL
	PRIMARY KEY
	(
		[oi_ID],
		[oi_Item]
	)
)


INSERT INTO OrderItem
SELECT	oi_ID		= 1001,		-- Order ID
	oi_Item		= 1,		-- Item Number
	oi_prod_Code	= 'P1A',	-- Product
	oi_PriceEach	= 1.00,
	oi_Discount	= NULL ,
	oi_Qty		= 1
UNION ALL SELECT 1001, 2, 'P1B', 1.00, NULL, 1
UNION ALL SELECT 1001, 3, 'P1C', 1.00, NULL, 1

UNION ALL SELECT 1002, 1, 'P1A', 1.00, NULL, 2	-- Should only discount 1
UNION ALL SELECT 1002, 2, 'P1B', 1.00, NULL, 1
UNION ALL SELECT 1002, 3, 'P1C', 1.00, NULL, 1 -- Should not be discounted
-- PROMO2
UNION ALL SELECT 2001, 1, 'P2A', 5.00, NULL, 2
UNION ALL SELECT 2001, 2, 'P2B', 5.00, NULL, 2

UNION ALL SELECT 2002, 1, 'P2A', 5.00, NULL, 3
UNION ALL SELECT 2002, 2, 'P2B', 5.00, NULL, 1

UNION ALL SELECT 2003, 1, 'P2A', 5.00, NULL, 4

UNION ALL SELECT 2004, 1, 'P2A', 5.00, NULL, 3	-- Should only discount 2
UNION ALL SELECT 2004, 2, 'P2B', 5.00, NULL, 2	-- Should only discount 1

UNION ALL SELECT 2005, 1, 'P2A', 5.00, NULL, 5	-- Should only discount 4


-- Clear existing Discount Prices
UPDATE	U
SET	oi_Discount = NULL
FROM	OrderItem U
WHERE	    oi_ID = @Match_OrderID
	AND oi_Discount IS NOT NULL



Update Z
Set
	oi_Discount = case 
		when (oi_Qty < qualifyingItemCnt - isnull((Select sum(oi_qty) 
			From orderItem Where oi_Id = Z.oi_Id and oi_item < Z.oi_item),0)) 
			then oi_Qty*perItem
		when (qualifyingItemCnt - isnull((Select sum(oi_qty) 
			From orderItem Where oi_Id = Z.oi_Id and oi_item < Z.oi_item),0)) < 0 
			then 0 
		else (qualifyingItemCnt - isnull((Select sum(oi_qty) 
			From orderItem Where oi_Id = Z.oi_Id and oi_item < Z.oi_item),0))*perItem end
From orderItem Z
Inner Join promotionproduct Y
On Z.oi_prod_Code = Y.pp_prod_Code
Cross Join
	(
	Select 
		qualifyingItemCnt = (sum(oi_Qty)/min(pp_Qty))*min(pp_Qty),
		perItem = min(pp_DiscountedPrice/pp_Qty)
	From orderItem A
	Inner Join promotionproduct B
	On A.oi_prod_Code = B.pp_prod_Code
	Where oi_ID = @Match_OrderID
	) X
Where oi_ID = @Match_OrderID


SELECT	*, [Line Total] = (oi_PriceEach * oi_Qty) - oi_Discount
FROM	OrderItem
WHERE	oi_ID = @Match_OrderID
ORDER BY oi_ID, oi_Item
GO



DROP TABLE PromotionHeader
GO
DROP TABLE PromotionProduct
GO
DROP TABLE OrderItem
GO

SET NOCOUNT OFF
GO


Corey
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts
Posted - 2004-11-02 : 08:43:34
Dont You Just love it when marketing gets a brilliant Idea then after they have already advertized the promo........

You get to make it work.


F.U.B.A.R.



Jim
Users <> Logic
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2004-11-02 : 08:45:19
Corey -- can you add some "new line" characters to your post (the one with the code) -- it is making this thread kind of hard to read by streching everything out.

Thanks!!!

- Jeff
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-11-02 : 08:48:13
"buy any combination of the two items totalling, and the discount applies to every 4 items purchased"

Spot on

"I would suggest you have these criteria in different columns as they behave quite differently. I am working on a mock up..."

That would be a great help. Please change the DDL as you see fit, nothing is cast in stone ...

Kristen
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2004-11-02 : 09:05:23
Kristen - also let me clarify promo01- You do have to buy 1 of each and discount on sets? or you just have to buy any combination of the items totalling a multiple of 3?

Corey
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2004-11-02 : 09:29:18
Okay I think this will do it:


SET NOCOUNT ON

DECLARE	@Match_OrderID	int
-- Set the Order ID being tested (does not work for 1002, 2004, 2005)
SELECT	@Match_OrderID = 2005

-- Update products in basket (for any discounts etc)
CREATE TABLE PromotionHeader
(
	ph_Code			varchar(10) NOT NULL,
	ph_Description		varchar(50) NOT NULL,
	ph_Style		varchar(10) NOT NULL, -- Style of promotion
	ph_QualProducts		int NOT NULL,	-- Number of Qualifying products
	ph_QualQty		int NOT NULL,	-- Qty of Products to Qualify
-- Maybe we need a Total Deal price (rather than price-per-each-item)
--	pp_TotalPrice	money NULL,		-- Discounted price
	PRIMARY KEY
	(
		ph_Code
	)
)

CREATE TABLE PromotionProduct
(
	pp_ph_Code		varchar(10) NOT NULL,	-- Promotion code
	pp_prod_Code		varchar(5) NOT NULL,	-- Product Code
	pp_Qty			int NOT NULL,		-- Quantity
	pp_DiscountedPrice	money NOT NULL,		-- Discounted price
	PRIMARY KEY
	(
		pp_ph_Code,
		pp_prod_Code
	)
)

-- PROM01 - must buy all three products to qualify, buy 3, 6, 9, ...
-- PROM02 - buy 4 of any product to qualify, buy 4, 8, 12, ...

INSERT INTO PromotionHeader
SELECT	ph_Code		= 'PROM01',
	ph_Description	= 'Multi Buy Sweets Promotion',
	ph_style	= 'All', -- Must have 'ALL/ANY' of the products
	ph_QualProducts	= 3,	-- Number of Qualifying products
	ph_QualQty	= 3	-- Qty of Products to Qualify
--	pp_TotalPrice	= 2.50	-- Discounted price
UNION ALL SELECT 'PROMO2', 'Multi buy glasses 4', 'Any', 1, 4	--, NULL

INSERT INTO PromotionProduct
SELECT	pp_ph_Code	= 'PROM01',
	pp_prod_Code	= 'P1A',	-- Product Code
	pp_Qty		= 1,		-- Quantity
	pp_DiscountedPrice = 0.83	-- Discounted price
UNION ALL SELECT 'PROM01', 'P1B', 1, 0.84
UNION ALL SELECT 'PROM01', 'P1C', 1, 0.83
UNION ALL SELECT 'PROMO2', 'P2A', 4, 4.00
UNION ALL SELECT 'PROMO2', 'P2B', 4, 4.00

CREATE TABLE OrderItem
(
	oi_ID		int NOT NULL ,		-- Order ID
	oi_Item		smallint NOT NULL ,	-- Item Number
	oi_prod_Code	varchar (5) NOT NULL ,	-- Product
	oi_PriceEach	money NOT NULL ,
	oi_Discount	money NULL ,
	oi_Qty		int NOT NULL
	PRIMARY KEY
	(
		[oi_ID],
		[oi_Item]
	)
)


INSERT INTO OrderItem
SELECT	oi_ID		= 1001,		-- Order ID
	oi_Item		= 1,		-- Item Number
	oi_prod_Code	= 'P1A',	-- Product
	oi_PriceEach	= 1.00,
	oi_Discount	= NULL ,
	oi_Qty		= 1
UNION ALL SELECT 1001, 2, 'P1B', 1.00, NULL, 1
UNION ALL SELECT 1001, 3, 'P1C', 1.00, NULL, 1

UNION ALL SELECT 1002, 1, 'P1A', 1.00, NULL, 2	-- Should only discount 1
UNION ALL SELECT 1002, 2, 'P1B', 1.00, NULL, 1
UNION ALL SELECT 1002, 3, 'P1C', 1.00, NULL, 1
-- PROMO2
UNION ALL SELECT 2001, 1, 'P2A', 5.00, NULL, 2
UNION ALL SELECT 2001, 2, 'P2B', 5.00, NULL, 2

UNION ALL SELECT 2002, 1, 'P2A', 5.00, NULL, 3
UNION ALL SELECT 2002, 2, 'P2B', 5.00, NULL, 1

UNION ALL SELECT 2003, 1, 'P2A', 5.00, NULL, 4

UNION ALL SELECT 2004, 1, 'P2A', 5.00, NULL, 3	-- Should only discount 2
UNION ALL SELECT 2004, 2, 'P2B', 5.00, NULL, 2

UNION ALL SELECT 2005, 1, 'P2A', 5.00, NULL, 5	-- Should only discount 4


-- Clear existing Discount Prices
UPDATE	U
SET	oi_Discount = NULL
FROM	OrderItem U
WHERE	    oi_ID = @Match_OrderID
	AND oi_Discount IS NOT NULL

--Promo01 (This is the calc for Style 'ALL')
Update Z
Set
	oi_Discount = W.ItemSets * Y.pp_DiscountedPrice
From orderItem Z
Inner Join promotionproduct Y
On Z.oi_prod_Code = Y.pp_prod_Code
Inner Join promotionheader X
On Y.pp_ph_Code = X.ph_Code
Cross Join
	(
	Select 
		ItemSets = case when min(oi_qty) > 0 then min(oi_qty) else 0 end
	From orderItem A
	Inner Join promotionproduct B
	On A.oi_prod_Code = B.pp_prod_Code
	Inner Join promotionheader C
	On B.pp_ph_Code = C.ph_Code
	Where oi_ID = @Match_OrderID
	and ph_Style = 'ALL'
	) W
Where oi_ID = @Match_OrderID
and ph_Style = 'ALL'


--Promo02 (This is the calculation for Style 'ANY')
Update Z
Set
	oi_Discount = case 
		when (oi_Qty < qualifyingItemCnt - isnull((Select sum(oi_qty) From orderItem 
				Where oi_Id = Z.oi_Id and oi_item < Z.oi_item),0)) 
			then oi_Qty*perItem
		when (qualifyingItemCnt - isnull((Select sum(oi_qty) From orderItem 
				Where oi_Id = Z.oi_Id and oi_item < Z.oi_item),0)) < 0 
			then 0 
		else (qualifyingItemCnt - isnull((Select sum(oi_qty) From orderItem 
			Where oi_Id = Z.oi_Id and oi_item < Z.oi_item),0))*perItem 
		end
From orderItem Z
Inner Join promotionproduct Y
On Z.oi_prod_Code = Y.pp_prod_Code
Inner Join promotionheader X
On Y.pp_ph_Code = X.ph_Code
Cross Join
	(
	Select 
		qualifyingItemCnt = (sum(oi_Qty)/min(pp_Qty))*min(pp_Qty),
		perItem = min(pp_DiscountedPrice/pp_Qty)
	From orderItem A
	Inner Join promotionproduct B
	On A.oi_prod_Code = B.pp_prod_Code
	Inner Join promotionheader C
	On B.pp_ph_Code = C.ph_Code
	Where oi_ID = @Match_OrderID
	and ph_Style = 'ANY'
	) W
Where oi_ID = @Match_OrderID
and ph_Style = 'ANY'


SELECT	*, [Line Total] = (oi_PriceEach * oi_Qty) - oi_Discount
FROM	OrderItem
WHERE	oi_ID = @Match_OrderID
ORDER BY oi_ID, oi_Item
GO



DROP TABLE PromotionHeader
GO
DROP TABLE PromotionProduct
GO
DROP TABLE OrderItem
GO

SET NOCOUNT OFF
GO


Corey
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2004-11-03 : 01:51:18
"Kristen - also let me clarify promo01- You do have to buy 1 of each and discount on sets? or you just have to buy any combination of the items totalling a multiple of 3?"

PROM01

You have to buy [multiples of] one each of three products.

PROMO2

You have to buy multiples of 4, any combination of products.

Corey, thanks for the code. I've got a meeting this morning, and my daughter's Hockey match (that's on grass over this side of the pond chaps!) to watch this afternoon, but this is urgent for the client so I'll try to get a look at it before close of play.

Kristen
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site