I'm working on building a query to handle random pairings of people so that each one can assess many others. I'm looking for a way to handle this in bulk - cross join perhaps? -rather than using a cursor to loop through people one at a time, which when tested was pretty slow as there will likely be hundreds of pairings at a time.
There are a few main parameters:
- Each pair must be unique - two IDs can only be paired once.
- There will be a specific number of pairs per ID - both the person being assessed and the person doing the assessing can have no more or less than the specific number of pairs.
- All IDs are in this one table.
- Must be able to create the pairs in random order rather.
- No ID can be paired with itself.
Any ideas for how I could approach this?
Here's the query I've been working on
DECLARE @assessmentID INT=[N];
DECLARE @assessmentPairs TABLE(
assessorID INT,
authorID INT,
assessorCounter INT,
authorCounter INT
UNIQUE NONCLUSTERED ([assessorID], [authorID])
);
INSERT INTO @assessmentPairs
SELECT assessorID,authorID,assessorCounter,authorCounter
FROM (
SELECT
e1.personID AS assessorID,
e2.personID AS authorID,
assessorCounter=ROW_NUMBER() OVER(PARTITION BY e1.personID ORDER BY e1.personID),
authorCounter=ROW_NUMBER() OVER(PARTITION BY e2.personID ORDER BY NEWID())
FROM People e1
JOIN Assessments a ON a.courseOfferingID=e1.courseOfferingID
CROSS JOIN People e2
WHERE e2.personID<>e1.personID
AND a.assessmentID=@assessmentID
GROUP BY e1.personID,e2.personID
) AS x
WHERE authorCounter<=10
ORDER BY assessorID,authorCounter,authorID,assessorCounter
SELECT *
FROM @assessmentPairs
ORDER BY authorID,assessorID