April 20, 2014 at 2:50 am
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;
April 21, 2014 at 4:34 am
vignesh.ms (4/19/2014)
this is not the fine answer & also not mine...
You were not asked to post someone else's code. You were asked to post what you tried.
If you are unwilling to code something yourself, why should we make the effort of coding for you? Language barrier aside, even you should know that you should be making some of this effort yourself if you want help with something.
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply