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 2008 Forums
 Transact-SQL (2008)
 buy one get one free with product range

Author  Topic 

pawel79
Starting Member

4 Posts

Posted - 2012-10-30 : 12:23:06
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-30 : 13:20:49
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.
Go to Top of Page

pawel79
Starting Member

4 Posts

Posted - 2012-10-31 : 04:52:43
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-31 : 07:44:12
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-31 : 07:59:07
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 - 2012-10-31 : 12:37:56
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-31 : 12:59:00
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 - 2012-11-01 : 12:44:24
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-01 : 14:33:59
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
   

- Advertisement -