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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.06 seconds. Powered By: Snitz Forums 2000