July 7, 2015 at 3:11 pm
I have a table that holds members and the points they have earned. I have to write a procedure to get the points that a certain member has earned, then find 250 other members that are randomly distributed around them. I did that, but now they want to get random members within certain ranges of this member. So, if Member A has 250 points, then the requirement is to return ~~20 members in the 200-300 range including this member, plus 20-30 members in the other ranges (i.e. 300-400, 400-500, 100-200, etc...). Does anyone have any ideas on how to do this in a query?
Using the data below, Member 270306 has 306 points, so I would want to show 20 members between 300 and 400. Then 20 between 400 and 500, 20 between 100 and 200, etc...
Here is some sample code to generate the date
DECLARE @Rows INT = 1000000; -- One million rows
drop table Poitns Test
CREATE TABLE dbo.PointsTest
(
Member INT IDENTITY PRIMARY KEY
,Points BIGINT
);
WITH Tally (n) AS
(
SELECT TOP (@Rows) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b
)
INSERT INTO dbo.PointsTest
SELECT n % 1000
FROM Tally;
SELECT *
FROM dbo.PointsTest
where Member = 270306
order by Points
SELECT *
FROM dbo.PointsTest
where points between 300 and 400
order by Points
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 8, 2015 at 8:24 am
How about the following as a base from which to expand it to fit the need:
DECLARE @Rows AS INT = 1000000; -- One million rows
DECLARE @SampleSize AS INT = 20;
DECLARE @RangeWidth AS INT = 100;
DECLARE @MemberID AS INT = 270306;
DECLARE @PointsTest AS TABLE (
Member INT IDENTITY PRIMARY KEY,
Points BIGINT
);
WITH Tally (n) AS (
SELECT TOP (@Rows) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns AS a
CROSS JOIN sys.all_columns AS b
)
INSERT INTO @PointsTest (Points)
SELECT n % 1000 AS Points
FROM Tally;
DECLARE @SpecificMemberData AS TABLE (
MemberID INT PRIMARY KEY,
Points BIGINT
);
INSERT INTO @SpecificMemberData (MemberID, Points)
SELECT Member, Points
FROM @PointsTest
WHERE Member = @MemberID;
WITH RANGES AS (
SELECT 1 AS RANGE_NO,
CAST(Points - (Points % 100) - 100 AS varchar(9)) + ' - ' +
CAST(Points - (Points % 100) - 1 AS varchar(9)) AS RANGE_NAME,
Points - (Points % 100) - 100 AS RANGE_LOW,
Points - (Points % 100) - 1 AS RANGE_HIGH
FROM @SpecificMemberData
UNION ALL
SELECT RANGE_NO + 1,
CAST(RANGE_LOW + 100 AS varchar(9)) + ' - ' +
CAST(RANGE_HIGH + 100 AS varchar(9)) AS RANGE_NAME,
RANGE_LOW + 100 AS RANGE_LOW,
RANGE_HIGH + 100 AS RANGE_HIGH
FROM RANGES
WHERE RANGE_NO + 1 < 4
),
ALL_MEMBERS_IN_RANGES AS (
SELECT R.RANGE_NAME, PT.Member AS MemberID, PT.Points,
ROW_NUMBER() OVER(PARTITION BY R.RANGE_NAME ORDER BY RAND(CHECKSUM(NEWID()))) AS RN
FROM @PointsTest AS PT
INNER JOIN RANGES AS R
ON PT.Points BETWEEN R.RANGE_LOW AND R.RANGE_HIGH
WHERE PT.Member <> @MemberID
),
FINAL_DATA AS (
SELECT RANGE_NAME, MemberID, Points, RN
FROM ALL_MEMBERS_IN_RANGES
WHERE RN <= @SampleSize
UNION ALL
SELECT R.RANGE_NAME, SMD.MemberID, SMD.Points, 0 AS RN
FROM @SpecificMemberData AS SMD
INNER JOIN RANGES AS R
ON SMD.Points BETWEEN R.RANGE_LOW AND R.RANGE_HIGH
)
SELECT *
FROM FINAL_DATA
ORDER BY RANGE_NAME, CASE RN WHEN 0 THEN 0 ELSE Points END
Let me know what you think... You might be able to implement it as a TVF.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply