Just for fun (with nothing better to do), a recursive and a window function approach 😎
Neither are perfect, more like a little twist....
A resource hungry recursion:
USE tempdb;
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE N'#Sample_%')
DROP TABLE #Sample;
CREATE TABLE #Sample (
product varchar(1024),
Price decimal(9,2)
)
INSERT INTO #Sample values
('Pen',10),('DVD ',29),('Pendrive',45)
,('Mouse',12.5),('TV',49);
DECLARE @TARGET DECIMAL(12,2) = 200;
;WITH BOUNDRIES AS
(SELECT
MAX(@TARGET / S.Price) AS MAX_COL
,MIN(@TARGET / S.Price) AS MIN_COL
FROM #Sample S
),
LEV2 AS
(
SELECT
1 AS LEVEL
,CAST(S1.product AS VARCHAR(1024)) AS product
,CAST(S1.Price AS DECIMAL(12,2)) AS Price
FROM #Sample S1
UNION ALL
SELECT
LEVEL + 1 AS LEVEL
,CAST(S1.product + CHAR(44) + S2.product AS VARCHAR(1024)) AS product
,CAST(S1.Price + S2.Price AS DECIMAL(12,2)) AS Price
FROM LEV2 S1, #Sample S2
WHERE S1.LEVEL < (SELECT MAX_COL FROM BOUNDRIES)
AND S1.Price < @TARGET
AND S1.product <> S2.product
)
,LEV_TOTAL AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY LEN(L2.product)
ORDER BY (SELECT NULL)
) AS RID
,L2.Price
,L2.product
,LEN(L2.product) AS PROD_LEN
,L2.LEVEL
FROM LEV2 L2
WHERE L2.Price = @TARGET
)
SELECT
*
FROM LEV_TOTAL WHERE RID = 1
Very fast window approach:
USE tempdb;
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE N'#Sample_%')
DROP TABLE #Sample;
CREATE TABLE #Sample (
product varchar(100),
Price decimal(9,2)
)
INSERT INTO #Sample values
('Pen',10),('DVD',29),('Pendrive',45)
,('Mouse',12.5),('TV',49);
DECLARE @TARGET DECIMAL(12,2) = 1000;
;WITH NN AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS X(N))
,NUMS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM NN N1,NN N2, NN N3
ORDER BY N OFFSET 0 ROWS FETCH FIRST (SELECT CAST(MAX(@TARGET / S.Price) AS INT) FROM #Sample S) ROWS ONLY)
,BASE_TOTAL AS
(
SELECT
S.product
,S.Price
,NM.N
,SUM(S.Price) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY (SELECT NULL)
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS TOTAL
FROM
#Sample S
CROSS APPLY NUMS NM
)
SELECT
ROW_NUMBER() OVER
(
PARTITION BY (SELECT NULL)
ORDER BY BT.TOTAL DESC
) AS RID
,BT.product
,BT.Price
,BT.TOTAL
FROM BASE_TOTAL BT
WHERE TOTAL <= @TARGET;