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.
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 rangesAny 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 59item(2for1) 2 159item(2for1) 3 59item 4 59 this will be freeitem 5 39 this will be freeitem 6 29 this will be free basket 2 example item(2for1) 1 59 this will be freeitem(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 freeitem 6 49 this will be freeitem 7 39 this will be freebasket 9 example (if I remove product at159)item(2for1) 1 59item(2for1) 2 159item(2for1) 3 59item 4 59 THIS WONT BE FREE IF ITEM 2 is removeditem 5 39 this will be freeitem 6 29 this will be freeI 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.ThanksBEGIN 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 @priceEND |
|
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. |
|
|
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 - 70item 3 - 0 (org price 20)item 2 - 59item 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. |
|
|
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. |
|
|
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 IsFreeFROM cte A INNER JOIN cte b ON a.RN1 = b.RN2;DROP TABLE #tmp; |
|
|
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-100INSERT 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'); |
|
|
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 |
|
|
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 IsFreeFROM cte A INNER JOIN cte b ON a.RN1 = b.RN2;DROP TABLE #tmp; So in this case results would be ID Price 2for1 Isfree6 29 no Yes5 39 no Yes1 59 yes No3 59 no No4 59 no No2 159 yes No |
|
|
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 sureCREATE 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 IsFreeFROM cte A INNER JOIN cte b ON a.RN1 = b.RN2; DROP TABLE #tmp; |
|
|
|
|
|
|
|