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

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.

---

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;

	CASE WHEN (a.RN1 <= a.RN2 OR a.Price > 59 OR b.Price < 59) THEN 'No' ELSE 'Yes' END AS IsFree

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;

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

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;