March 24, 2009 at 11:19 am
I have n records that can be assigned to n offices, and I need to ensure the office assignments are equal.
Example:
R = Record
O = Office
R1, Possible Assignments O1, O2
R2, Possible Assignments O2, O3
R3, Possible Assignments O3, O4
R4, Possible Assignments O1, O4
R5, Possible Assignments O1, O2
Does anyone have a sql statement(s) that can equally distribute the assignment of Offices to Records without doing a loop? i.e., each office would get assigned to a record, with one office getting assigned to two records (4 office, 5 records)
March 24, 2009 at 2:09 pm
This solution should work for any number of records and any number of offices where the number of records is greater than or equal to the number of offices and the number of offices is greater than or equal to 2.
The test data set has 10000 records and 8 offices. The script uses a Tally table to generate the test data.
CREATE TABLE #Record (id char(10) NOT NULL PRIMARY KEY)
CREATE TABLE #Office (id char(4) NOT NULL PRIMARY KEY)
INSERT INTO #Record (id)
SELECT 'R' + RIGHT('00000' + CONVERT(varchar(5), T.N), 5)
FROM Tally T WHERE (T.N BETWEEN 1 AND 10000)
INSERT INTO #Office (id)
SELECT 'O' + RIGHT('000' + CONVERT(varchar(3), T.N), 3)
FROM Tally T WHERE (T.N BETWEEN 1 AND 8)
Here's the query. The CTEs assign an incrementing number to the #Record and #Office rows, and the CTEs are joined using the modulus operator (%) in such a way as to prevent the same office from being assigned twice to the same record (if the number of offices >= 2), and to evenly distribute the records between the offices (if number of records >= number of offices).
DECLARE @n int
SELECT @n = COUNT(*) FROM #Office
;WITH cteRecord AS (
SELECT id, rn = ROW_NUMBER() OVER (ORDER BY id)
FROM #Record
),
cteOffice AS (
SELECT id, rn = ROW_NUMBER() OVER (ORDER BY id)
FROM #Office
)
SELECT R.id AS Record, O1.id Office1, O2.id AS Office2
FROM cteRecord R
INNER JOIN cteOffice O1 ON (O1.rn = (R.rn - 1) % @n + 1)
INNER JOIN cteOffice O2 ON (O2.rn = R.rn % @n + 1)
ORDER BY R.id
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply