SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 buy one get one free with product range
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pawel79
Starting Member

4 Posts

Posted - 10/30/2012 :  12:23:06  Show Profile  Reply with Quote
I have to write SQL query which will show basket results.
If you buy product (mark as 2for1) you can add another item for free but there are ranges
Any thing €59- €100 Range
Mary buys product @ €59 0-€59
Mary buys product @ €69 0-€59
Mary buys product @ €100 0-€59
Any thing over €100
Mary buys product @ €110 0-100
Mary buys product @ €180 > €400 0-100

basket 1 example
item(2for1) 1 59
item(2for1) 2 159
item(2for1) 3 59
item 4 59 this will be free
item 5 39 this will be free
item 6 29 this will be free


basket 2 example
item(2for1) 1 59 this will be free
item(2for1) 2 159


basket 4 example
item(2for1) 1 159
item(2for1) 2 159
(NO FREE ITEMS)

basket 5 example
item(2for1) 1 89
item(2for1) 2 89
(NO FREE ITEMS)

basket 6 example
item(2for1) 1 89
item(2for1) 2 59 this will be free

basket 7 example
item(2for1) 1 59
item(2for1) 2 59 this will be free

extream basket 8 example
item(2for1) 1 59
item(2for1) 2 159
item(2for1) 3 59
item(2for1) 4 59
item 5 59 this will be free
item 6 49 this will be free
item 7 39 this will be free

basket 9 example (if I remove product at159)
item(2for1) 1 59
item(2for1) 2 159
item(2for1) 3 59
item 4 59 THIS WONT BE FREE IF ITEM 2 is removed
item 5 39 this will be free
item 6 29 this will be free

I had a go but can't seem to calculate how many product are in relation to range 0-59 and how many 0-100. I may be over complicating it. I don't need a whole the query just need a help what why to go about it. Can any one can help.
Thanks



BEGIN
	DECLARE	@price	real

	SELECT @price =SELECT LensPrice =

						CASE 
							WHEN
								CASE
								WHEN (SELECT ID FROM( 									
																	SELECT TOP (
									CASE 
										WHEN (SELECT dbo.func_getNoCartItemsRange1(ci.cartID)) +(SELECT dbo.func_getNoCartItemsRange2(ci.cartID))>0
											THEN
											CASE 
												WHEN (SELECT dbo.func_getNoCartItems(ci.cartID))-((SELECT dbo.func_getNoCartItemsRange1(ci.cartID)) +(SELECT dbo.func_getNoCartItemsRange2(ci.cartID))*2)>0 THEN (SELECT dbo.func_getNoCartItemsRange1(ci.cartID)) +(SELECT dbo.func_getNoCartItemsRange2(ci.cartID)) 
												ELSE round((SELECT dbo.func_getNoCartItems(ci.cartID)/2),0,1)
											END
										
										ELSE 0
									END
									) ci1.ID 									
									FROM cartItems ci1
									LEFT JOIN products p ON ci1.productID = p.ID 	    
									WHERE cartID= ci.cartID				
									ORDER BY p.Price
									)tbl WHERE ID = ci.ID)IS NOT NULL
								THEN 1
								ELSE 0
								END			
								>0 
							THEN 0							
							
							ELSE p.Price
	
						END							

				FROM cartItems ci
				LEFT JOIN products p ON ci.productID = p.ID 				
				WHERE ci.id = @ID	
	)tbl
	RETURN @price
END

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/30/2012 :  13:20:49  Show Profile  Reply with Quote
What do you want to happen if Mary wants only four products, priced at €70, €59, €20 and €10?
Do you give away the last two for free or do you give away the €59 item for free?

If I am Mary, I want the €59 item for free.

Edited by - sunitabeck on 10/30/2012 13:23:21
Go to Top of Page

pawel79
Starting Member

4 Posts

Posted - 10/31/2012 :  04:52:43  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

What do you want to happen if Mary wants only four products, priced at €70, €59, €20 and €10?
Do you give away the last two for free or do you give away the €59 item for free?

If I am Mary, I want the €59 item for free.


If €70, €59 are 2for1 (they are in range of 59-10) so I give the cheapest 20 and 10 for free.
so the basket would look like
item 1 - 70
item 3 - 0 (org price 20)
item 2 - 59
item 4 - 0 (org price 10)
They don't need to be group like that but just move them to show what I mean.
Thanks for reply.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/31/2012 :  07:44:12  Show Profile  Reply with Quote
If that is your rule, that is your rule. But, I think you may have a lot of unhappy customers and/or customers who try to game the system. In the example I posted, instead of buying all four items together, I will buy the most two expensive items together so I get one of them free, and then buy the least two expensive later paying for both.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/31/2012 :  07:59:07  Show Profile  Reply with Quote
Here is my thoughts on a way to do what you are asking to do. It pairs out the highest priced item against the lowest priced item.
CREATE TABLE #tmp (Item INT, Price INT);
INSERT INTO  #tmp VALUES (1,59),(2,159),(3,59),(4,59),(5,39),(6,29);


;WITH cte AS
(
	SELECT *, 
		ROW_NUMBER() OVER (ORDER BY Price DESC,Item DESC) AS RN1,
		ROW_NUMBER() OVER (ORDER BY Price ASC,Item ASC) AS RN2
	FROM
		#tmp
)
SELECT
	a.Item,
	a.Price,
	CASE WHEN (a.RN1 <= a.RN2 OR a.Price > 100 OR b.Price < 59) THEN 'No' ELSE 'Yes' END AS IsFree
FROM
	cte A
	INNER JOIN cte b ON a.RN1 = b.RN2;

DROP TABLE #tmp;
Go to Top of Page

pawel79
Starting Member

4 Posts

Posted - 10/31/2012 :  12:37:56  Show Profile  Reply with Quote
Thanks a lot for help so far. Unfortunately I cant fully understand what you've done so just gonna ask more questions.
If I change
INSERT INTO #tmp VALUES (1,89),(2,89);

I shouldn't get any of them free cos the second item is not in range of 0-59.
It works fine for second range 0-100
INSERT INTO #tmp VALUES (1,159),(2,159);

Any reason why?

Will it be possible to add another column which indicates if product is on 2for1 offer and still use the way of thinking you introduced?
INSERT INTO #tmp VALUES (1,89,'yes'),(2,89,'yes');
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/31/2012 :  12:59:00  Show Profile  Reply with Quote
Sorry about that - change as shown in red.
	CASE WHEN (a.RN1 <= a.RN2 OR a.Price > 59 OR b.Price < 59) THEN 'No' ELSE 'Yes' END AS IsFree

Go to Top of Page

pawel79
Starting Member

4 Posts

Posted - 11/01/2012 :  12:44:24  Show Profile  Reply with Quote
Thanks.Almost there Is it a big job to do a checks if product is 2for1

CREATE TABLE #tmp (Item INT, Price INT, a2for1 varchar(3));
INSERT INTO  #tmp VALUES (1,59,'yes'),(2,159,'yes'),(3,59,'no'),(4,59,'no'),(5,39,'no'),(6,29,'no');


;WITH cte AS
(
	SELECT *, 
		ROW_NUMBER() OVER (ORDER BY Price DESC,Item DESC) AS RN1,
		ROW_NUMBER() OVER (ORDER BY Price ASC,Item ASC) AS RN2
	FROM
		#tmp
)
SELECT
	a.Item,
	a.Price,
	a.a2for1,
	CASE WHEN (a.RN1 <= a.RN2 OR a.Price > 100 OR b.Price < 59) THEN 'No' ELSE 'Yes' END AS IsFree
FROM
	cte A
	INNER JOIN cte b ON a.RN1 = b.RN2;

DROP TABLE #tmp;

So in this case results would be

ID	Price	2for1	Isfree
6	29	no	Yes
5	39	no	Yes
1	59	yes	No
3	59	no	No
4	59	no	No
2	159	yes	No
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/01/2012 :  14:33:59  Show Profile  Reply with Quote
I think the following would do it, but I haven' tested it enough to know for sure
CREATE TABLE #tmp (Item INT, Price INT, a2for1 varchar(3));
INSERT INTO  #tmp VALUES (1,59,'yes'),(2,159,'yes'),(3,59,'no'),(4,59,'no'),(5,39,'no'),(6,29,'no');

;WITH cte AS
(
	SELECT *, 
		ROW_NUMBER() OVER (ORDER BY CASE WHEN a2for1 = 'No' THEN 1 ELSE 0 END, Price DESC,Item DESC) AS RN1,
		ROW_NUMBER() OVER (ORDER BY CASE WHEN a2for1 = 'No' THEN 0 ELSE 1 END, Price ASC,Item ASC) AS RN2
	FROM
		#tmp
)
SELECT
	a.Item,
	a.Price,
	a.a2for1,
	CASE WHEN (a.RN1 <= a.RN2 OR a.Price > 59 OR b.Price < 59  OR b.a2for1 = 'No') THEN 'No' ELSE 'Yes' END AS IsFree
FROM
	cte A
	INNER JOIN cte b ON a.RN1 = b.RN2;
	
DROP TABLE #tmp;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000